In SSIS excel datasource not taking more characters than 255

Question

I have developed SSIS package that import multiple excel files into SQL. Now issue is "Excel data source" check first few rows for determine datatype and it took text data type with length 255 for my remarks column.

But in some files remarks is longer than 255 chars.

I checked some blogs they saying intentional put long text in first row for remarks then SSIS will determine datatype as Unicode text stream. That solved my problem but when other file comes to import datatype again changed to 255 chars and getting truncation error.

Please advise how to fix this issue.

Actually you can set data type and length at File Connection Manager, just manually set it. — BeiBei ZHU, Aug 26 '19 at 02:13
@BeiBeiZHU but its reset to original datatype when i run the package. — Yash, Aug 27 '19 at 14:06

Hadi · Answer 1 · 2022-02-04T17:36:04.123

1

Update - 2022-02-04:

Solutions and workarounds are listed in the following answer:

Cannot import long text from Excel to SQL Server using SSIS

Initial Answer:

Inside the Data Flow Task, right-click on the Excel Source component, got to Advanced Editor >> Input and Output Properties Tab, and change the column length manually in the External Columns and Output Columns as shown in the screenshot below.

edited Feb 04 '22 at 17:36

answered Aug 26 '19 at 03:59

Hadi

36,233
13
65
124

Thanks Hadi for reply, I did same to change datatype manually but it's changing itself when new file comes in to import. – Yash Aug 26 '19 at 06:33
@user2781180 try to change the Excel source `ValidatExternalMetadata` property to false – Hadi Aug 26 '19 at 07:26
i changed ValidatExternalMetadata property to false still external column of Excel datasource changed to length 255 chars. – Yash Aug 26 '19 at 13:40

score 0 · Accepted Answer · answered Sep 09 '19 at 13:25

I have fixed my issue of importing excel having more than 255 characters in particular column using below code in SSIS script task.

        string path = Dts.Variables["User::FileName"].Value.ToString();

        FileStream stream = File.Open(path, FileMode.Open, FileAccess.Read);
        IExcelDataReader excelReader = null;

        excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
        DataSet result = excelReader.AsDataSet();
        excelReader.Close();

        var dt = result.Tables[0];

        string CS = "Data Source=localhost;Initial Catalog=Demo;Persist Security Info=True;User ID=sa;Password=; Connect Timeout=200; pooling='true'; Max Pool Size=200";
        // Bulk Copy to SQL Server   
        SqlBulkCopy bulkInsert = new SqlBulkCopy(CS);
        bulkInsert.DestinationTableName = "test";
        bulkInsert.WriteToServer(dt);

In SSIS excel datasource not taking more characters than 255

2 Answers2

Update - 2022-02-04:

Initial Answer:

Linked