SSIS challenge

Question

Post reply

SSIS challenge

nightowl23

Mr or Mrs. 500

Points: 522
More actions
July 24, 2015 at 7:17 am

#300574

I have three tables in data base:
customer
product
sales
and i want to use SSIS package dynamically load data from database into three separate flat file, each table into each file.
i know i got to use for each loop task ADO.Net schema row set enumerator, with OLEDB connection manager, select table name or view name variable from access mode list, but the problem comes, as table name is dynamic then flat file connection is also dynamic, i am using visual studio 2013
has anyone got the experience?
many thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

If you have 3 different tables going to 3 different files, then you just need a single data flow task with the 3 flows.

Why do you want to make it dynamic with a loop? What do you need to be dynamic?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Jayanth_Kurup SSC-Insane Points: 22967 More actions · Answer 2

If you know you have three fixed tables and three fixed flat files , which part of your SSIS package are you expecting to be dynamic? is it the rows or the columns ? or does the table names change or the filenames change?

Jayanth Kurup[/url]

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 3

nightowl23 (7/24/2015)
I have three tables in data base:
customer
product
sales
and i want to use SSIS package dynamically load data from database into three separate flat file, each table into each file.
i know i got to use for each loop task ADO.Net schema row set enumerator, with OLEDB connection manager, select table name or view name variable from access mode list, but the problem comes, as table name is dynamic then flat file connection is also dynamic, i am using visual studio 2013
has anyone got the experience?
many thanks

Agree. You cannot design a flow for one table then reuse it for another table, unless the tables have the same structure.

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

nightowl23 Mr or Mrs. 500 Points: 522 More actions · Answer 4

It is doable to load multiple tables into files dynamically.

within ADO enumerator, use SQL task for BCP command:

declare @sqlStr varchar(2000)

set @sqlStr = 'BCP TESTDB.[dbo].[customer] IN B:\Users\Documents\SQL Server Management Studio\customer24072015.txt -T-c'

exec xp_cmdshell @sqlStr

however run this in SQL management studio, it complains

"Unknown argument 'Server' on command line."

can anyone help? thanks

Luis Cazares SSC Guru Points: 183706 More actions · Answer 5

Put the path between double quotes (").

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2