Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Converting a File with Parent-Child Records in SSIS Expand / Collapse
Author
Message
Posted Thursday, November 26, 2009 8:11 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, September 12, 2014 8:05 AM
Points: 27, Visits: 137
Comments posted to this topic are about the item Converting a File with Parent-Child Records in SSIS
Post #825410
Posted Saturday, November 28, 2009 8:45 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 3:24 PM
Points: 536, Visits: 762
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?




Post #825832
Posted Saturday, November 28, 2009 3:54 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, September 12, 2014 8:05 AM
Points: 27, Visits: 137
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
Post #825853
Posted Sunday, November 29, 2009 7:32 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, May 6, 2013 11:40 PM
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.
Post #825970
Posted Wednesday, December 2, 2009 6:03 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, September 1, 2014 4:03 AM
Points: 143, Visits: 549
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
Post #827377
Posted Wednesday, December 2, 2009 12:47 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, January 3, 2014 12:03 PM
Points: 1, Visits: 110
This is a very good article. Thanks for sharing!

BTW, could you please attach the zipped SSIS project and sample files?
Post #827737
Posted Tuesday, December 29, 2009 4:09 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, January 13, 2014 4:13 PM
Points: 1, Visits: 19
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?
Post #840118
Posted Wednesday, December 30, 2009 11:08 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, September 12, 2014 8:05 AM
Points: 27, Visits: 137
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.



Post #840483
Posted Wednesday, December 30, 2009 11:10 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, September 12, 2014 8:05 AM
Points: 27, Visits: 137
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.
Post #840485
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse