March 26, 2004 at 10:22 am
I am loading a number of excel spreadsheets into a table. Because of the number of files, I created a dynamic DTS package to go check a directory and then load any files it finds. For each file it finds, it will execute a second DTS package that actually does the load. Prior to executing the 2nd package, it will define the filename to load as a global variable.
My problem is in the 2nd package. The worksheet name is different in every spreadsheet. I have an activeX script defines the name of the file prior to a datapump step, but how do I dynamically define the worksheet name?
The 2nd package looks like:
Step 1: Truncate the load table
Step 2: ActiveX script to define the filename
Step 3: DataPump to transform the data (The source is an excel spreadsheet and the destination is a database table.)
Step 4: Move data from load table in fat table.
The ActiveX script looks like:
Function Main()
Dim oConn, sFilename
sFilename = DTSGlobalVariables("gvFilename").value
Set oConn = DTSGlobalVariables.Parent.Connections("HistoryFile")
oConn.DataSource = sFilename
Set oConn = Nothing
Main = DTSTaskExecResult_Success
End Function
Any help would be greatly appreciated!
March 29, 2004 at 8:00 am
This was removed by the editor as SPAM
March 29, 2004 at 6:52 pm
Since you are using Active Scripting loop the the Worksheet objects to get the name and place in another variable. I don't have sample code here but email me I believe on of the guys at wokr has something like this.
March 31, 2004 at 6:41 am
Where can I find your email address?
August 11, 2005 at 9:50 am
Yes, I also need similar process. Could you please post the sample code. Thanks.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply