April 19, 2002 at 9:54 am
I am currently unable to afford the luxuries that SQL 2000 provides and is wondering if anyone can help me change a destination table at runtime based on a certain value. I am reading in a consolidated text file consisting of 3 separate log files with a value (1,2,3). I am wanting to form one transformation and ship each log and value to a separate table for processing. Can anyone help?
April 19, 2002 at 10:28 am
You will have to use an ActiveX task to do this. Basically you will have to set a variable reference to the package, then get the table name you need and finally, find the connection that you need to change and alter it's value. Then have your transform run.
It's easier to do this, IMHO, with two packages. One to read the text file and make the change in a second pacakge. Then run the second pacakge.
Steve Jones
April 19, 2002 at 10:47 am
I guess I am not understanding the object model or vbscript enough to perform this. How would I located the destination object name to change?
April 24, 2002 at 9:32 pm
An easy way to do this be to used a staging table and a stored procedure or insert query. Import the text file into a staging table using DTS if you like. Use a stored procedure (or 3 SQL insert statements) to copy from the staging table to the correct destination table based on the (1,2, or 3) value. If the source and destination happen to be on different servers, use linked servers. Personally, I would probably not use DTS at all for this type of thing since it simply adds an extra layer of complexity to the solution, without much added benefit.
April 24, 2002 at 9:46 pm
Thanks for the input. I will take a look at that suggestion. The current method that I have working is to use the FileSystemObject command. By doing this I can split into 3 separate text files and upload simutaneously.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply