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.
View 2 Replies
ADVERTISEMENT
Feb 27, 2007
when i run activex Script it's shows this error
[ActiveX Script Task] Error: Retrieving the file name for a component failed with error code 0x001B6438
View 2 Replies
View Related
Sep 13, 2006
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?
Jay Abbott
View 1 Replies
View Related
Jan 16, 2004
Hi,
Thanks for reading.
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.
Thanks in advance,
Steve
View 1 Replies
View Related
Apr 8, 2003
Hi,
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
Thanks
:)
View 3 Replies
View Related
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.
Does anyone know why this is occuring?
View 1 Replies
View Related
Aug 23, 2007
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.
View 2 Replies
View Related
Jun 11, 2007
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 :-)
View 7 Replies
View Related
Jul 3, 2007
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.
TIA,
Bob
View 1 Replies
View Related
Jul 18, 2000
Thanks in advance for your help...
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;'
/***Append BLOB with revelant data from customer table, sfogli 7-17-00.
***/
SELECT @BLOB = (SELECT'CustomerID=' + CUSTOMERID + ';' +
'CompanyName=' + COMPANYNAME + ';' +
'ContactName=' + CONTACTNAME + ';' +
'ContactTitle=' + CONTACTTITLE + ';' +
'Address=' + ADDRESS + ';' +
'City=' + CITY + ';' +
--Contains null values'Region=' + REGION + ';' +
'PostalCode=' + POSTALCODE + ';' +
'Country=' + COUNTRY + ';' +
'Phone=' + PHONE + ';' +
'Fax=' + FAX + ';'
FROMCUSTOMERS
WHERECUSTOMERID = @CUSTOMERID)
/***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
/***Append BLOB with revelant data from orders table, sfogli 7-17-00.
***/
SELECT@BLOB = @BLOB + (SELECT'OrderID=' + CONVERT(VARCHAR(5),ORDERID) + ';' +
'CustomerID=' + CUSTOMERID + ';' +
'EmployeeID=' + CONVERT(VARCHAR(7),EMPLOYEEID) + ';' +
'OrderDate=' + CONVERT(VARCHAR(15),ORDERDATE) + ';'
/***'RequiredDate=' + REQUIREDDATE + ';' +
'ShippedDate=' + SHIPPEDDATE + ';' +
'ShipVia=' + SHIPVIA + ';' +
'Freight=' + FREIGHT + ';' +
'ShipName=' + SHIPNAME + ';' +
'ShipAddress=' + SHIPADDRESS + ';' +
'ShipCity=' + SHIPCITY + ';' +
'ShipRegion=' + SHIPREGION + ';' +
'ShipPostalCode=' + SHIPPOSTALCODE + ';' +
'ShipCountry=' + SHIPCOUNTRY + ';'
***/
FROMORDERS
WHEREORDERID = CONVERT(VARCHAR(5),@ORDERID))
EXEC @SF = SP_OACREATE "PMIDocHandler.HTMLDocs", @pOBJ OUT
IF @SF <> 0 GOTO ERR
--(sPath, sCustomer, sContractID, sKeyValuePairs)
EXEC @SF = SP_OAMETHOD @pOBJ, "CreateDocument" ,@PARM1, @CUSTOMERID, @ORDERID, @BLOB
IF @SF <> 0 GOTO ERR
ERR:
RAISERROR('THIS IS ONLY A TEST.',16,1)
--SELECT @BLOB AS 'VARIABLES PASSED'
RETURN
View 2 Replies
View Related
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.
View 1 Replies
View Related
Mar 21, 2007
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 ?
tia.
View 4 Replies
View Related
Mar 29, 2006
Hi all
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
Chris
View 6 Replies
View Related
Jan 21, 2008
Hi All,
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
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
Could any one please help me in this regard, of accessing activeX script with connecting to data base.
I' ll be so kind of any immediate response.
Thanking You
Ganesh
View 1 Replies
View Related
May 21, 2007
Hi,
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.
-Prashant
View 4 Replies
View Related
Jan 11, 2007
Hi,
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.
Hope to hear your ideas.
View 7 Replies
View Related
Dec 6, 2007
Hi there
We have a SSIS run which runs as follows
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.
Thanx,
/Nils M - Copenhagen
View 3 Replies
View Related
May 9, 2007
Hi,
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 ?
View 5 Replies
View Related
Apr 23, 2001
How do I use ActiveX script in DTS to do a 'Save As' on an excel file and
convert that to a tab delimited text file ?
Help is much appreciated.
Hayes
View 2 Replies
View Related
Jan 29, 2004
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?
Thanks a lot for your help
View 3 Replies
View Related
Sep 26, 2006
hello friends, i heard from my colleagues that xml and activex are necessary for DBA
is it so? how could we use in our daily activities?
can any one provide me good links for this concepts please?
thank you very much.
View 8 Replies
View Related
Oct 30, 2006
There must be a difference, but I don't know what it is.
Can someone please explain the advantages and disadvantages of each method?
Thanks in advance.
View 17 Replies
View Related
Apr 28, 2005
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.
View 1 Replies
View Related
Aug 12, 2000
Hi there,
Can I be able to call a Activex DLL from Sp or from triggers.
Sachi
Looking for a answer..
View 1 Replies
View Related
Jan 19, 2001
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
Main = DTSTransformStat_OK
End Function
View 1 Replies
View Related
Jan 19, 2000
Hi,
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
----
Q - Whats wrong ?
View 1 Replies
View Related
Oct 17, 2003
I get this error msg when I run my ActiveX script in a DTS package.
Err number: 429
Err Message: ActiveX component can't create object
When I Set crApplication = CreateObject("CrystalRuntime.Application.9")
if Err.Number <>0 then
'I get the message here
ne one know what this is about? I'm running this package on SQL server 2000 with Admistrative access
View 5 Replies
View Related
Feb 22, 2005
Hi all,
I have a problem with a SQL Agent JOB on SQL 2K SP3 installation (on Win2K Server).
SQL Agent is running under a domain account with local Admin privileges and SA privileges in SQL server.
A job with an ActiveXScript step fails with "Permission denied: 'CreateObject line..."
If I execute the same ActiveXScript as cmdexec step (calling CSript with the VBScript as parameter) it works without any problem.
The ActiveXScript for reference:
Dim oXls
Dim oWorkBook
Dim oSheet
Set oXLS = CreateObject ("Excel.Application")
oXls.Visible = True
oXls.DisplayAlerts = True
Set oWorkBook = oXLS.WorkBooks.Add
Set oSheet = oWorkBook.ActiveSheet
oSheet.Name = "Test AG"
oWorkBook.SaveAs "C:TempTestAg.Xls"
oWorkBook.Close
Set oSheet = Nothing
Set oWorkBook = Nothing
oXls.Quit
Set oXlS = Nothing
Does anybody have any idea why approach 1 fails while approach 2 does work happily?
View 2 Replies
View Related
May 12, 2001
Hi all,
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
Can u help me out ?
Thanks & Regards
Rajanikanth
View 1 Replies
View Related
Aug 19, 2004
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.
Function Main()
ms_year = year(date())
ms_month = month(date())
ms_day = day(date())
ms_date = ms_year & "_" & ms_month &"_" & ms_day & "_"
dim objFSO, strFullNm
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
View 1 Replies
View Related
Mar 9, 2005
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'
-----------------------------------------------------------------------
Thank you for any suggestion!
J827
View 1 Replies
View Related
Oct 6, 2005
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
View 1 Replies
View Related
Aug 2, 2005
Hi gurus,
I am trying a accomplish a mail task which gets its information from the tables. iam using DTS[Activex and send email tasks].
table info:
agentid agentmail orderno
1 xx@hh vinet
2 xy@hh husk
1 xx@hh husk
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.
Thanks
Cindy
View 8 Replies
View Related
