Using the Script Component With Multiple Outputs

  • Tim Mitchell

    SSCoach

    Points: 15654

    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

  • Girish Bhat

    Ten Centuries

    Points: 1323

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

    Girish

  • Jack Corbett

    SSC Guru

    Points: 184380

    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

    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: 15654

    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 3188

    SSC Guru

    Points: 88266

    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.

    [font="Verdana"]Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster[/url]
    Managing Transaction Logs[/url]
    [/font]

  • Misha_SQL

    SSCertifiable

    Points: 5388

    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

    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

    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

    Can I have the same example shown in VB please

    Thank you

  • parthi-1705

    SSCrazy Eights

    Points: 9277

    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: 15654

    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: 15654

    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: 15654

    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

    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

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

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