SSIS - Export multiple SQL Server tables to multiple text files

Question

I have to move data between two SQL Server DBs. My task is to export the data as text (.dat) files, move the files and import into the destination. I have to migrate over 200 tables.

This is what I tried

1) I used a Execute SQL task to fetch my tables. 2) Used a For each loop to loop through the table names from the collection. 3) Used a script task inside the for each loop to build the text file destination path. 4) Called a DFT with the table name in a variable for the source ole db and the path name in a variable for the destination flat file.

First table extracts fine but the second table bombs with a synchronization error. I see this is numerous posts but could not find one that matches my scenario. Hence posting here.

Even if I get the package to work with multiple DFTs, the second table from the second DFT does not export columns because the flat file connection manager still remembers the first table columns. Is there a way to get it to forget the columns?

Any thoughts on how I can export multiple tables to multiple text files using one DFT using dynamic source and destination variable?

Thanks and appreciate your help.

I assume there's a reason you don't just shove data from A to B and skip the whole file business? — billinkc
– billinkc, Commented Oct 10, 2014 at 15:18
Yes. The destination could be Production sometimes and our DBA and Security does not allow this for various reasons. Sometimes we may have to hold a particular day's data and then reapply after a couple of days in the target. These two are the reasons. — Web_developer_beginner
– Web_developer_beginner, Commented Oct 10, 2014 at 15:22
Fair enough. The short answer is that you cannot do what you're attempting to do. The data flow is tightly bound to the source meta data and that cannot be changed at run time. If you can install BIDS Helper, I can show you how to automate your package generation with some Biml — billinkc
– billinkc, Commented Oct 10, 2014 at 15:29
Thank you for the speedy answer. I will check if I can install BIDS Helper in my company and get back to you. — Web_developer_beginner
– Web_developer_beginner, Commented Oct 10, 2014 at 15:38
There is a no install option with bids helper. It is the mechanism for turning biml into ssis packages. Pretty slick. Also, a good all around addition to your bi development experience — billinkc
– billinkc, Commented Oct 10, 2014 at 16:02

madhav bitra · Accepted Answer · 2014-12-10 13:50:57Z

1

Unfortunately Bulk Import Task only enable us to use format files effectively to map the columns between source and destinations. Bulk Import Task uses BULK INSERT TSQL command to import the data, to execute user should have the BULKADMIN server privilege.

Most of the companies would not allow BULKADMIN server privilege to enable due to security reasons. Hence using the script task to construct BCP statements is a good and simple option to Export. You does not require to construct .bat file as script itself can execute dos commands which runs under .NET security account.

answered Dec 10, 2014 at 13:50

madhav bitra

1751 silver badge3 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Web_developer_beginner Over a year ago

Thank you. Yes I had the same problem with the privileges. However, this solution fit my needs.

Web_developer_beginner · Accepted Answer · 2014-11-14 19:58:09Z

I figured out a way to do this. I thought I will share if anybody is stuck in the same situation.

So, in summary, I needed to export and import data via files. I also wanted to use a format file if at all possible for various reasons.

What I did was

1) Construct a DFT which gets me a list of table names from the DB that I need to export. I used 'oledb' as a source and 'recordset destination' as target and stored the table names inside a object variable.

A DFT is not really necessary. You can do it any other way. Also, in our application, we store the table names in a table.

2) Add a 'For each loop container' with a 'For Each ADO Enumerator' which takes my object variable from the previous step into the collection.

3) Parse the variable one by one and construct BCP statements like below inside a Script task. Create variables as necessary. The BCP statement will be stored in a variable.

I loop through the tables and construct multiple BCP statements like this.

BCP "DBNAME.DBO.TABLENAME1" out "PATH\FILENAME2.dat" -S SERVERNAME -T -t"|" -r$\n -f "PATH\filename.fmt"

The statements are put inside a .bat file. This is also done inside the script task.

4) A execute process task will next execute the .BAT file. I had to do this because, I do not have the option to use the 'master..xp_cmdShell' command or the 'BULK INSERT' command in my company. If I had the option to execute cmdshell, I could have directly run the command from the package.

5) Again add a 'For each loop container' with a 'For Each ADO Enumerator' which takes my object variable from the previous step into the collection.

6) Parse the variable one by one and construct BCP statements like this inside a Script task. Create variables as necessary. The BCP statement will be stored in a variable.

I loop through the tables and construct multiple BCP statements like this.

BCP "DBNAME.DBO.TABLENAME1" in "PATH\FILENAME2.dat" -S SERVERNAME -T -t"|" -r$\n -b10000 -f "PATH\filename.fmt"

The statements are put inside a .bat file. This is also done inside the script task.

The -b10000 was put so I can import in batches. Without this many of my large tables could not be copied due to less space in the tempdb.

7) Run the .bat file to import the file again.

I am not sure if this is the best solution. I still thought I will share what satisfied my requirement. If my answer is not clear, I would be happy to explain if you have any questions. We can also optimize this solution. The same can be done purely via VB Scripts but you have to write some code to do that.

I also created a package configuration file where I can change the DB name, server name, the data and format file locations dynamically.

Thanks.

Collectives™ on Stack Overflow

SSIS - Export multiple SQL Server tables to multiple text files

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related