[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


SSIS Package Data Load Issue

Posted on 2011-04-25
Medium Priority
Last Modified: 2013-11-10
I have a data load from a csv file to a database via an SSIS package. When I run the package it fails. My question is that on the Control Flow panel the data source is green (i.e. success) and the data destination is red (i.e. failure). Then on the data flow page the data souce and data destination boxes are both yellow (i.e. in process). Additionally on the data flow page the next to the detination arrow it reports a number that must be the number of rows that have flowed from the source to destination. So to me it must mean that the next row is where the error occurs? Specifically I am trying to load 1450 records and the data flow page shows the number of 748. Unfortunatalty when I check the data I can not find any issues with row 748 and for that matter any row.
Any ideas?
Question by:SeTech

Expert Comment

ID: 35461378
No It is not. SSIS Package will load your data in batches (For Ex: 600 records per sec) and that too in a transaction. You can't predict the error record in this way.

Enable Error data redirect, so that it will redirect your error records to another destination (Like a flat file) and loaded the good records.

Enable logging in your package and write the info to your log file.

LVL 22

Accepted Solution

8080_Diver earned 2000 total points
ID: 35461988
Since you are working with a CSV file, there are some "quick and Dirty" checks you can make.

Open the data file in Excel and then highlight all of the headers for the columns that should be in the data.  Once you have done that, double click on one of the vertical lines separating 2 column headers.  (That should expand all of the columns to a size that will accomodate the data in them.)  Now, go to the column just to the right of the rightmost column that should contain data.  scroll down through the data and see if there are any rows that appear to have data in that column.  (Note, since it is just to the right of what should be the rightmost column of data, there shouldn't be any data in that column.)  If there is a row that has data displaced into that column, that probably means that there is a spurious comma somewhere in the data.

Now, if the above check doesn't indicate a row that is messed up, the next test would be for invalid or improperly formatted dates.  However, you will know (or need to know) more about the data (and which columns should have dates in them).  One check is to highlight the column header for each date column and format the cells as a date . . . then scroll down and see if any of them seem to have bad data.

Another check is for invalid numerics and is similar to the date check above.

In general, though, I would highly recommend importing the data from the CSV file into a staging table that has all of its columns defined as VarChar(xx) (where "xx" will vary from column to column and is sufficient to handle the data coming in for each column).  That way, you can get the data into SQL Server where, among other things, you can scrub the data for invalid dates and other issues.  Also, if there is a problem, the data is in the staging table where you run queries to determine what other scrubbing you need to do in order to make sure it is going to load into your target table correctly.
LVL 21

Expert Comment

by:Jason Yousef, MS
ID: 35463769

As @radcaesar said it process in batches. Also steps to redirect error rows are mentioned here


Loot at the "Progress" tab and the problem row will be mentioned, just copy the error lines because you can't see all of it on the screen and paste it in a notepad and you'll find the row number.

Hope that helps.
LVL 21

Expert Comment

by:Alpesh Patel
ID: 35464844
If Arrow in between both boxes shows 748 Records Text then there is a problem in 749. Please check the output/error in Preocess Tab besides Package Explorer.

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question