Converting a File with Parent-Child Records in SSIS

  • ava1over

    Old Hand

    Points: 383

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

  • Misha_SQL

    SSCertifiable

    Points: 5397

    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

    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

    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

    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

    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

    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

    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

    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.

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

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