Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm new to the whole SSIS world. I'm trying to create a process to copy data between SQL Server 2008 environments. I want to be able to run a job to export data from my prod database and then use that export to load the data into multiple dev instances of the database. I'm imagining two processes - one export and one import - that can be run separately.

The first question I have is what is the best format for the exported data?

I'm about 90% complete with a process to export the data to flat files, one flat file per table, but I'm wondering if that's the best way to handle it. Would it be faster to write to Excel or another format?

My process uses the Execute SQL task to get a list of all the table names and then uses a Foreach Loop containing a script task that writes the flat files. I may need to exclude some tables which is why I'm using the Execute SQL task to get the table names.

My second question is, once I have the data exported, what's the best way to import it?

I have a query that will order the table names so they get processed in the proper order for foreign keys, so I guess I'll have a similar process as above, where I have an Execute SQL task to run that query, then another Foreach to loop through the table names, and a script task to read the files and populate the tables. Is there a faster/better way to handle that import?

share|improve this question
    
can you connect to via network to all of your databases via SSMS ? –  user9399 Aug 1 '12 at 18:51
    
No. The export will create files that users without access to the prod database can use to import prod data into their own local dev databases. –  stomcavage Aug 1 '12 at 18:58
3  
NEver export to or import from Excel if you can possibly avoid it! Excel support in SSIS is atrocious. –  HLGEM Aug 1 '12 at 19:14
2  
If you don't have direct connectivity between source / target, why would you not just use backups? –  Eric Higgins Aug 1 '12 at 21:14
    
Can you post your export package? Thanks - SSIS newbie –  SSIS Newbie Jul 29 at 0:14

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.