Get Server Name In ActiveX Where DTS Package Lives
Nov 2, 2000
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 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 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 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
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.
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 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 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.
Because the IIS threads are recycled at night, the first user that calls up Report Manager in the morning has to wait 30 seconds before the page loads, as IIS is creating a thread.
I'd like to set up a job in SQL Agent that will navigate to http://localhost/reports/ every morning before users come in to work, so they won't have to wait.
I think a SAL Agent job of type ActiveX Script would be suitable for this, so I need to write the code to navigate to the reports page in VBScript.
Anyone knows how to navigate to a page in VBScript ?
I am migrating some DTS2000 packages to SSIS via the migration wizard. However there is an activex script in each DTS that connects to an AS400 to retrieve record count for integrity checking. Under SQL server 2000 this worked fine but now it errors with "The OLE DB provider "MSADSQL" has not been registered" how do i get this to work i suspect its because this is a 32 bit dll and my new system is a 64bit sql server on windows 2003. The odbc driver for the as400 has been installed also. The script is as follows:
dim cn 'sql connection dim rs 'sql recordset for the insertion of new as400 records dim insertstr 'sql insert string dim rs1 'sql recordset for the insertion of new as400 records dim insertstr1 'sql insert string
set cn = createobject("ADODB.Connection") set rs = createobject("ADODB.Recordset")
Function Main() 'set up connection for sql cn.provider="sqloledb" cn.commandtimeout = 3600 'timeout in seconds cn.open %sqlserver05%, %user%, %password%
'populate temporary table in sql with recordset from as400 odbc insertstr1 = "Insert into db.dbo.%table% select 'table name',a.* from openrowset ('MSDASQL','DRIVER={Client Access ODBC Driver (32-bit)};SYSTEM=%system name%;UID=%username%;PWD=%password%','SELECT count(*) from %table%') as a"
set rs1 = cn.execute (insertstr1)
'close all objects cn.close Main = DTSTaskExecResult_Success End Function
Does anyone know how i change this to work under sql server 2005. i need to resolve this quickly in order to deliver a project on time. Many thanks
I' m trying to execute activeX script from a job. I could do it successfully. But when i tried to access data base the job is failed when running.
My Requirement : I would like to send a mail in a scheduled base using a job and activeX script.
I could send a mail giving static parameters. But as i need to make the parameter values of activeX script dynamic, i tried to connect data base and retrieve the required data as shown below. When i parse the script it showed success.But this is failing when it is running.
while creating a job i had selected ActiveX script option with vbscript language ////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// Set con = Server.CreateObject("ADODB.Connection") con.Open "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;password=welcome;Initial Catalog=MyDatabase;Data Source=(local); " Set rs = Server.CreateObject("ADODB.Recordset") Set rs = con.execute("select fromid,toid,seqid from MailsLog where status='N'") while rs.eof <> true con.execute ("update MailsLog set status='Y' where seqid = " & rs("seqid")) wend set rs = nothing set con = nothing
I created a package which passes some infornmations( through parameters) to its child package.
I need to do some processing in parent package based on execution status of child package.i.e.
if child fails then some operation and if child succeeds then other operation.
To determine the status of execution of child package I am using two differnt constraint ..one constraint is having value "Success" and other having value "Failure".
My problem is that when child packge is executed successfully the constraint with value = "Success" works properly but when child fails the constraint with value "Failure" does not work.
I'd like to know if there's a way to pass parent package parameters to a package executed by SQL Server Job Agent? It appears that sp_start_job doesn't have any variable that could accomodate this.
The master package has a configuration file, specifying the connect strings The master package passes these connect-strings to the child packages in a variable Both master package and child packages have connection managers, setup to use localhost. This is done deliberately to be able to test the packages on individual development pc€™s. We do not want to change anything inside the packages when deploying to test, and from test to production. All differences will be in the config files (which are pretty fixed, they very seldom change). That way we can be sure that we can deploy to production without any changes at all.
The package is run from the file system, through a job-schedule.
We experience the following when running on a not default sql-server instance (called dkms5253uedw)
Case 1: The master package starts by executing three sql-scripts (drop foreign key€™s, truncate tables, create foreign key€™s). This works fine.
The master package then executes the first child package. We then in the sysdtslog get:
Error - €œcannot connect to database xxx€? Info - €œpackage is preparing to get connection string from parent €¦€?
The child package then executes OK, does all it€™s work, and finish. Because there has been an error, the master package then stops with an error.
Case 2: When we run exactly the same, but with the connection strings in the config file pointing to the default instance (dkms5253), the everything works fine.
Case 3: When we run exactly the same, again against the dkms5253uedw instance, but now with the exact same databases defined in the default instance, it also works perfect.
Case 4: When we then stop the sql-server on the default instance, the package faults again, this time with
Error - €œtimeout when connect to database xxx€? Info - €œpackage is preparing to get connection string from parent €¦€?
And the continues as in the first case
From all this we conclude, that the child package tries to connect to the database before it knows the connection string it gets passed in the variable from the master package. It therefore tries to connect to the default instance, and this only works if the default instance is running and has the same databases defined. As far as we can see, the child package does no work against the default instance (no logging etc.).
We have tried delayed validation in the packages and in the connection managers, but with the same results (error).
So we are desperately hoping that someone can help us solve this problem.
I have packages stored in SQL store. I was letting users run the packages from a .net app that I made with
Microsoft.SqlServer.Dts.Runtime
Now I have noticed this causes the packages to run on the client pc cpu, as well as the network traffic is done via the client pc, in my particular case this is slow.
From the doc and in this forum I have found that you can run a package on the Server cpu through sql agent, let packages be run in a sql job. after that you can start a package from an application with the SQL sp_start_job .
But How do you set a user::varibale in a package if you have to start the package from a sql agent job ?
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.
