Problems Schduling DTS Package With ActiveX Script
Aug 29, 2001
I have created a DTS job on one of my servers that fails when it is scheduled. If I execute the job directly from the DTS console it runs successfully. However, if I schedule the job, it returns the following error:
... DTSRun: Executing... DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart: DTSStep_DTSActiveScriptTask_1 DTSRun OnError: DTSStep_DTSActiveScriptTask_1, Error = -2147220482 (800403FE) Error string: Error Code: 4096 Error Source= Microsoft Outlook Error Description: Unable to perform the operation. The server is not available. Contact your administrator if this condition persists. Could not complete the operation because the service provider does not support it. Error on Line 23 Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 1100 Error Detail Records: Error: -2147220482 (800403FE); Provider Error: 0 (0) Error string: Error Code: 4096 Error Source= Microsoft Outlook Error Description: Unable to perform the operation. The server is not available. Contact your administrator if this condit... Process Exit Code 1. The step failed.
I am creating a DTS package to import a .txt file into sql. I have everything in place, but the text file needs to have the last record deleted before the import. I need help with this part
I would like to delete the last record from a fixed width text file before I import it into sql. The number of rows will vary from file to file.
Can any one offer suggestions on the best way to do this.
I understand that I have to use the FSO to open and read the file, but I am not sure the best way to proceed after that.
I'm looking for a way to get the name of the server on which the DTS package lives.
I copy packages between servers. The problem is that everytime a package is copied to different server, I have to change the reference in the connection strings to point to the new server name. I'd like to find an automatic way to interrogate the server name where the package currently lives and dynamically change connection strings from within an ActiveX task. That would cut maintenance way down.
I have several .xls files in a folder. I want to process all .xls file one by one. The following script will give the file name using a loop. How I can call another package by passing the name of file to it.
Dim objFSO, objFolder, colFiles, objFile
Set objFSO = CreateObject("Scripting.FileSystemObject") Set objFolder = objFSO.GetFolder(DTSGlobalVariables("gvFolder").value) Set colFiles = objFolder.Files
If colFiles.Count > 0 then For Each objFile in colFiles If Ucase(Right(objFile,4)) = ".XLS" then ' How to call a another Package by Passing the objFile to it End if Next End if
Using ActiveX Script Task it is possible create office automation objects such as Word and Excel typically using the CreateObject() Syntax. Can you use this syntax to create a Package object using CreateObject("DTS.Package")? If not Why not? I am not sure whether it was possible in DTS.
I am aware of the fact that ActuveX Script Task is included in this Version of SSIS and will be deprecated.
Hello All,I am trying to create a DTS package.I have two tables tbl_A and tbl_B with similar data/rows but noprimary keys.tbl_A is master.I would like this package to query tbl_A and tbl_B and find1)all rows in tbl_A that are different in tbl_B, 2)all rows in tbl_Athat are not present in tbl_B and3)all rows in tbl_B that are not present in tbl_A, and then just showthose rows.Can this be done with a simple UNION?Perhaps this could produce a temp Table that can be dropped once theDTS package exists successfully.The 2nd part after all the above rows are retrieved is that I wouldlike to add an addional Column to the retrieved data called STATUSwhich has 3 possible values(letters) at the end of each row...M (modified) means that row exists in tbl_B but has 1 or moredifferent columnsA (add) means this row exists in tbl_A but not in tbl_BD (delete) means this row exists in tbl_B but not in tbl_AI'm hopping this DTS package would output a nice comma seperated TXTfile with only...1) rows from tbl_A that are different in tbl_B (STATUS M)2) rows from tbl_A that are not present in tbl_B (STATUS A)3) rows from tbl_B that are not present in tbl_A (STATUS D)Can a DTS package in MS SQL be used to perfom all of the above tasks?I would very much appreciate any help or any advise.Thanks in advance :-)
I have a DTS package that I brought over from SQL server 2000 in to SQL Server 2005. I have installed all of the legacy components to run the DTS packages but I need to debug an ActiveX script task. In SQL Server 2000 I could turn on Just-In-Time debugging and use the stop operator (in my vbscript) to break the running script and launch the debugger.
I don't see how to do this in SQL Server 2005 Management Studio. Is it possible to debug a script object in a DTS package running in SQL Server 2005?
I have a SQL2000 DTS package that executes vbscript to loop through a recordset which:
- runs a stored procedure and populated tables
- builds a recordset from the populated tables to write records to an Excel file
- writes status to text files with either the error or success notices
I use FSO to set up the success and error files, but the scheduled job in SQL2005 which calls the SSIS package returns the following error:
"Retrieving the file name for a component failed with error code 0x0015F74C"
I can successullly run this (vbscript) in both the SSIS package via the BI Development Studio and in MS Access (exactly the same code in both) - but not as a SSIS package called in a scheduled job in SQL2005.
I am at an impasse with this ... any and ALL assistance would be GREATLY appreciated.
I have to put some ActiveX code in the first step of a DTS package, which will search thru all the files in some folder and if it finds a file with filename starting with "Test" (like TestFile.txt), the script will rename it and then use it for transforming data to SQL tables.
In VB Dir$ function could have been used, what should I use in ActiveX?
Hello, I have a DTS package that has an ActiveX task. This task works with access to create databases and push out data to them. The problem is, I'm getting an error trying to establish a connection to the SQL Server database as such: set objConn = Server.CreateObject("ADODB.Connection") I get this as a resolution: http://support.microsoft.com/default.aspx?scid=kb;en-us;201740 But I wasn't sure since I'm not dealing with ASP. I'm trying to run the task as myself manually, not scheduled also, for testing purposes. Any ideas? Thanks.
I'm attempting to write my first activex script. Not familiar with VB script so bear with what might seem like a simple question.
I'm trying to use an activex script in a dts to do a transformation. I have all the columns as straight copies except for one which I'm doing an activex script. What I'm trying to do is if the date is less than 1900-01-01 I want to default it to 1900-01-01. Every once in awhile a date comes across from the source file with a date of 0001-01-01 which sql doesn't like so I want to default it to 1900-01-01 instead. The script I've created parces fine but when I run the dts I get an error that the activex script encountered an invalid data value for source column LLAI. I don't understand why. any suggestions would be greatly appreciated.
Here is the script.
Function Main() If DTSSource("LLAI") >= "1900-01-01" Then DTSDestination("LastActivityISODate") = DTSSource("LLAI") Else DTSDestination("LastActivityISODate") = "1900-01-01" End If
Am having trouble writing to a table on the SQL 7 Server database, using a DTS ActiveX script.
When I try a .ADDNEW function, the following error comes up. "The opperation required by the application is not supported by the provider."
The line preceding the .ADDNEW are as follows. ----- SET Conn=CreateObject("ADODB.Connection") SET RS = CreateObject("ADODB.RecordSet") Conn.ConnectionString = "PROVIDER=SQLOLEDB;DATABASE=DataIn;User ID=sa;Password=" Conn.Open RS.Open sqlSites, Conn ----
I am getting following error message due to which i am not able to access database
Microsoft OLE DB Provider for ODBC Drivers error '80040e4d' [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed- User: _ Reason: Not defined as a valid user of a trusted SQL Server connection. /myhome/adduser.asp, line 28
I using ODBC DSN with NT authentication. I am accessing Data from SQLServer 6.5 using ActiveX DLL. I used ADO to connect to the database in DLL
I ran this activeX script with my DTS package. For debugging purpose I included the message box. The message is showing the actual name of the file but the the file name is not changing to the name display in the message box.
set objFSO = CreateObject("Scripting.FileSystemObject") strFullNm = DTSGlobalVariables("FilePathRoot").value & "IMS_ALL_DONATIONS.txt"
'check to see if file exist and then concatenate the file if objFSO.FileExists(strFullNm) then strFullNm = DTSGlobalVariables("FilePathRoot").value & " " & ms_date & "IMS_ALL_DONATIONS.txt" MsgBox "This is the new filename: " & strFullNm else MsgBox "File does not exist" end if
set objFSO = nothing Main = DTSTaskExecResult_Success End Function
I have a DTS Package Job that needs to pre-check a txt file (see below) with a 'Date' in it. TO compare it with the current Date (execution Date -> today). If they match, move on to the next step and fail otherwise. I don't know how to create an ActiveX script to do this kind of comparison.
----------------------------------------------------------------------- Volume Unit Referred SBR Used Recfm SSNE BlkSz Dsorg Dsname 5GSL4B 6760 2005/03/09 1065535 FB 3000 27000 PS 'AAS3P.QT.SECMRK.ZXWSDB.FULL.UNPACKED' -----------------------------------------------------------------------
I am tying to run this DTS: the job has an activex part which uses : "Set fso = CreateObject("Scripting.FileSystemObject")" this row gives me the following error: "ActiveX Scripting encountered a Run Time Error during the execution of the script. " the dts worked when I run it local on my station but when I try running it on the server this error comes can u help??? 10x
i need to loop through all the table(currently iam doing it using global variable) and send mail to the agents with messagetext as there orderno(s) I figured to send mail when an agent has one order but iam stuck when the agent has more than 1 order . Can you anyone please help /advice any better solution.
Gurus I have a DTS running in Sql Server 2000 and it is a Activex script.Now i want to migrate this DTS in 2005?Can you please explain the steps.. My problem is when i execute this package in 2005 in busines intelligence studio it turns red.. What does that mean?Itz a simple script which calls some URL
I migrated the DTS from 2000, and the migrated SSIS which includes ActiveX script is 11KB, takes 00:00.125 running. I rewrote the SSIS using some new features provided in BIDS, and the new package is 50KB, takes 00:00:6.016 running. Is that normal or maybe because of the efficiency of my code?
I'm getting a little confused about what is supported in MS-SQL ActiveX andwhat is Visual Basic and what is VBScript. Can someone please point me to awebsite, recommend a book or if I've missed it where in the MS-SQL help thatdeals with the ActiveX supported in SQL. I'm not looking to do web pagesASP, ect. I'm looking to use the ActiveX as add on funtionality to supportthe Stored Procs I write.Thanks,-p
hi all I'm working on a complicated activex script which is a part of a DTS package. Are there some debuggers around that could help make the task easier? Specifically I'm looking for an ActiveX script debugger. Can somebody point me to a tutorial or something? Regards
hey guys i have a code that looks something like this set mySourceConn = CreateObject("ADODB.Connection") mySourceConn.Open = "Provider=SQLOLEDB;Server=(local);Database=server_name;User Id=sa;Password=saPWD"
this works fine but the problem that i'm having is we do not want to hard code the password in the connection string. so i was wondering if there is anyway to access the DTS Connection object from the ActiveX. i was able to get the object but it is in connection2 datatype and i am not familiar of using this datatype. Code below returned the connection2 object 'DTS_Conn is the name of the object i specified in the DTS'DTS_Conn --> SQL Server Connection objectDTSGlobalVariables.Parent.Connections("DTS_Conn") can i use this connection2 object to run the sql statement and get the recordset?
I have been trying to create, then call an activeX object. I use sp_OAcreate and then sp_OAmethod to create the object, the call the method within. I have registered the DLL but I can not get it to work. I used the examples within T-SQL help but it doesn't seem to work. I am calling from within a sp within the northwind db. I need to pass 4 parms to the method. Here is the proc.
CREATE PROCEDURE SIMPLE2_SP (@CUSTOMERID nVARCHAR(25) = NULL, @ORDERID INT = NULL) AS
DECLARE @BLOB nVARCHAR(1200), @PARM1 nVARCHAR(25), @SF INT, @pOBJ INT
/***Make sure that the customerid is specified, if not, get out. sfogli 7-17-00 ***/ IF (@CUSTOMERID is NULL) OR (@CUSTOMERID = '') BEGIN RAISERROR('Must specify a CUSTOMER name.',-1,-1) RETURN (1) END
/***Hardcode the path, customerid, and orderid for testing purposes. sfogli 7-17-00. ***/ --SELECT @BLOB = 'PATH=C:REPORT.HTM;CUSTOMERID=' + @CUSTOMERID + ';' + 'ORDERID=' + CONVERT(VARCHAR(5),@ORDERID)+ ';' SELECT@PARM1 = 'PATH=C:REPORT.HTM;'
/***Make sure that the orderid is specified, if not, raise message. sfogli 7-17-00 ***/ IF (@ORDERID is NULL) OR (@ORDERID = '') BEGIN RAISERROR('To continue process, please specify an ORDERID and retry.',-1,-1) PRINT @BLOB RETURN (1) END
I'm looking for a way to get the name of the server on which the DTS package lives.
I copy packages between servers. The problem is that everytime a package is copied to different server, I have to change the reference in the connection strings to point to the new server name. I'd like to find an automatic way to interrogate the server name where the package currently lives and dynamically change connection strings from within an ActiveX task. That would cut maintenance way down.
Hello, Does anyone know how I add the SQLDMO library to enable me to reference it in DTS Active X scripts? I do not want to install VB on the server to do this.
