Automate export from SQL server to multiple .CSV files

0
votes

I am trying to find a way to automate exporting data from SQL Server tables to .csv files.

Currently, I am using SSIS to export data but it's a manual process where I have to tell the package what .csv files to export to and can only do one customer at a time. I have a SQL table with over 1,800 customers and each customer has corresponding data in multiple tables. Is it possible to use SSIS, tell the package to traverse through the customer table, and export data based on the customer ID from two corresponding tables to two different .csv files?

Many thanks!

1
ssisexport-to-csv
Yes it is possible, but you need to give us more details: what the tables are, how they relate to each other, etc. – Code Different
What is SSID? Do you mean SSIS? If so, tag it such – Nick.McDermaid
The two tables related to the customer table have data such as documents and pricing. – NetSystemAdmin
Master package, Has an Execute SQL Task that dumps the Customer ID recordset into a variable. Use a Foreach Loop Container to shred that list. Inside the for each loop, have N Data Flows. In each Data Flow, use a Parameter driven Query against the table (select * from documents where customerId = ?) etc and export to a file. Use an Expression on the ConnectionString property for each output file to match the value being shredded. Some diligent bingling of these terms should provide plenty of examples. Just build it out one piece at a time and it shouldn't be bad. – billinkc

1 Answers

0
votes

you can automate csv file dynamically. you can do it using package level variables and foreachloop for the reference you can go through this link. just customize it as per your requirement. http://bi-polar23.blogspot.in/2008/02/dynamic-flat-file-destinations.html