使用SSIS导入固定长度的文件 [英] Fixed Length File Import using SSIS

问题描述

嗨
我正在尝试以编程方式使用SSIS导入固定长度的文件.

我的connectionManager代码是

Hi
I am Trying to import a Fixed Length File Using SSIS programatically.

My connectionManager code is

connectionManager = package.InnerObject.Connections.Add("FLATFILE");
 connectionManager.ConnectionString = "C:\FIXED.TXT";
 connectionManager.Name = "SSIS Connection Manager for Files";
 connectionManager.Description = string.Concat("SSIS Connection Manager");
connectionManager.Properties["ColumnNamesInFirstDataRow"].SetValue(connectionManager, TRUE);
connectionManager.Properties["RowDelimiter"].SetValue(connectionManager, "\r\n");
connectionManager.Properties["Format"].SetValue(connectionManager, "FixedWidth");
connectionManager.Properties["HeaderRowDelimiter"].SetValue(connectionManager, "\r\n");

我的列映射代码是

and my column mapping code is

RuntimeWrapper.IDTSConnectionManagerFlatFile90 flatFileConnection =
     connectionManager.InnerObject as RuntimeWrapper.IDTSConnectionManagerFlatFile90;

    RuntimeWrapper.IDTSConnectionManagerFlatFileColumn90 column;
    RuntimeWrapper.IDTSConnectionManagerFlatFileColumns90 columns;
    RuntimeWrapper.IDTSName90 name;


    DataTable schemaTable = DataSource.GetSchemaTable(); // get the schema table


    foreach (DataRow row in schemaTable.Rows)
    {   // iterate
        string colName = row["ColumnName"] as string;    // get the col name
        // now create a new column for the connection manager
        column = flatFileConnection.Columns.Add();       // if this is the last row
       // column.ColumnDelimiter = delimitedDataSource.HeaderRowDelimiter;
        if (schemaTable.Rows.IndexOf(row) == (schemaTable.Rows.Count - 1))
        {
            column.ColumnDelimiter =delimitedDataSource.HeaderRowDelimiter;
            //column.ColumnDelimiter = @delimitedDataSource.HeaderRowDelimiter;
        }

        column.ColumnType = "FixedWidth";
        column.TextQualified = delimitedDataSource.TextQualifier != null;
        flatFileConnection.ColumnNamesInFirstDataRow = true;
        column.ColumnWidth = 5;
        column.MaximumWidth = 5;
        column.DataType = RuntimeWrapper.DataType.DT_WSTR;
        column.DataPrecision = 0;
        column.DataScale = 0;

        name = (RuntimeWrapper.IDTSName90)column;
        name.Name = colName;
    }

该代码错误地导入了数据.同时导入标题行

我的文件内容是

This code is importing datas wrongly. Also importing the Header row

My file content is

data1,data2,data3
111112222233333
444445555566666

但它是这样导入的，
data1 data2 data3
a3 1 11112 22223
3333 4444 45555

任何人都可以帮助我解决此问题并以编程方式使用SSIS导入固定长度的文件吗?

问候
Hari

but it is importing like this ,
data1 data2 data3
a3 1 11112 22223
3333 4444 45555

Can any one help me to solve this and import a fixed length file using SSIS programatically

Regards
Hari

推荐答案

您想做什么?
看看您的文件内容是否像这样.

data1，data2，data3
111112222233333
444445555566666


那么您是否想像这样查看数据库表中的数据

data1 data2 data3
11111 22222 33333
44444 55555 66666

其中data1，data2和data3是您的列名和
剩下的两行是您的值.

What do you want to do ?
Look if your file content is like this .

data1,data2,data3
111112222233333
444445555566666


then are your want to see the data in your db table like this

data1 data2 data3
11111 22222 33333
44444 55555 66666

where data1,data2 and data3 are your column name and
remaining two rows are your values .

是的！这就是我所需要的.您能帮我吗?

哈里

Yes! This is what I need. Can you help me with this.

Hari

嗨
得到了解决方案.最后一列的列类型应定界

哈里

Hi
Got the solution. Column type of the last column should be delimited

Hari

这篇关于使用SSIS导入固定长度的文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！