1

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?

Cobus Ellis
  • 51
  • 4

1 Answers1

1

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

Proffesore
  • 402
  • 2
  • 10
  • I don't think this solves my problem, thank you for trying. I need to know how to change file layout mappings on the fly – Cobus Ellis Nov 13 '17 at 13:11
  • This makes a lot of sense. For someone who is more SQL and BI oriented, would you mind telling me how to proceed from there? Thanks so much for your help! – Cobus Ellis Nov 14 '17 at 05:49
  • An example here http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/66136/. – Proffesore Nov 14 '17 at 08:59
  • Thanks for everyone's help. I got a person with C# skills to help we with a script component. file gets split into multiple files and then imported from there... – Cobus Ellis Nov 15 '17 at 10:47