Using the Script Component With Multiple Outputs

Question

Post reply

Using the Script Component With Multiple Outputs

Tim Mitchell

SSCoach

Points: 15681
More actions
April 15, 2009 at 12:06 am

#203587

Comments posted to this topic are about the item Using the Script Component With Multiple Outputs
Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices

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

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

Girish Bhat Ten Centuries Points: 1323 More actions · Answer 1

Thanks for this tip. You have explained it really well in an easy to understand manner.

Girish

Jack Corbett SSC Guru Points: 184393 More actions · Answer 2

Great article Tim. I don't currently have to work with anything like this, but it is a good overview of using the Script Component in addition to the tutorial on managing an indefinite flat file source.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

gau2902 SSC Journeyman Points: 98 More actions · Answer 3

I agree with the solution but i would recommend another approach which is simpler and doesn't require any script component.

You can use conditional split to split the source data based on record type and have multiple destination for each record type.

Tim Mitchell SSCoach Points: 15681 More actions · Answer 4

You're right that the conditional split could be used in some cases, but when you have outputs with differing numbers of columns, the script component would be ideal.

Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices

Jeffrey Williams SSC Guru Points: 90244 More actions · Answer 5

And what about the case when you have information in the header record that needs to be associated with the detail records? For example, if we have an employer group in the header record - and the detail records are the list of employees for the group.

I have worked with files where the group identifier only exists in the header record, but we need that identifier for each detail record.

I don't think that can be done with a conditional split - and using a script component will work handily.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Misha_SQL SSCertifiable Points: 5407 More actions · Answer 6

Thank you for the article! I have been doing a lot of SSIS work over the last 3-4 years including some pretty advanced stuff, but have never touched Script component. Your article made it look much less scary :w00t:. I am sure I will use these techniques sometime soon.

matty_p75 SSCommitted Points: 1708 More actions · Answer 7

This is a good article for how to use a compenent that is very badly documented in Books Online and everywhere else. I used the Script Component transformation to "condense" a table that i could see was possible any other way. It took a lot of searching to find how to overwrite the appropriate subroutines and output to a new buffer, whch could subsequently be put into a table. I think that when there is a lack of documentation in this way people just don't use the component as they don't really know what it is for, which is a shame as it has its uses.

dpatel0501-696442 SSC Enthusiast Points: 191 More actions · Answer 8

So whats the right approach when we have records in the header as opposed to not having them in details?

psripuram Valued Member Points: 63 More actions · Answer 9

Can I have the same example shown in VB please

Thank you

parthi-1705 SSCrazy Eights Points: 9277 More actions · Answer 10

Hi,

I just copy and past but getting error

public override void CreateNewOutputRows()

{

// Create the StreamReader object to read the input file

System.IO.StreamReader reader = new System.IO.StreamReader(this.Variables.Filename);

// Loop through the file to read each line

while(!reader.EndOfStream)

{

// Read one line

string line = reader.ReadLine();

// Break the file apart into atomic elements

string[] items = line.Split('|');

// Record type 1 is Manager

if (items[0] == "1")

{

ManagerOutputBuffer.AddRow();

ManagerOutputBuffer.ManagerID = int.Parse(items[1]);

ManagerOutputBuffer.ManagerName = items[2];

ManagerOutputBuffer.ManagerRole = items[3];

ManagerOutputBuffer.Location = items[4];

}

// Record type 2 is Employee

elseif (items[0] == "2")

{

EmployeeOutputBuffer.AddRow();

EmployeeOutputBuffer.EmployeeID = int.Parse(items[1]);

EmployeeOutputBuffer.ManagerID = int.Parse(items[2]);

EmployeeOutputBuffer.EmployeeName = items[3];

EmployeeOutputBuffer.EmployeeRole = items[4];

}

// Record type 3 is Client

elseif (items[0] == "3")

{

ClientOutputBuffer.AddRow();

ClientOutputBuffer.SalespersonID = int.Parse(items[1]);

ClientOutputBuffer.ClientID = int.Parse(items[2]);

ClientOutputBuffer.ClientName = items[3];

}

I am getting this error what that it means did i miss some thing refer screenshot

Thanks

Parthi

Thanks
Parthi

Tim Mitchell SSCoach Points: 15681 More actions · Answer 11

dpatel0501-696442 (5/21/2009)
So whats the right approach when we have records in the header as opposed to not having them in details?

If I read your question right, you're asking how to address the headers in a file? I haven't run across one of these multi-format files that had a header, because a single header can't describe the various metadata. Perhaps a file could have headers within the data - I haven't seen this approach, but if this exists you'd need to use your script to detect the header records (the method for which would vary depending on the makeup of the header) and either discard the headers or use them to identify the upcoming metadata.

hth,

Tim

Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices

Tim Mitchell SSCoach Points: 15681 More actions · Answer 12

psripuram (8/5/2010)
Can I have the same example shown in VB please

Unfortunately I don't have a VB example for this code - most of my sample scripts are in C#.

Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices

Tim Mitchell SSCoach Points: 15681 More actions · Answer 13

parthi-1705 (11/17/2010)
Hi, I just copy and past but getting error

It looks like there are a couple of errors. First of all, the elseif should be else if. It looks like the browser causes those to run together for some reason.

The highlighted error indicates that you don't have a variable named Filename in this scope. You'll want to create a variable with this name in your package, which should be populated with the name of the file you want to shred.

hth,

Tim

Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices

kingshango10 Newbie Points: 3 More actions · Answer 14

Hi Tim. Thanks a lot for your post. It's very helpful I facing facing an almost similar kind of challenge but I haven't been able to resolve it yet. I am trying to process a Pipped separated flat file using SSIS. The file has two different types of records. each record set has has different it's own header row and a trailer row. They need to go to two different table.The trailer row has the row count for each record set while the header row contains column names for the records. So in essence it's like two types of files but they come in one file. I've tried several solutions including using a conditional split but I haven't been able to achieve this. I know you could do this using a script component and C# as in the example you have given but I haven't been able to achieve this. I've attached an image to show file format.. Kindly help. Thanks