Importing blank Excel columns using SSIS

Question

I'm creating an SSIS package to import Excel files (which have different layouts) into a SQL Server table with 150 columns.

The SSIS package has a Foreach Loop Container within which is a Data Flow Task which has an Excel Source component and an OLE DB Destination component.

The Data Flow Task will be ran for each Excel file so the Excel Source component needs to work for every file.

I've set up the Excel Source component with 150 Unicode string columns of length 255, and I've set ValidateExternalMetadata to False, however I'm getting the error: 'Column "F143" cannot be found at the datasource' when testing the package on an Excel file with 142 columns of data.

My question is: How do I get the package to ignore this error and import the columns that exist, or is there another way of dealing with the above situation?

SSIS doesn't handle (well) documents that have different properties, and ACE is not a helpful part in that. If you have differing files, I'd suggest either creating dataflows for the different types of files, or using a Script Component and building your own Source (thus not using the built in Excel Data Source Component). — Thom A, Dec 08 '17 at 17:06
@jhnpckr is your problem solved? I think that importing excel files using SSIS has many issues and this is one of them so i am interested to see the right answer. Why not replying or accepting one of the answers provided. This helps the others seeking for a similar question — Yahfoufi, Dec 12 '17 at 13:54
@Yahfoufi none of the three solutions below work for me so I think the only option is using the Script Component, however it's going to take some time to get this working and ensure that it is a viable solution. — jhnpckr, Dec 12 '17 at 14:37
@jhnpckr have you tried `Select * from [Sheet1$A1:ET]`? what is the error? — Yahfoufi, Dec 12 '17 at 14:38
@Yahfoufi Even when using the SQL command the Excel Source only has External and Output columns corresponding to the columns in the Excel files that are populated. So I still got the same error: 'Column "F143" cannot be found at the datasource' — jhnpckr, Dec 12 '17 at 14:54

score 0 · Answer 1 · edited Dec 12 '17 at 14:47

0

Try using the following sql command in Excel source

SELECT * FROM [Sheet1$A1:B]

Substitute B for whatever the 150th column is.

edited Dec 12 '17 at 14:47

Yahfoufi

2,220
1
22
41

answered Dec 08 '17 at 18:49

KeithL

5,348
3
19
25

score 0 · Answer 2 · answered Dec 08 '17 at 19:11

Open up the editor for your Excel Source task. In the left pane, select "Error Output". below the columns pane is a line that says "Set this value to selected cells:" with a dropdown next to it. Choose "Ignore failure" from the drop down. Now go to the columns pane and select all the 150 rows under the "Error" column, and then hit the "Apply" but next to the dropdown box. This should set all the Excel columns to "Ignore Error". Hit "Ok" and then you should be good to go.

score 0 · Answer 3 · answered Dec 08 '17 at 20:15

In the Excel Source, select the source type as SQL command and use the following command (Assuming that the worksheet name is Sheet1)

Select * from [Sheet1$A1:ET]

ET is the column with index = 150, so with this query you are forcing the Excel Source to read the 150 column, even if they contains some empty columns.

For additional info on importing excel with dynamic headers, just follow my answer below:

Importing excel files having variable headers

score 0 · Accepted Answer · answered Mar 23 '18 at 14:55

Rather than using a SQL Server table with arbitrarily many columns in the hope that this will be enough for all possible Excel file layouts, I redesigned the table to have xlRow, xlColumn, and xlValue columns.

I then used a Script Component as the Source of my Data Flow with the following C# code:

        Excel.Application xlApp = new Excel.Application();
        xlApp.Visible = false;
        xlApp.DisplayAlerts = false;
        Excel.Workbook xlWorkBook = xlApp.Workbooks.Open(filename, Password: "'");
        Excel.Worksheet xlWorkSheet = xlWorkBook.Worksheets[1] as Microsoft.Office.Interop.Excel.Worksheet;

        Excel.Range UsedRange = xlWorkSheet.UsedRange;


        foreach (Excel.Range c in UsedRange)
        {
            string val = Convert.ToString(c.Value2);
            if (val != "" && val != null)
            {
                Output0Buffer.AddRow();
                Output0Buffer.Row = c.Row;
                Output0Buffer.Column = c.Column;
                Output0Buffer.Value = val;
            }
        }

This loops through every non-empty cell in the first worksheet and outputs the row, column, and value of the cell to Output0 which is then inserted into the SQL table.

Importing blank Excel columns using SSIS

4 Answers4