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?

  • 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 – user44574 Jul 29 '14 at 0:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.