SSIS，导入具有不同字段计数和不同列宽的平面文件 [英] SSIS, Import flat file with different fields count and different column width

问题描述

大家好，
请提出建议，我想从平面文件导入数据，该文件提供不同的列宽和同一文件中的不同字段计数.

基于第一列.
例如.如果第一列为``N''，则第二列宽度为(8)，第三列宽度为(24)，第四列宽度(9)...等...
如果第一列为``P''，则第二列宽度为(36)，第三列宽度为(9)，第四列宽度(9)...等等...
如果第一列为``D''，则第二列宽度为(14)，第三列宽度为(18)，第四列宽度为(7)...等等...

示例数据
*************

Hi all,
Please kindly suggest, I want to import data from flat file, which provide different column width & different field count in the same file.

Base on the first column .
eg. If first column is ''N'', then 2nd column width is (8), 3rd column width is (24), fourth column width (9) ... ect...
If first column is ''P'', then 2nd column width is (36), 3rd column width is (9), fourth column width (9) ... etc...
If first column is ''D'', then 2nd column width is (14), 3rd column width is (18), fourth column width (7) ... etc...

Example data
*************

N 001     John                    19750805 M
P Engineer                            20000904 20050905
P Manager                             20060505 20100506
N 002     Susan                   19800607 F
P Programmer                          20020607 20050604
D Ayiuapid      sdflkle           sfsdfsd

使用SSIS对此有任何想法吗?

谢谢，最好的问候...

Any idea for this with using SSIS?

Thanks and best regards...

推荐答案

用脚本组件编写自定义源代码:我可以给你一个示例.
将连接添加到您的text/csv文件，并将其命名为MyFlatFile
因此，在脚本组件中，添加以下内容:
1.将脚本放在数据流"选项卡上时，使脚本成为源文件
2.在连接管理器(脚本组件内部)中单击添加"，然后从下拉列表中选择MyFlatFile conn. 4.转到输入和输出选项卡.
5.将Output0重命名为My
6.通过单击添加列"定义所需的所有列，您可以为字符串列设置Unicode字符串[DT_WSTR]并将最大长度指定为100，只是常见数字100
7.编辑脚本并将所有代码替换为以下内容(请记住对您在第6步中定义的列进行适当的更改):

Write custom source with Script Component: I can give you a sample.
Add connection to your text/csv file and name it as MyFlatFile
So in your script component, Add the following things:
1. Make the script compenent as source when drop it on data flow tab
2. Click "Add" in connection managers(inside script component) and select MyFlatFile conn from drop down
4. Go to Input and Output tab.
5. Rename Output0 to My
6. Define all the columns you need by clicking Add Column, you may set Unicode string [DT_WSTR] for string columns and specify max length as 100, just common figure 100
7. Edit script and replace all code with the following(do remember to make appropriate changes on the columns you defined on step 6):

/* Microsoft SQL Server Integration Services Script Component
*  Write scripts using Microsoft Visual C# 2008.
*  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.MyFlatFile;
        SourceFeed = (string)connMgr.AcquireConnection(null);

    }

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

    public override void CreateNewOutputRows()
    {

        string nextLine;
        string[] columns;

        char[] delimiters;
        delimiters = "|".ToCharArray();

        nextLine = textReader.ReadLine();
        while (nextLine != null)
        {
          columns = nextLine.Split(delimiters);
          if(MyBuffer.Length > 3)
          {  
           MyBuffer.AddRow();  
           MyBuffer.Type = columns[0].Trim();
                if (columns[0]=="N")
                {
                    MyBuffer.Designation = columns[1].Trim();
                    MyBuffer.Date = columns[2].Trim();
                    MyBuffer.Gender = columns[3].Trim();
                }
                if (columns[0]=="P")
                {
                    MyBuffer.Name = columns[1].Trim();
                    MyBuffer.CreateDate = columns[2].Trim();
                    MyBuffer.ModifiedDate = columns[3].Trim();
                }
                if (columns[0]=="D")
                {
                    MyBuffer.Name = columns[1].Trim();
                    MyBuffer.SecondField = columns[2].Trim();
                    MyBuffer.ThirdField = columns[3].Trim();
                }
            nextLine = textReader.ReadLine();
        }

    }

    public override void PostExecute()
    {

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

    }


}

这篇关于SSIS，导入具有不同字段计数和不同列宽的平面文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！