SQLServerCentral / Discuss Content Posted by Tim Mitchell / Article Discussions / Article Discussions by Author / Using the Script Component With Multiple Outputs / Latest Posts

RE: Using the Script Component With Multiple Outputs

Tim Mitchell — Thu, 02 Dec 2010 20:31:16 GMT

[quote][b]parthi-1705 (11/17/2010)[/b][hr]Hi, I just copy and past but getting error[/quote]It looks like there are a couple of errors. First of all, the [code]elseif[/code] should be [code]else if[/code]. 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 [code]Filename[/code] 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

RE: Using the Script Component With Multiple Outputs

Tim Mitchell — Thu, 02 Dec 2010 20:24:58 GMT

[quote][b]psripuram (8/5/2010)[/b][hr]Can I have the same example shown in VB please[/quote]Unfortunately I don't have a VB example for this code - most of my sample scripts are in C#.

RE: Using the Script Component With Multiple Outputs

Tim Mitchell — Thu, 02 Dec 2010 20:23:14 GMT

[quote][b]dpatel0501-696442 (5/21/2009)[/b][hr]So whats the right approach when we have records in the header as opposed to not having them in details?[/quote]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 [i]within [/i]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

RE: Using the Script Component With Multiple Outputs

parthi-1705 — Wed, 17 Nov 2010 12:36:48 GMT

Hi,I just copy and past but getting errorpublic override void CreateNewOutputRows(){// Create the StreamReader object to read the input fileSystem.IO.StreamReader reader = new System.IO.StreamReader(this.Variables.Filename);// Loop through the file to read each linewhile(!reader.EndOfStream){// Read one linestring line = reader.ReadLine();// Break the file apart into atomic elementsstring[] items = line.Split('|'); // Record type 1 is Managerif (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 Employeeelseif (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 Clientelseif (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 screenshotThanksParthi

RE: Using the Script Component With Multiple Outputs

psripuram — Thu, 05 Aug 2010 13:37:56 GMT

Can I have the same example shown in VB pleaseThank you

RE: Using the Script Component With Multiple Outputs

dpatel0501-696442 — Thu, 21 May 2009 09:58:38 GMT

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

RE: Using the Script Component With Multiple Outputs

matty_p75 — Thu, 23 Apr 2009 09:29:28 GMT

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.

RE: Using the Script Component With Multiple Outputs

mishaluba — Sun, 19 Apr 2009 16:47:06 GMT

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.

RE: Using the Script Component With Multiple Outputs

Jeffrey Williams 3188 — Wed, 15 Apr 2009 12:50:25 GMT

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.

RE: Using the Script Component With Multiple Outputs

Tim Mitchell — Wed, 15 Apr 2009 10:37:04 GMT

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.

RE: Using the Script Component With Multiple Outputs

gau2902 — Wed, 15 Apr 2009 09:40:44 GMT

I agree with the solution but i would recommend another approach which is simpler and doesn't require any script component. You can use [b]conditional split[/b] to split the source data based on record type and have multiple destination for each record type.

RE: Using the Script Component With Multiple Outputs

Jack Corbett — Wed, 15 Apr 2009 06:19:27 GMT

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.

RE: Using the Script Component With Multiple Outputs

Girish Bhat — Wed, 15 Apr 2009 01:56:33 GMT

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

Using the Script Component With Multiple Outputs

Tim Mitchell — Wed, 15 Apr 2009 00:06:57 GMT

Comments posted to this topic are about the item [B]Using the Script Component With Multiple Outputs[/B]