CODEWITHSUNDEEP
sql-serverssis
Jeroen Bolle
Jeroen Bolle

Reputation: 1836

Copying a dynamic number of tables into flat files using SSIS

Problem

I would like to copy a number of tables (around 100) into seperate flat files. The tables do not have the same number of columns.

So far

I have extracted which tables I wish to copy using a sys query and loop through each table using a for each container. The table name is stored in a variable. The for each container has a data flow task with an OLE DB Source component. This extracts all fields using a expression query (to insert the table name).

Now what?

I am stuck on how to save the data into a flat file. Each flat file should have the table name as filename (that can be done by declaring the connectionstring as an expression), but I am clueless on how to handle and bind the dynamic amount of columns.

Upvotes: 0

Views: 873

Answers (1)

grapefruitmoon
grapefruitmoon

Reputation: 3008

To be honest, SSIS doesn't play particularly nicely when you're looking to loop through files with differing columns. You'd probably be better using BCP in this case. It can output CSV files directly, and, if you can run xp_cmdshell, you can run through each of your tables in T-SQL. For example, something like the following...

declare @bcpsql varchar(8000)
select @bcpsql = 'bcp <tablename> out c:\<tablename>.txt -c -t, -T -S <yourserver>'
exec master..xp_cmdshell @bcpsql

Link to BOL on BCP...http://msdn.microsoft.com/en-us/library/ms162802.aspx

Upvotes: 1

Related Questions