November 17, 2005 at 7:30 am
Hi all,
I have a set of csv files that need to be loaded into a set of tables. Each CSV has a different number of columns and loads into a table of the same format.
I want to have one DTS package for this with an ActiveX script. I can dynamically pick up the correct file (based on a prefix held as a Global Variable). I can derive the db tablename from this prefix but my question is: Can i dynamically set the destination tablename and will DTS handle the column mappings (they are a straight 1:1.
I've looked around and found the properties but can find nothing about mapping.
Can someone advise please?
Thanks,
Dan
November 17, 2005 at 7:51 am
You'd have to work through the object model for your pacakage in the DTS ActiveX to move the mappings. It's possible, heck you could build the entire package at runtime.
One way to help is to save the package as a VB code and then dig through it to get an idea of the object names for your transform task. Then change these around as you go.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
November 17, 2005 at 7:56 am
Thanks.
I've found a little more and it all points to your suggestion.
The only reason that I'm going to all this trouble is that I'd end up with 49 identical packages (apart from source/target names) and I'm not changing code in 49 places (because I know I'll need to!!)
Dan
November 18, 2005 at 8:43 am
You can probably use a stored procedure to call the package (refer to SQLDTS.com for an example on how to do this). In the SP, make call the package once for every table, populating the package's global variables with appropriate info (table name, etc.). In the package, use FSO to read the CSV and create the table dynamically. Remember that you can populate global variables at any time from a SQL task using the output parameter. You can also populate GVs from an Activex task. In the package, use an Activex Task you can map the input to the output in a way similar to that used by the wizard when it creates a simple mapping for you. The net result of all this is a single DTS package, called by a single stored procedure that loops thru metadata using a cursor, then calls the package which does the work.
All this is apparently convoluted, but mastering this technology will help you in the future. The fusion of SPs and DTS has been a significant factor in improving my architectural skills in general and SQL-related technology in particular.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply