SSIS Package Data Load Issue

Posted on 2011-04-25
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
    LVL 9

    Expert Comment

    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

    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


    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
    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    The steps for moving the system databases to a new location are documented in the following technical article: However sometimes after the moving process is finished, though SQL i…
    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    Via a live example, show how to setup several different housekeeping processes for a SQL Server.
    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.

    779 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

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now