CODEWITHSUNDEEP
sql-serverssistext-filesmetadatassis-2014
Cobus Ellis
Cobus Ellis

Reputation: 51

How to import a text file with multiple layouts into SQL with SSIS

I'm fairly new to SSIS, any help would be much appreciated!

I need to import a comma delimited text file. The rows in the file have different layouts. The value of the first column specifies the layout.

For example:

Layout 1: Name, Surname, Age, ID

Layout 2: ID, Salary

So column names and data types differ completely.

Is there a way to import such a file without using a script task in SSIS?

Upvotes: 1

Views: 873

Answers (1)

Proffesore
Proffesore

Reputation: 412

You can use flat file source from the SSIS toolbox/Other sources. Check https://learn.microsoft.com/en-us/sql/integration-services/connection-manager/flat-file-connection-manager for more information Edited: After you changed your question, I undrestood better. Script task is the only solution, as you have to build a logic.

public override void CreateNewOutputRows()
{
    // Create the StreamReader object to read the input file
    System.IO.StreamReader reader = new System.IO.StreamReader(this.Variables.vInputFilename);

    // 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('|');

        /*
            Each line should match one of three record types. When matched to
            the correct type, a new row in that output will be created and the
            columns from the file will be written to the appropriate output cols
        */

        // Record type 1 is Manager
        if (items[0] == "Layout 1")
        {
            OutputBuffer0.AddRow();

        }

        // Layout 2
        else if (items[0] == "Layout 2")
        {
            OutputBuffer1.AddRow();

        }
    }
}

Then based on the output you connect the relevant tables.Let me know if it works :)

Upvotes: 1

Related Questions