Changing the Source File in DTS

Question

Changing the Source File in DTS

jdoering

Valued Member

Points: 59
More actions
September 18, 2002 at 7:20 am

#105193

I am desperately trying to create a DTS package that imports an Excel spreadsheet (that has 5 worksheets). The problem with this is that the Excel worksheet changes names. I am successfully passing in a global variable to specify the file name using:
1 - Step is an ActiveX Task:
Function Main()
Dim oConn, sFilename, oPkg,
sFilename = "E:\eCSConnect\LoadTemplate_Lite_V12_" & DTSGlobalVariables("@Supplier_ID").Value & ".xls"
Set oConn = DTSGlobalVariables.Parent.Connections("Microsoft Excel 97-2000")
oConn.DataSource= sFilename
Main = DTSTaskExecResult_Success
End Function
2 - Step: Excel Connection to the file path and base name + globalvariable that changes. Looks like this: E:\eCsConnect\LoadProducts_?.xls
3 - Step: Data Transformation to a table in the database
The error I'm receiving is:
"Error Source: Microsoft Jet Database Engine
Error Description: Failure Creating File"
I think I understand why I'm getting this error because the literal excel file LoadProducts_?.xls does not exist. However, I want to make the DTS package realize that this will be a variable file name.
Has anyone done this before? Please advise.
Best regards,
Julie

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

mhweiss SSCarpal Tunnel Points: 4284 More actions · Answer 1

I do this with text files. The same logic should apply to your Excel files though. What I have done is set up a global variable in the package. Then using an ActiveX script, I open a fso object and search my directory for files that have a set naming pattern...in your case you could search for 'LoadProducts_' and a file extension of '.xls'. When found, set your gloabal variable to the file name by parsing the file name or using the fso object and then set your excel connection object to the global variable.

hth,

Michael

Michael Weiss

jdoering Valued Member Points: 59 More actions · Answer 2

Michael,

Thank you for your response. I am not a programmer so I am very lost here. What is an fso object? How would I set my excel connection to a global variable? I ask this because I have to drag and drop that Excel Icon into the DTS screen and it requires me to locate an exact file name. Please advise.

Thank you very much!

Best regards,

Julie

mhweiss SSCarpal Tunnel Points: 4284 More actions · Answer 3

Julie,

I have some code I will post in an hour or thereabouts when I get in the office. Until then, look up file system object in the vbscript help files, on microsoft.com/msdn (i think!), or in help in an activex task object (the help button on the code page itself)...

hth,

Michael

Michael Weiss

jdoering Valued Member Points: 59 More actions · Answer 4

Thanks Michael! I will look up file system object in the meantime.

Thanks,

Julie

jdoering Valued Member Points: 59 More actions · Answer 5

How do I set up my Excel Connection Object in ActiveX for this?

mhweiss SSCarpal Tunnel Points: 4284 More actions · Answer 6

Julie,

Here is some code that with a little modification should work for you...let me know if you have any problems! If your email address is listed on the site here, I will send a copy there as well because of word wrapping here (possibly)...

hth,

Michael

'**********************************************************************

' Visual Basic ActiveX Script

'************************************************************************

Function Main()

Dim responsesuccess

responsesuccess = 0

'NOTE: Uncomment these lines to check global variable values at runtime:

'MsgBox "gvfilepath: " & DTSGlobalVariables("gvfilerootpath").Value & vbCrLf & _

' "gvfileextension: " & DTSGlobalVariables("gvfileextension").Value & vbCrLf & _

' "gvarchivefolder: " & DTSGlobalVariables("gvarchivefolder").Value

responsesuccess = ImportFilesToStagingTable(DTSGlobalVariables("gvfilerootpath").Value, _

DTSGlobalVariables("gvfileextension").Value, DTSGlobalVariables("gvarchivefolder").Value)

If responsesuccess <> 0 Then

Main = DTSTaskExecResult_Success

Else

Main = DTSTaskExecResult_Failure

End If

End Function

Function ImportFilesToStagingTable(filepath, fileext, archivefolder)

'

'Accepts: filepath - string variable to hold full path to file (pass in global variable "gvfilepath")

'fileext - global variable holding extension of files we are looking to import, eg. ".xls"

'archivefolder - string variable to hold full path and

'folder name of archive folder (pass in global variable "gvarchivefolder")

'

'Returns: 1 if successful

' 0 if fails.

ImportFiles = 0

Dim fso

Dim objFolder

Dim objarchivefolder

Dim colFiles

Dim objFile

Dim objPackage

Dim objConn

Dim i

'NOTE: This code assumes your package is stored in sql server and not in the metadata repository

Set objPackage = CreateObject("DTS.Package")

objPackage.LoadFromSQLServer "your server here","","","256",,,,"your package name here"

'Here comes the File System Object we all know and love!

Set fso = CreateObject("Scripting.FileSystemObject")

Set objarchivefolder = fso.GetFolder(filepath & archivefolder)

Set objfolder = fso.GetFolder(filepath)

'msgbox objfolder.name

Set colFiles = objfolder.Files

i = 0

If colfiles.Count > 0 Then

For Each objfile in colfiles

If UCase(Left(objfile.name,13)) = "LoadProducts_" Then

If UCase(Right(objFile.name,4)) = ".XLS" Then

'Check file size - if greater than 0 then import using objPackage object instantiated above:

If objFile.Size > 0 Then

'Run package here and load your sql server table:

objPackage.GlobalVariables.Item("gvfiletoget").Value = filepath & "\" & objfile.Name

'MsgBox objPackage.GlobalVariables.Item("gvfiletoget").Value

'NOTE: uncomment MsgBox above to check variable value at runtime

Set objConn = objPackage.Connections("Connection 1") 'NOTE: you may have to substitute your

'connection object's (excel data source object) name here for "Connection 1"

objConn.DataSource = objPackage.GlobalVariables.Item("gvfiletoget").Value

objPackage.Execute

'Move the file to archive folder specified by gvarchivefolder:

If fso.FolderExists(objarchivefolder) Then

objfile.Move(objarchivefolder & "\")

Else

fso.CreateFolder(objarchivefolder)

objfile.Move(objarchivefolder & "\")

End If

i = i + 1

End If

'MsgBox "Total Files Processed: " & Cstr(i)

'Note: Clean up object variables to ensure release of system resources:

objPackage.Uninitialize()

Set objPackage = Nothing

Set objfolder = Nothing

Set colFiles = Nothing

Set objFile = Nothing

Set objConn = Nothing

Set fso = Nothing

ImportFiles = 1

End Function 'ImportFiles()

Michael Weiss

mhweiss SSCarpal Tunnel Points: 4284 More actions · Answer 7

The code I sent to you sets your excel connection object provided you have one in your package...just substitute the name of your connection object in the place of 'Connection 1' in the code...

hth,

Michael

Michael Weiss

A_Bomb Newbie Points: 3 More actions · Answer 8

Excellent! I have a similar problem but in the reverse. I need to dump 2 SQL tables to Tab delimited text files that include the column names as headers. I couldn't find a switch in BCP to include headers, so DTS was the only way. But the destination file names need to include the date they are dumped, i.e. tablename_yymmdd.txt. I can modify your code to dynamically change the filename in my Flat file destination connection at run-time. Thanks!

mhweiss SSCarpal Tunnel Points: 4284 More actions · Answer 9

You are more than welcome!

Michael Weiss