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?
