April 5, 2012 at 6:47 am
Good Morning. Needing some assistance in SSIS. Fairly new to this technology. Here's the issue:
I have a data file coming out of 1 system. It's PO information with multiple records per PO. I need to create a .CSV file out of this to go into another system. So, if there's 3 records on the source file for a PO, the .csv file would have 1 header record with the total cost of the PO and other relevant info. The detail would then have 3 records for each line of the PO with the individual cost for that part.
I have my data flow with the data file as the source. My question is how to do I create the header and detail for each PO on the source file??
Thanks in advance for any help.
John
April 5, 2012 at 7:49 am
It would be much easier to provide a solution if you posted examples of your input file and the output file.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 5, 2012 at 7:53 am
Input
PO Number
Company ID
PO Line Num
PO Date
Vendor Num
Vendor Facility
Account Number
Tax Flag
Part Num
Part Desc
Received QTY
Received Price
Invoice Number
Invoice Date
Output (.csv)
Header for each record invoice above
Record Type (H)
Vendor
Invoice No
Site
Business Entity
Invoice Date
Posting Period
Gross Amount
Discount Amount
Due Date
PO Number
Detail record for each record above
Record Type (D)
Vendor
Invoice Number
Site
Business Entity
Amount
Major Acct
Sum Acct
Profit Center
So, basically, if there is 3 records in source for a PO, the the .CSV would have 4 records. 1 header and 3 details
john Thanks a ton for the help
April 5, 2012 at 2:11 pm
SSIS requires that each of the records have the same structure. Since your header records have a different structure than your detail records, you can't do this directly in SSIS. You have to take a roundabout route.
You'll need to load your input file into a table—it can be a staging table—and then force each of your records into a single string so that all of your records will have the same layout.
If you supply sample data, instead of just headers, we might be able to help you with this.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
April 5, 2012 at 2:24 pm
I'd probably use a script to create the csv.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply