Transform Data Task in DTS Pacakage
-
I have data that I am getting from one table from one ODBC source, and I want to transfer it to my other database, but when I do that I want to reorganize the data into different tables based on store procedures and/or the T-SQL statements that I provide.
-
Although dts is a valid ETL tool, I would use the term ETL very loosely.
It's primary function is to move data from one table to another. Transformation is generally limited to uppercasing or concatenating columns or adding columns up before hitting the target.
I presume you are looking to normalise a single table into maultiple tables with primary and foreign keys. If so, then you need to look at investing in a higher quality tool like Informatica.
The only way you will get dts to 'help' you to handle this, is to move the data from one single table to another and then call pre-prepared stored procedures to split the data into different tables using lookup queries to generate primary and foreign keys or generating sequences to use as surrogate keys.
------------------------------
The Users are always right - when I'm not wrong! -
i have the neseccary stored procedures. they have been tested, because I originally done this with VB, and using VB as the transfer source to my stored procedures. basically I am asking is how can I call my stored procedures from the ActiveX Scripts, and pass on the nesecary input variables.
-
Following standard ETL practices, load your data into a staging table the use multiple ExecuteSQL tasks to execute your stored procedures in required order. This will allow you to process the data in the fastest possible method.
You could also try using multiple Data Driven Query tasks, but that will have its own performance overheads.
--------------------
Colt 45 - the original point and click interface
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply