May 26, 2010 at 5:17 am
I am in the process of creating SSIS Data Flow Package using the DataReader Source connecting to Informix database using ADO.net. When using the DataReader you must pass a query in with the connection string information, so my question becomes:
what is the best way to extract an entire database (all tables with data)...do I need to code out select statements for the whole thing (1,300 tables), create some custom code that iterates over all tables in the database and does a select *.....Hopefully you may see my point. I have to assume that someone has done this in the past. Maybe BCP is another way...Just trying to save as much time as I can due to the high number of tables. Thank you for time and any information.
Thanks For your continued Help.
May 26, 2010 at 5:34 am
I don't know how things work in Informix, but in SQL Server you have system tables that contain all the table names.
You could write a script that iterates over this system table, thus getting all the table names and all the column names.
Then, using this values, you could create with the script a SELECT INTO statement for each table.
Finally, create a linked server on your SQL Server that points to your Informix server. Then execute all the statements, and all your tables will be created with the data in it.
I have done it in the past to copy all the tables from an AS/400 (with openquery) to a SQL Server database and it works quite well, but it can take some time if it is a big database.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply