Need to import source filename with data

Question

Post reply

Need to import source filename with data

Bryan-261831

Grasshopper

Points: 21
More actions
September 20, 2005 at 8:23 am

#165991

With SS2K, is there a way to tell DTS to pull in the source file name along with the data?

I'm loading client .csv files of format

NAME_ID_DATE.csv

and I need to pull the NAME, ID, and Date values (or the whole file name) into a "DATA_SOURCE" field on my staging table.

Thanks for the help!

Viewing 7 posts - 1 through 6 (of 6 total)

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

Jonathan Stokes SSCrazy Eights Points: 9861 More actions · Answer 1

As far as I know or have found out, there is no way of retrieving the value of a source file within an activex script. If anyone has the code, please share.

The approach I use will work for you and is more dynamic to boot.

Firstly create a global variable in the package. Call it gvSource and make it a string.

Add a Dynamic Properties Task into your package. Click on add/connections/source/ and open OLE db properties. Dbl Click on data source and dropdown the source to global variable and select the variable gvSource.

Before you execute the package, set the global variable to the filename. The dts package will then run as before. But now, we have the filename stored somewhere we can get at it.

Create an activex transformation in the package and use this code:

DTSDestination("columntopopulate") = DTSGlobalVariables("gvSource").Value

If you know vb script, you can use 'Instr' to pull out each part of the filename and populate it into separate columns.

You also have a package now that runs dynamically, without needing to change the source connection manually.

To make it easier run something like this on a command line.

dtsrunui /S servername /N packagename /E /A "gvSource":"8"="sourcefilename"

All you have to change each time you run is the last parameter "sourcefilename". Leave all the double quotes to run.

If you are new to dts it may seem overcomplicated. Check BOL, this site and http://www.sqldts.com. Learn each step in turn and it is very, very easy.

Good luck.

------------------------------
The Users are always right - when I'm not wrong!

Bryan-261831 Grasshopper Points: 21 More actions · Answer 2

I think I might have some of that code after spending the day digging... The key appears to be FileSystemObject. This does correctly load the source filename into my table.

Function Main()

Main = DTSTransformStat_OK

Set fso = CreateObject("Scripting.FileSystemObject")

Set f = fso.GetFolder("C:\Somefolder")

Set fc = f.Files

For Each f1 in fc

GetAnExtension = fso.GetExtensionName(lcase(f1.name))

If GetAnExtension = "csv" then

If left(lcase(f1.name),8) = "customer" then

DTSDestination("DATA_SOURCE") = ucase(f1.name)

End If

The problem I'm having at the moment is that this is all done as part of the transformation, so although it appears to be cycling through each file, it's always on the last file by the end of the transformation. The net effect is that only the records from the last file are loaded. I need to find a way to do the whole thing programmatically. I haven't figured out how to reference the source (csv) and destination (SS2K table) yet though.

You're right in your assesment though. I'm quite new to DTS. I did Cognos and Oracle SQL reporting and just changed companies to a Microsoft shop. Thanks for the tip, hopefully I can get it running.

EDIT: I s'pose I should add that the other thing I'm trying to do is load data from all the files in a given directory that match my criteria above (csv files that start with "customer")

Ed Zann SSCrazy Eights Points: 8694 More actions · Answer 3

Check out this article on Looping and Importing multiple files via DTS:

http://www.sqldts.com/default.aspx?246

I have used this code in a modified fashion to import from multiple files and tag the records with the source file name and mod date. I have found SQLDTS.com is a great reference site when you need to accomplish something via DTS.

Jonathan Stokes SSCrazy Eights Points: 9861 More actions · Answer 4

I didn't know you wanted to expand the functionailty to the folder level.

I found this page much easier to follow and I have been using the technique for a year or so importing multiple excel files and capturing the name, again from the global variable.

http://www.databasejournal.com/features/mssql/article.php/1461661

If you have any problems, come back to this message.

------------------------------
The Users are always right - when I'm not wrong!