Transfer DATA from two tables into one CSV-File

Question

Post reply

Transfer DATA from two tables into one CSV-File

Austrianpower

Grasshopper

Points: 17
More actions
August 24, 2010 at 8:27 am

#225181

Hello Pro's, 🙂
Right now I try to create a SSIS-Package with which can transfer data from two tables into one csv-file.
The filename should also include some information from the first table.
for Example:
Table1:
IDDOCUMENTNUMBER VALUE
1471115,5
2471229,5
Table2:
IDARTICLEVALUE
115515
115618
215715
215519
225665
As a result the filename should be „4711.csv“ (Document Number from Table 1)
The file “4711.csv” should include the content from both tables with the same ID:
1471115,5
115515
115618
The next file should be “4712.csv” with following content:
2471229,5
215715
215519
225665
till the end of the first table...
I have done a some similar package before, but there I had to transfer only one
table into one file..
Hope someone can give me any suggestions on how to handle this. I’m thinking about
some for-each loop or something like that, but I have no experience in this. Maybe
someone can explain it to me.
Thanks a lot
Stefan

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

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

philip.coe SSC Journeyman Points: 79 More actions · Answer 1

Do you require a header row?

If not just do two data flows to the same connection manager in order that you want them exported.

Ensure that you select the option on the flat file conneciton manager to NOT overwrite existing date ( this is on by default).

If you do require one:

There are two ways to do this, 1 create two connection managers to the same file where the second connection manager does not include header rows and does not overwrite existing data.

The other is to do 3 data flows tasks, the first data flow being the header row (just type out a select statement with static values and map them to the appropriate columns).

The reason for this is that if you leave the header row option on for both connection manager's it will write additional header rows per data flow in the dataset.

Austrianpower Grasshopper Points: 17 More actions · Answer 2

Hello,

I do not need any headers so the export with two data flows without overwright should work.

But I think my main problem is to generate the loop from the first data row to the last, and the

special file name which i need and all necessary variables to handle this export.

Maybe someone can make me an example loop, which does this transfer from two tables into one csv? - or even parts of it

I would be thankful

Stefan

philip.coe SSC Journeyman Points: 79 More actions · Answer 3

After re reading your post, what i stated wont work,

you will need to use a for each loop

first do a data flow (using a select distinct ) to a record set desination(object) so as in your example it gets the number 1 & 2

map this to a variable, and work out the file name based on this variable, the connection string then is evaluated to this file path

then in the data flow/s use paramaters and map the variable into the query.

easiest would be to do two sql server sources both with the paramater mapped and a union task before it hits the flat file destination

Austrianpower Grasshopper Points: 17 More actions · Answer 4

Hello Philip,

as a result of your task, I will get one file with the content of both tables.

But what I need is one file per ID from the first table with different file names, which I have explained in my first post.

TABLE A:

ID DOCUMENTNUMBER VALUE

1 4711 50

2 4712 60

TABLE B:

ID ARTICLE AMOUNT

1 15689 10.000

1 15690 20.000

2 16895 13.000

2 16896 12.500

2 85263 11.250

Content of first file "4711.csv" should be: