如何将SSIS加载的文件的第一行检索到SSIS中的变量？ [英] How can I retrieve the first row of a file I am loading with SSIS to a variable in the SSIS?

问题描述

我正在运行Visual Studio 2010（商业智能）的SSIS包;我正在将文件加载到数据库。但是我加载数据的表依赖于文件的标题行。我希望能够获取文件的头记录，并使用SSIS中的脚本组件进行条件加载。有人可以给我一些关于如何做到这一点的想法吗？



提前谢谢！

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!

推荐答案

假设你你的包上有TextFile连接。

添加一个脚本组件 - 源。

内部脚本Compoenent编辑器：在连接管理器窗格中，选择你的文本文件连接，并命名它asSourceFeedTextConn



在输入和输出窗格中创建所需的输出。说MyType1Output，MyType2Output，为每个输出添加必需的列

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

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

    }
}

现在添加目标组件并将Script组件的每个输出连接到每个目的地。



如果您需要任何帮助，请告诉我。



谢谢，



Kuthuparakkal

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

这篇关于如何将SSIS加载的文件的第一行检索到SSIS中的变量？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！