Im trying to design and create an extraction system which can be used to extract data from Oracle to SQL server and looking for some feedback on how best to achieve this. Here is the situation. Oracle source, has many tables. I want anywhere from one to all of the tables and need an easy system to specify which tables to extract. The extracted data is then stored on the SQL server which could have the exact same table name, column name, and comparable column type. One caveat is that there could be invalid data in the Oracle system, those rows should just be logged and redirected to an error table where all the columns are of string type for easy insertion. The most important requirement is the easy of use for the user to specify which tables to extract from. Here is my current solution. I have a single SSIS package for each of the tables that I need to extract from. The issue with this is the manageability of each one of the packages and creating new packages for each new table that I want to extract from. I am using config files to specify the source connection information, and if the locations of these change, I have to go into each package to change the location in Package Configurations. The upside is that there is easy redirect of rows to the error table. As you can see, the manageability of this gets out of hand. What I have so far. I have a table driven system in the SQL Server destination where in one of the tables I specify the table name and the columns I want to extract from the source. Using sqlbulkcopy I extract the data and write it into the SQL destination. Everything seemed rosy, but since sqlbulkcopy is a transaction, I cant seem to be able to trap the error rows and redirect the error rows into that tables error table. I believe that the only way is to do use sqlcommand and do a select first and then an insert. I am able to create the select statement dynamically, but im unsure how best to proceed from there. Once I have the select statement, how do I execute the select statement, grab that data and write it into the SQL server destination? An INSERT command requires parameters, specifically the data type, which I wouldnt be able to specify the SQLDbType in my code because that would mean each one of the tables column types would have to be specified. Eg. cmd.Parameters.Add("@empname",SQLDbType.Varchar,10); cmd.Parameters["@empname"].Value=txtname.Text; cmd.Parameters.Add("@empaddress",SQLDbType.Varchar,10); cmd.Parameters["@empaddress"].Value=txtaddress.Text; Anyone have any suggestions on how best to tackle this? If anyone has any suggestions on how I can redirect my error rows with the sqlbulkcopy, that would be ideal because that solution seems to already work all upto the part of the error rows. ThanksYou are so wise...like a miniature budha covered in fur. -Anchorman

Hi, In my opinion, SSIS would be more appropriate. SQLBulkCopy has few error handling facilities in SQLBulkCopy. Instead of creating a SSIS package for each table, I think we can have multiple Data Flow tasks in one single SSIS package for move all data from Oracle to SQL Server. In this way, we do need to specify the source connection information many times for packages and can get the failed rows easily. Please remember to mark the replies as answers if they help and unmark them if they provide no help. Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.

Hi, In my opinion, SSIS would be more appropriate. SQLBulkCopy has few error handling facilities in SQLBulkCopy. Instead of creating a SSIS package for each table, I think we can have multiple Data Flow tasks in one single SSIS package for move all data from Oracle to SQL Server. In this way, we do not need to specify the source connection information many times for packages and can get the failed rows easily. Please remember to mark the replies as answers if they help and unmark them if they provide no help. Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.

Actually, there is a need for each package to be different because each extract is an incremental extract. There is logic in each package that picks up the last datetime stamp and that is how it identifies what record to start the extract from.You are so wise...like a miniature budha covered in fur. -Anchorman