CODEWITHSUNDEEP
sql-serverssis
Kay
Kay

Reputation: 195

SSIS: rows failure when loading data from Excel source to OLE DB destination

I am pretty new at this. I am trying to import an Excel file (which is copied and saved it from a SQL Server table) into SQL Server using a SSIS package.

Here is how I created the excel file:

CREATE TABLE #drivers_source 
(
    FirstName NVARCHAR(255) NOT NULL, 
    LastName NVARCHAR(255) NOT NULL, 
    CarMake NVARCHAR(255) NOT NULL, 
    License NVARCHAR(255) NOT NULL, 
    State NVARCHAR(255) NOT NULL
)

INSERT INTO #drivers_source 
VALUES ('Amy', 'Ashley', 'Toyota', '15629686', 'New York'),
       ('Bob', 'Burns', 'Ford', '25448665', 'Washington'),
       ('Cindy', 'Cody', 'Honda','370659CA', 'California'),
       ('Dave', 'Doe', 'Audi','45988718', 'Texas');

SELECT * 
FROM #drivers_source;

I copied the data (with headers) from #drivers_source into the Excel file and saved it.

This is how I created the destination table:

CREATE TABLE dbo.drivers_destination 
(
    FirstName NVARCHAR(255) NOT NULL, 
    LastName NVARCHAR(255) NOT NULL, 
    CarMake NVARCHAR(255) NOT NULL, 
    License NVARCHAR(255) NOT NULL, 
    State NVARCHAR(255) NOT NULL
);

I created a SSIS package with an Excel source and an OLEDB destination, run the package and found that only the first two rows were successfully inserted. I spent hours trying to figure out why, but still had no clues.

Any thoughts on why this error happened, and how this could be solved (without affecting the source data) would be appreciated!

Upvotes: 1

Views: 730

Answers (1)

AlwaysLearning
AlwaysLearning

Reputation: 8809

Check the configuration of the Excel Source component. It sounds like "Type Guessing" has been configured to guess column types based on the first two rows - the default TypeGuessRows setting is normally 8.

In this case it would consider the License column to be of type int based on the first two values 15629686 and 25448665 and then get a conversion error when it tries to convert 370659CA to int.

Confirm in the Excel Source's Output Columns that the License column is configured to use DataType String [DT_STR] or Unicode string [DT_WSTR] instead of something like Four-byte signed integer [DT_I4].

Upvotes: 1

Related Questions