Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Converting a File with Parent-Child Records in SSIS


Converting a File with Parent-Child Records in SSIS

Author
Message
ava1over
ava1over
SSC Rookie
SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)

Group: General Forum Members
Points: 29 Visits: 222
Comments posted to this topic are about the item Converting a File with Parent-Child Records in SSIS
Misha_SQL
Misha_SQL
SSChasing Mays
SSChasing Mays (607 reputation)SSChasing Mays (607 reputation)SSChasing Mays (607 reputation)SSChasing Mays (607 reputation)SSChasing Mays (607 reputation)SSChasing Mays (607 reputation)SSChasing Mays (607 reputation)SSChasing Mays (607 reputation)

Group: General Forum Members
Points: 607 Visits: 985
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
ava1over
SSC Rookie
SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)

Group: General Forum Members
Points: 29 Visits: 222
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
zhuce-haoma
SSC Rookie
SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)

Group: General Forum Members
Points: 49 Visits: 191
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
Divya Agrawal
SSC-Enthusiastic
SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)

Group: General Forum Members
Points: 158 Visits: 604
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
kjiang22206
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 115
This is a very good article. Thanks for sharing!

BTW, could you please attach the zipped SSIS project and sample files?
thauptmann
thauptmann
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 63
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
ava1over
SSC Rookie
SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)

Group: General Forum Members
Points: 29 Visits: 222
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
ava1over
SSC Rookie
SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)

Group: General Forum Members
Points: 29 Visits: 222
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search