Executing SQL in ActiveX Script Task 1

[EdwinGene]

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?

 

[jhall01]

This will do it all within an ActiveX Script:

Dim oConn, opkg, tsql, fso, connection

	connection = "Provider=SQLOLEDB; Data Source=YourServer;Initial Catalog=yourDB;UID=username_cf;password=password;"
	SET opkg = DTSGlobalVariables.Parent
	Set oConn = CreateObject("ADODB.Connection")
	oConn.open connection

	set fso = CreateObject("scripting.FileSystemObject")
	set folder = fso.getfolder("c:\yourdirectory\")

	For Each file in folder.files

		tsql = "insert into yourtable values('" &  file.name & "')"
		oConn.Execute tsql

	
	next

	oConn.close
	set oConn = nothing
	Main = DTSTaskExecResult_Success

 

[EdwinGene]

Thank you, jhall. I only wish there was a way to use the existing DTS connection, instead of having to declare a new one. Oh, well. Maybe that will be a feature of the new Integration Services.

 

[jhall01]

I was thinking and you could create a SQL Task and dynamically set the SQL statement within the ActiveX For statement as opposed to creating a new connection and then build a long string of insert statements

set fso = CreateObject("scripting.FileSystemObject")
set folder = fso.getfolder("c:\yourdirectory\")
set oPkg = DTSGlobalVariables.parent
set oSQLtask = oPkg.tasks("DTSTask_DTSExecuteSQLTask_1").CustomTask

tsql = ''
For Each file in folder.files
  tsql = tsql & "insert into yourtable values('" &  file.name & "');"
next

oSQLtask.SQLStatement = tsql

You might have to throw in a line feed in there but this will prevent you from creating a new connection. And you will be limited to the size of the variable "tsql"

I actually like this method better and will be updating a couple of my packages!!!

 

[jhall01]

correction:

tsql = ''

Should read:

tsql = ""