Converting a File with Parent-Child Records in SSIS

Question

Converting a File with Parent-Child Records in SSIS

ava1over

Old Hand

Points: 383
More actions
November 26, 2009 at 8:11 pm

#217281

Comments posted to this topic are about the item Converting a File with Parent-Child Records in SSIS

Viewing 9 posts - 1 through 8 (of 8 total)

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

Misha_SQL SSCertifiable Points: 5407 More actions · Answer 1

Thank you for the article! I have been using SSIS for a while, but script component has always been a mystery to me. Could you please clarify what is the purpose of "ExclusionGroup" property in the script editor?

ava1over Old Hand Points: 383 More actions · Answer 2

Great question. Below is a link to a post that sheds a little more light on exclusion groups:

http://consultingblogs.emc.com/jamiethomson/archive/2008/01/05/SSIS_3A00_-ExclusionGroup.aspx

zhuce-haoma Valued Member Points: 69 More actions · Answer 3

Thanks for this article. Hope you can supply a link to download the csv file and ssis package.Thank you in advance again.

Divya Agrawal Hall of Fame Points: 3144 More actions · Answer 4

I used the same logic of Conditional Split in my package to split the records based on conditions having data and nodata. I have a stored procedure which will either output data or no records. If it returns records then a csv file has to be created otherwise no file has to be created.

I tried using ([Column0])!="" to check whethere records are there are not. Although the sp is not returning any row it is still creating the file. Can you help me with this? How to create a dynamic csv file from Data Flow Transformations..

However i have achieved the same using Execute sql task in Control flow..but i wanted to get it though Data flow transformation.

Is it possible?

--Divya

kjiang22206 Grasshopper Points: 11 More actions · Answer 5

This is a very good article. Thanks for sharing!

BTW, could you please attach the zipped SSIS project and sample files?

thauptmann Grasshopper Points: 13 More actions · Answer 6

How were you able to save the Flat File Connection Manager Editor settings without defining columns? I have a file with Headers and Lines. There are 21 columns for headers and 11 columns for lines. If I let the connection manager define the columns of my flat file automatically it will find 21 but the package fails as soon as it gets to a line row (before it can be evaluated by the conditional split task) since it's finding carriage returns after column11.

Not sure how to get around that. Can you explain, or include a seoncd screen shot of the Flat File Connection Manager Editor column definition screen? Or did you data set happen to have the same number of columns for all rows, regardless of the type of data in the row?

ava1over Old Hand Points: 383 More actions · Answer 7

Good Question. For this file there were the same number of columns in all rows [43]. However, you have a few options for your scenario:

1) Have the connection manager ignore the first row.

2) Remove the first row before processing the file. (this could be done by using a script task or a execute process task)

3) Send the Header Row as an error row to a separate destination for discard or later use.

Hope that helps.

ava1over Old Hand Points: 383 More actions · Answer 8

Hmm, that is an interesting question. I will have to do some trials of my own. I have been able to reuse a file just with a separate connection manager for each record type.