SSIS: How to import many tables?
-
Dear all, I am new to the SSIS. I have many tables from old database (SQL 2000) and have to transfer to a new database (SQL 2012). I have created one SSIS project and have successfully shipped the data of one table already. I have also added a 'Execute SQL Task' to clean out table and reset identity column before the 'Data Flow Task'. Now what is the best way to import the rest? Import all tables in just one package? Or create one package for each table and create one package to 'call' all other package one by one? I think it is possible to create one package and use parameter to go over a pre-defined set of tables.
-
Dear all, I am new to the SSIS. I have many tables from old database (SQL 2000) and have to transfer to a new database (SQL 2012). I have created one SSIS project and have successfully shipped the data of one table already. I have also added a 'Execute SQL Task' to clean out table and reset identity column before the 'Data Flow Task'. Now what is the best way to import the rest? Import all tables in just one package? Or create one package for each table and create one package to 'call' all other package one by one? I think it is possible to create one package and use parameter to go over a pre-defined set of tables.
Why SSIS, do you expect to have to repeat this process? If it is a one off exercise then I would set up a linked server and drag the data across using scripts, saving the scripts of course. Actually I would backup and restore onto the 2012 server if it will work, I'm not sure the restore would work over such a wide version range!
Never underestimate the power of human stupidity RAH
-
Why SSIS, do you expect to have to repeat this process? If it is a one off exercise then I would set up a linked server and drag the data across using scripts, saving the scripts of course. Actually I would backup and restore onto the 2012 server if it will work, I'm not sure the restore would work over such a wide version range!
Never underestimate the power of human stupidity RAH
Well, I have already imported several tables. And the last one is a painful one. As for the last one, I still have to split that (old) one into five new tables. I think I am going to try to use the linked server approach. Thanks.
-
Dear all, I am new to the SSIS. I have many tables from old database (SQL 2000) and have to transfer to a new database (SQL 2012). I have created one SSIS project and have successfully shipped the data of one table already. I have also added a 'Execute SQL Task' to clean out table and reset identity column before the 'Data Flow Task'. Now what is the best way to import the rest? Import all tables in just one package? Or create one package for each table and create one package to 'call' all other package one by one? I think it is possible to create one package and use parameter to go over a pre-defined set of tables.
If it is a one off work out then I would set up a connected hosting server and move the information across using programs, preserving the programs of course. how to franchise your business