SSIS Package - Get one column from multiple source files

Question

I am trying to create an SSIS package that can accept multiple (known) file structures that are dumped into the same processing folder. I am trying to pull the AccountId from each file.

The problem I am running into is that my Data Flow Task only works for the one specific file that I setup for the Flat File Source. In my Flat File Source I only make the AccountId column available, but that doesn't appear to resolve the issue.

Current SSIS Package Structure

Foreach Loop: Loops though all files in the specificed folder

Foreach Loop

Data Flow Task: Processes each file and stores in SQL table

Dataflow Task

Example CSV File Structures

File 1:

Name | AccountId | Address | City | State
John | 154235    | 1234    | LA   | CA

File 2:

Name | Address | City | State | AccountId | Phone
Kyle | 5825    | Test | KY    | 534534    | 555-555-5555

File :

Name | Address | City | State | Email      | AccountId | Phone
Bob  | 52345   | Fake | WY    | [email protected] | 756313    | 444-444-4444

Is the problem that you want to import from multiple sources simultaneously, or do you want to use the same source to handle multiple file formats? — Daniel Gimenez
I want to import from multiple sources into a table just containing the AccountId field. I am getting the sources from different third parties that all dump their files into the same folder. — ferensilver

Daniel Gimenez Daniel Gimenez · Accepted Answer · 2018-07-03T19:34:27

The flat file source is going to use the position of the column defined in the connection manager. It won't try to pull the positions from the headers of each file individually.

If you want to create one source that can handle different locations for a column, then you can either purchase a component that can handle this scenario, or write your own with the script component.

Below is a sample of a Script Component set as an output that should do the job. You'll have to figure out the parsing of the file on your own, but that shouldn't be so hard. Just don't forget to configure a variable for the file path, and add AccountId as an output in the Inputs and Outputs section.

public override void CreateNewOutputRows()
{
    int colIdx = -1;
    var text = File.ReadAllText(Variables.FilePath); // Set in Script/ReadOnlyVariables
    var rows = ParseText(text);
    var firstRow = rows.FirstOrDefault().ToList();
    // find matching row
    for (var i = 0; i < firstRow.Count; i++)
    {
        if (firstRow[i] == "AccountId")
        {
            colIdx = i;
            break;
        }
    }
    foreach (var row in rows.Skip(1))
    {
        Output0Buffer.AddRow();
        Output0Buffer.AccountId = row.Skip(colIdx).First(); // configure columns in output
    }
}

public IEnumerable<IEnumerable<string>> ParseText(string text)
{
    /** implement yourself.  You can find examples online **/
}

SSIS Package - Get one column from multiple source files

1 Answers