October 22, 2015 at 7:38 pm
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 ?
October 22, 2015 at 7:49 pm
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
October 23, 2015 at 1:58 am
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 ?
October 23, 2015 at 7:45 am
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
October 23, 2015 at 8:28 am
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
October 23, 2015 at 9:39 pm
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply