Converting a File with Parent-Child Records in SSIS

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Jeff Singleton » Converting a File with Parent-Child Records...

Converting a File with Parent-Child Records in SSIS

Rate Topic

Display Mode

Topic Options

Author

Message

ava1over

Posted Thursday, November 26, 2009 8:11 PM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, April 18, 2013 11:56 AM
Points: 27, Visits: 113

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

Post #825410

mishaluba

Posted Saturday, November 28, 2009 8:45 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 5:54 PM
Points: 525, Visits: 617

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

ava1over

Posted Saturday, November 28, 2009 3:54 PM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, April 18, 2013 11:56 AM
Points: 27, Visits: 113

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

zhuce-haoma

Posted Sunday, November 29, 2009 7:32 PM

SSC Rookie

Group: General Forum Members
Last Login: Monday, May 06, 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

Divya Agrawal

Posted Wednesday, December 02, 2009 6:03 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 7:23 AM
Points: 140, Visits: 476

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

kjiang22206

Posted Wednesday, December 02, 2009 12:47 PM

Forum Newbie

Group: General Forum Members
Last Login: Sunday, December 16, 2012 7:23 PM
Points: 1, Visits: 107

This is a very good article. Thanks for sharing!

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

Post #827737

thauptmann

Posted Tuesday, December 29, 2009 4:09 PM

Forum Newbie

Group: General Forum Members
Last Login: Monday, July 23, 2012 7:38 PM
Points: 1, Visits: 12

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

ava1over

Posted Wednesday, December 30, 2009 11:08 AM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, April 18, 2013 11:56 AM
Points: 27, Visits: 113

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

ava1over

Posted Wednesday, December 30, 2009 11:10 AM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, April 18, 2013 11:56 AM
Points: 27, Visits: 113

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 »

Permissions