Export 3 different data records to a flat file

Question

Export 3 different data records to a flat file

ErickTreeTops

Ten Centuries

Points: 1253
More actions
October 22, 2015 at 7:38 pm

#149134

I have a package where I created 3 different recordtypes; Headers, sub-Header and Items.
Both the number of columns and data types are differen for all 3 records
The records are related by a transaction number and I want to output them to a flat file In the correct order grouped by the transaction.
eg.
Transaction 1
Header
sub-Header
Item
.
.
Transaction 2
Header
sub-Header
item
.
.
I can't union the data as it's not in the same format and I believe that the SSIS merge option also requires the data to be the same sturcture.
How can I combine these three record types in the correct order an output the transactions to a flat file ?

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

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

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 1

I have no idea how to do this in SSIS because I do these types of things using T-SQL. Hopefully, it might give you an idea.

I would do a join of the 3 tables based on your transaction numbers and simply concatenate the columns together in one monster string. The key would be that your header related columns would come first and you would add a newline character, then your sub-header related columns also followed by newline character and then final your item columns.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

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

merge operator doesn't require the same column structure or number, it can be used to combine results from table that have different number of columns , I am not sure about data types but that can be easily fixed with a data conversions task , Have you considered xml as a possible output format ?

Jayanth Kurup[/url]

michal.lisinski SSCertifiable Points: 6933 More actions · Answer 3

I developed simlar project a few years ago. It exports invoices to the flat file in specified order:

Invoice 1

Invoice header

Item lines

Subtotal netto

Subtotal by VAT

Total

Footer

...

Invoice n

Invoice header

Item lines

Subtotal netto

Subtotal by VAT

Total

Footer

So I created a stored procedure at the first step, which returns a single invoice.

I call SP in Data Flow task to extract every invoice inside Foreach Loop container and that's all folks.

Br.

Mike

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 4

michal.lisinski (10/23/2015)
I developed simlar project a few years ago. It exports invoices to the flat file in specified order:
Invoice 1
Invoice header
Item lines
Subtotal netto
Subtotal by VAT
Total
Footer
...
Invoice n
Invoice header
Item lines
Subtotal netto
Subtotal by VAT
Total
Footer
So I created a stored procedure at the first step, which returns a single invoice.
I call SP in Data Flow task to extract every invoice inside Foreach Loop container and that's all folks.
Br.
Mike

Hi Mike,

Any idea how many rows (calls to the proc) and how long it took?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ErickTreeTops Ten Centuries Points: 1253 More actions · Answer 5

ok so far this is what I have done

I've created a stored proc that will return 3 results sets.

Next I will call it from SSIS using a script control.

As far as I know that's the only way you can return multiple results sets to SSIS.

I'm then going to loop through the header result sets where the transaction ID occurs only once.

I will then use that ID to do a filter on the other two Tables in .net code and read only those sub header and item records associated with that transaction Id.

For each I will create a row and use the IO class to output the rows to a file.

Won't know if the script control will do the job until Monday