Ignoring column from Excel file while importing to

Question

I have multiple Excel files that have the same format. I need to import them into SQL Server.

The issue I currently have is that there are two text columns that I need to ignore completely as they are free text and the character length for some rows exceeds what the server allows me to import which results in a truncation error.

Because I don't need these columns for my analysis, the table I'm importing to doesn't include these columns but for some reason the SSIS packages still picks up those columns and cuts the import job halfway through.

I tried using max character length for those columns which still results in the truncation error.

I need to create an SSIS package that ignores the two columns completely without deleting the columns from Excel.

Answer 1

If you just create the SSIS package in SSDT the Excel file can be queried to return only the required columns. In the package, create an Excel Connection Manager using the Excel file. Then on the Control Flow of the package add a Data Flow Task that has an Excel Source component in it. On this source, change the data access mode to SQL command and the file can then be queried similar to SQL. In the following example TabName is the name of the Excel tab containing the data that will be returned. If either the tab or any column names contain spaces they will need to be enclosed in square brackets, i.e. TabName would be [Tab Name].

Answer 2

The solution is simple. I needed to write a query that will exclude the columns. So instead of selecting "Copy data from one or more tables" you select "write a query" and exclude the columns you don't need. This one worked 100%

Answer 3

You can specify which columns you need to ignore from the Edit Mappings dialog.

I have added the image for your reference:

Answer 4

Import/Export Wizard

Since you mentioned in the comments that you are using SQL Server Import/Export Wizard. You can solve that if you have a fixed columns (range) that you are looking to import (example: first 10 columns).

In Import/Export wizard, after selecting destination options you will be asked if you want to read from tables or query:

Select the query option, then use a simple select query and specify the columns range after the sheet name. As example:

SELECT * FROM [Sheet1$A:C]

The query above will read from the first 3 columns in Sheet1 since A:C represent the range between first column A and third column C.

Now, you can check the columns from the Edit Mappings dialog:

---

SSIS

You can use the same logic within SSIS package, just write the same SQL command in the Excel Source after changing the Access Mode to SQL Command.