Executing SQL in ActiveX Script Task
-
I want to put filenames from a folder on the servers hard drive into a table in my DTS Package SQL Server OLEDB connection. I can do this by issuing a "dir" command and directing the output into a text file on the hard drive, and then importing the text file with a data pump, and the inserting the filenames into the table using an ExecuteSQL task. This seems like a very caveman way of doing this, however.
What I want to do is use an ActiveX Script task; declare a folder object and a files collection; then loop through the files collection, inserting each file name into the SQL table using a SQL Query in the ActiveX Script task.
The problem is, I don't know how to execute a SQL Query on the existing SQL Connection in my DTS package from inside the ActiveX Script task. How would I do this?
-
at this url, http://www.sqldts.com/default.aspx?246, is the basics for making a DTS script where the task execution order is manipulated programmatically.
I would use an ActiveX step, and do it all in that step. You've got the right idea. Use a FileSysteObject, but also use an ADOCommand object to run a stored procedure that actually does the insert. You then just get the path and filename, and push those into the parameters for that item. When you're finished, you'll set the NextDTSTask to...well...
The only caveat about this is that to find the REAL name for the DTSStep items, you need to save your package once as a VBScript item. Whatever name you give a step via the DTS Designer is NOT what you use in the VBScript.
-
You can do the file system stuff with FileSystemObjects rather than your text file:
Dim objFso
Set objFso = CreateObject("Scripting.FileSystemObject")
Then see http://msdn.microsoft.com/library/default.asp?url=/library/en-us/script56/html/fsoorifilesystemobject.asp for all the nice things you can do.
The way I used to run a query on an existing connection in an ActiveX task might not be the best, but it does work. I set up an ADO connection in the task, and then set the connection string:
myConn.ConnectionString = "File Name=" & DTSGlobalVariables.Parent.Connections("myDB").UDLPath
This is only going to work if the original connection is set up with a UDL, but there are other properties of the connection object which you might also read to get your ADO connection to be the same.
Bill.
-
Thank you both. Creating and using the File System Object is something I am already familiar with.
What I am looking for is a way to execute SQL Code against the existing MS OLEDB connection which is already defined in the DTS Package from inside the ActiveX Task VBA Script. I have learned how to declare a separate ADO connection to the same database and run SQL against the ADO connection, but I haven't been able to find out if you can run SQL against the existing DTS package connection and so avoid having to create a second ADO connection. That is what I'm hoping is possible and someone knows how to do.
-
I agree with the previous post -- within the ActiveX Script, use FSO to loop through and get all of the filenames within a directory. Then you can use ADO to insert the values to your table, something like the following...
Dim myConn
Dim myRS
Set myConn = CreateObject("ADODB.Connection")
Set myRS = CreateObject("ADODB.Recordset")
'Set ADO connection properties.
myConn.ConnectionString = "Provider=SQLOLEDB;Data Source=yourServerName;Initial Catalog=yourDatabaseName;Trusted_Connection=Yes;"
SET UP LOOP USING FileSystemObject (FSO) TO GET THE FILE NAMES
myConn.Execute "INSERT INTO tbl_name VALUES ('filename_from_FSO')"
LOOP
-
Everything everyone is telling me, I already know.
Let me state this very clearly:
What I am looking for is if there is a way to execute SQL against the existing DTS MS OLEDB connection. Nothing more and nothing less. If SQL cannot be executed against the existing DTS MS OLEDB connection, then that would be good to know also. I know about File System Objects. I know about ADO connections. I have used them before. I don't need to learn how to use FSO's and ADO's.
I don't mean to offend anyone, but this is the third time I have asked this in this thread.
-
See http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dtsprog/dtspmethai_4p0k.asp
Execute Method
The Execute method executes a Data Transformation Services (DTS) task object.
-
Edwin,
I don't think you can do what you want to do. I was looking at doing something similar this week. I looked all over the internet, but I couldn't find a way of referencing an already created database object from an ActiveX script. What I was forced to do is create another connection manually in ActiveX using ADO and use that.
-
You can use and refer to the DTS connection object programmatically in the ActiveX script using the DTS object model. Once you think of it like that, you realize that it's kind of a silly question. Sure you can create an ExecSQL task for your connection programmatically, create the source sql for it and execute it - oh gee - you are already in the DTS gui so why bother? - it's a lot more code than good old ADO. This is however exactly what you would want to do if you were say, writing all of this in VB.
When in the GUI, use the GUI objects. You will be amazed how much you can do with only a little code once you understand the object model. You can manipulate DTS object properties programmatically - change a source sql string or a destination object on the fly - use the dynamic properties task - even loop your package dynamically. The point is - work with the GUI not against it.
Here is something you can do that is pretty useful and cool that does take advantage of your pre-existing connection object. Upstream to your ActiveX, you can dump a recordset from an ExecSQL task as a rowset type output parameter into a global variable. Then you can treat your recordset as an ADO recordset in the ActiveX script. Just
set oRs = GlobalVariables("goRs").value 'this came from the output variable
oRs.MoveFirst
Do Until oRs.EOF = TRUE
etc., etc.
Have Fun!
[font="Courier New"]ZenDada[/font]
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply