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.

Answer 1

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);