Dynamic Text source

Question

Post reply

Dynamic Text source

lof

Default port

Points: 1498
More actions
August 19, 2002 at 12:49 pm

#105158

I have a need to load text files with varying filenames using the DTS. What is the best approach to set up the connection object. I know that I can set up the ActiveX script that will open the text file and INSERT it line by line into my table, But BOL says that this is not the best way to do it performance-wise.
Is there a way to programatically assign different filenames (from a Global Variable) to the TextSource connection object?
Thanks,
Jakub

Viewing 5 posts - 1 through 4 (of 4 total)

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

mhweiss SSCarpal Tunnel Points: 4284 More actions · Answer 1

Take a look at the following function that you can execute via an ActiveX task...

Function DynamicFileImport

Dim objPackage

Dim objConn

Set objPackage = CreateObject("DTS.Package")

objPackage.LoadFromSQLServer "yourservername","","","256",,,,"textfileimportpackagename"

objPackage.GlobalVariables.Item("gvfiletoget").Value = InputBox("enter complete file path and name","Dynamic Text File Import")

If Len(objPackage.GlobalVariables.Item("gvfiletoget").Value) > 0 Then

Set objConn = objPackage.Connections("nameofyourtextimporttaskhere")

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

objPackage.Execute

End If

objPackage.Uninitialize()

Set objPackage = Nothing

End Function

You will need to have your import text file package set up. Then reference it in the function above and call the function via your Function_Main in the ActiveX task. Alternatively, you can just paste the code sans the opening and closing (Function.../End Function) lines in your function main procedure.

hth,

Michael

PS - I am sure you will need to modify the code somewhat to fit your own needs but it should point you in the right direction...

Michael Weiss

David Benoit SSC-Dedicated Points: 34562 More actions · Answer 2

We use the Dynamic Properties Task within DTS to point the "Text File Source" connection to the filename we are looking for. Our situation allows for this as we have files that get tagged with MonthYear information and we are able to build either a SQL script or ActiveX script to point the "Text File Source" connection to the "new" file name.

Once you walk through the Dynamic Properties Task once it becomes pretty clear.

Just another thought and I hope this helps.

David

@SQLTentmaker

“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

lof Default port Points: 1498 More actions · Answer 3

Thanks,

That should work. Actually I found a solution which works for me. There is a DynamicProperties Task in DTS, that alowsme to assign a Global Variable to the filename.

Thanks for your help,

Jakub

lof Default port Points: 1498 More actions · Answer 4

Thanks David,

I guess our posts have just crossed. The dynamic task works for me as I can set the global variable from the web page.

Jakub