Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
scabral7
Yak Posting Veteran
57 Posts |
 Posted - 2010-05-22 : 21:00:51
|
| does anyone know if it is possible to use a list of tables to use in the Transfer SQL Server Objects Task step in SSIS for the Object List parameter? I want to move about 500 tables from SQL 2000 to Sql 2005 using SSIS but i don't want to have to check each table off, rather i have the table names stored in a table and would like to query that and have that list be the used for the Object List for my tables.Any ideas if this is possible? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
scabral7
Yak Posting Veteran
57 Posts |
Posted - 2010-05-22 : 23:38:26
|
| Hi Tara,the 2005 database does already exist, but i have about 500 tables that need to be 'refreshed' with data from the 2000 database. I'm curious about the bcp process. Do you have an example of how to write the script to pass data from each table to the 2005 db? |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2010-05-22 : 23:44:43
|
This will get you started and is from memory:Run this on the 2000 system to generate the output:SELECT 'bcp dbNameGoesHere.dbo.' + TABLE_NAME + ' out c:\temp\' + TABLE_NAME + '.csv -t, -Sserver1\instance1 -T -c -r\r\n'FROM INFORMATION_SCHEMA.TABLESWHERE TABLE_NAME ... <-- this is where you'll need to do some workThe above should produce all of the commands that you'll need to export the data to flat files in comma delimited format.Now copy/paste that into a batch file (.cmd or .bat) and run it. Next generate the import commands by running the above query on the 2000 system with a slight difference:SELECT 'bcp dbNameGoesHere.dbo.' + TABLE_NAME + ' in c:\temp\' + TABLE_NAME + '.csv -t, -Sserver2\instance1 -T -c -r\r\n'FROM INFORMATION_SCHEMA.TABLESWHERE TABLE_NAME ... <-- this is where you'll need to do some workMake sure that the first SELECT points at your 2000 system for the -S switch and that the second one points at your 2005 system.Now copy the above output into another batch file and run it. This should import all of the data from the files into your 2005 system.I probably have the commands a little off, but it should give you a good start. The key is to make sure that the output of each SELECT is correct before putting them into a batch file. On Monday, I'll be at work and in front of a SQL Server system, so I can better help you out if you run into any problems. Let me know your progress on this. I will probably check SQLTeam tomorrow too, but any help I provide will be just my best guess as I don't plan on connecting to a SQL Server system this weekend!  Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2010-05-22 : 23:48:57
|
| I didn't notice until now that you have the table names stored in a table already. So replace INFORMATION_SCHEMA.TABLES in my queries with your table and the TABLE_NAME column too.The point of my queries is to generate the bcp commands.By the bcp is an executable (bcp.exe) that allows you to import/export data in SQL Server. It isn't as powerful as SSIS, but it doesn't sound like you need to transform any data here, so bcp.exe should work just fine. You'll have bcp on your system if you have the SQL client tools installed. If it's not in your computer's path variable, then you can fully qualify it in the above queries. It'll be located in the BINN directory somewhere underneath Microsoft SQL Server folder. I'd look it up for you, but I don't have SQL Server on the system I'm using right now.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
scabral7
Yak Posting Veteran
57 Posts |
Posted - 2010-05-23 : 01:17:33
|
| Thanks Tara,i guess i'm just worried about how big these files will be and if i have enough room to store them on the server. I may have to to them 20 or 30 at a time, but i'm not sure. I don't really know how much data each table has, but i'm sure some of them are rather large. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2010-05-23 : 12:12:05
|
| What about Constraints? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
|
|
|
|
Transfer SQL Server Objects Task