SSIS setting variables in Script Component of Data Flow

Question

I have an input flat file that has 2 types of input records for each output record. The first record (identified by C in first column) has an ID and Demographic information. The second record (identified by L in first column) has some financial information. They are pipe delimited and of different lengths.

There isn't any way to write all the C records to one stream and the L records to another stream and then bring them back together. So my solution is to put in a conditional split. When I hit a C record store all the info I need in SSIS variables. When I hit an L record make derived columns out of the variables and use the derived columns and the columns from the L record to make my output record (also flat file).

I've looked all over the Internet and can't find C# code to set my variables within the Script Component of the path of the C records. What I want the code to look like is something like

Variable.User::Firstname = Column 2 (from input file) Variable.User::Lastname = Column 3 (from input file)

etc.

Can somebody help me out?

Thanks,

Dick

Answer 1

This idea won't work. What do you think you will be able to do with the variables as each row gets processed? Anything you do with the value of the variables would have to be done IN the script that populates them, because by the time you leave the script, the variable is being populated by the value of the next row.

However, treating your question as academic, the way to access variables in a script component has already been asked and answered here: How to access ssis package variables inside script component

Here is how I would approach this:

1. Configure your source component so that each row is a single column
2. Next do a conditional split that sends the C-rows down one path, and the L-rows down another
3. In each path use either a Derived Column transformation or a Script transformation that splits the string by the actual delimiter and creates the actual columns for the type of record in that path.
4. Continue on with the rest of your processing until they reach their separate destinations.

Answer 2

I like using script components:

First Step is to add a data flow.

In data flow add script component and chose source

In inputs/Outputs: Add your column info that you want as output. (Note you will have 2 separate outputs with many columns) and chose your data types.

Now enter the script editor.

Here is the code to use 2 separate If statements

string strPath = "";
    var lines = System.IO.File.ReadAllLines(strPath);

foreach (string line in lines)
{
    if (line.Substring(1, 1) == "C")
    {
        char[] delim =  "|".ToCharArray() ;
        var C_cols = line.Split(delim);
        Output0Buffer.AddRow();
        Output0Buffer.FirstName = C_cols[0];
        Output0Buffer.LastName = int.Parse(C_cols[1]); //Note that everything is a string until cast to correct data type
        // Keep on going
    }

    if (line.Substring(1, 1) == "L")
    {
        char[] delim = "|".ToCharArray();
        var L_cols = line.Split(delim);
        Output2Buffer.AddRow();
        Output2Buffer.FirstName = L_cols[0];
        Output2Buffer.LastName = int.Parse(L_cols[1]);
        // Keep on going
    }
}

At this point the script component will have two outputs that can lead down different paths.

Answer 3

Thanks for your responses.

Since I need both a C record and the L record after it, I decided to load the entire input file into a SQL Server table. Once it was in SQL I wrote a fairly straightforward stored procedure to cursor through the records in the table, put the needed columns into SQL variables when it was a C record and insert the data from the SQL variables and the input data into the output record when it was an L record (the same thing I was trying to do entirely within SSIS but was unable to). After my table of output records was populated it was a simple matter to write a Data Flow to SELECT all the records from the table output records as a Data Flow Source and put them into the desired flat file as the Data Source Destination.

Dick Rosenberg