Click here to Skip to main content
15,891,923 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I am running an SSIS package from visual studio 2010(business intelligence); I am loading a file to a database. But the table I load the data to, is dependent on the header row of the file. I want to be able to grab the header record of the file and do a conditional load using the script component in the SSIS. Can someone give me ideas on how to accomplish this?

Thanks in advance!
Posted
Comments
Kuthuparakkal 12-Dec-12 22:39pm    
Excel, text, what's the file type ?
Bassofa 13-Dec-12 1:16am    
text file
Kuthuparakkal 13-Dec-12 1:59am    
see my solution posted... thanx

1 solution

Assuming you have TextFile connection on your Package.
Add a script component - Source.
Inside Script Compoenent Editor: In the connection manager pane, select your text file connection, and name it as "SourceFeedTextConn"

Create Required Outputs in Input and Output pane. say MyType1Output, MyType2Output , add required columns to each output

C#
/* Microsoft SQL Server Integration Services Script Component
 * ScriptMain is the entry point class of the script.*/

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using System.IO;

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
    private StreamReader textReader;
    private string SourceFeed;

    public override void AcquireConnections(object Transaction)
    {

        IDTSConnectionManager100 connMgr = this.Connections.SourceFeedTextConn;
        SourceFeed = (string)connMgr.AcquireConnection(null);

    }

    public override void PreExecute()
    {
        base.PreExecute();
        textReader = new StreamReader(SourceFeed);
    }

    public override void CreateNewOutputRows()
    {

        string nextLine;
        string[] columns;
        string type="";
        char[] delimiters;
        delimiters = ";".ToCharArray();

        nextLine = textReader.ReadLine();
        columns = nextLine.Split(delimiters); //all header
        string header = string.Join(";;", columns);
          // Analyze the header here
          if (header.IndexOf("MyType1Col1", StringComparison.InvariantCultureIgnoreCase) > -1
                    && header.IndexOf("MyType1Col2", StringComparison.InvariantCultureIgnoreCase)> -1
                    && header.IndexOf("MyType1Col3", StringComparison.InvariantCultureIgnoreCase) > -1)//and so on)
               
             type="MyType1";
           if (header.IndexOf("MyType2Col1", StringComparison.InvariantCultureIgnoreCase) > -1
                    && header.IndexOf("MyType2Col2", StringComparison.InvariantCultureIgnoreCase) > -1
                    && header.IndexOf("MyType2Col3", StringComparison.InvariantCultureIgnoreCase) > -1)// and so on)
        
             type="MyType2";
          //and so on
       nextLine = textReader.ReadLine();
       while (nextLine != null)
        {
            columns = nextLine.Split(delimiters);
            if(type=="MyType1")
            {
                 MyType1Output.AddRow();
                 MyType1Output.MyType1Col1 = columns[0].Replace("\"", "").Replace(";", "").Trim();
                 MyType1Output.MyType1Col2 = columns[1].Replace("\"", "").Replace(";", "").Trim();
                 //and so on
            }
            if(type=="MyType2")
            {
                 MyType2Output.AddRow();
                 MyType2Output.MyType2Col1 = columns[0].Replace("\"", "").Replace(";", "").Trim();
                 MyType2Output.MyType2Col2 = columns[1].Replace("\"", "").Replace(";", "").Trim();
                 //and so on
            }
            //and so on
         nextLine = textReader.ReadLine();
        }
    
    }

    public override void PostExecute()
    {

        base.PostExecute();
        textReader.Close();

    }
}


Now Add add destination components and connect each output of Script component to each destination.

Let me know if you need any help.

Thanks,

Kuthuparakkal
 
Share this answer
 
v3
Comments
Bassofa 13-Dec-12 11:29am    
Ok Man; I will try that and let you know.

Thank alot.
Kuthuparakkal 14-Dec-12 22:06pm    
did it work for you ?

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900