Solved

SSIS Import data problem

Posted on 2009-07-05
11
2,433 Views
Last Modified: 2013-11-10
Hello
I have a flat file and sql database
when i try to run the project it's always import the half of rows for example if there is sex rows in the flat file its import three rows only so what' the problem ?
Thanks.
0
Comment
Question by:Rawasi
11 Comments
 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 428 total points
ID: 24779949
Send the error records to an error file and Kindly check for the following:

1. Check whether it violates Primary Key constraint by having duplicate records.
2. Check whether it violates any Foreign Key constraint.
3. Check whether it violates any Unique Key constraint.
4. Check whether it holds Null in any Not Null columns.
5. Check whether inserts are reverted back by some triggers in the existing table.
0
 

Assisted Solution

by:adeel289
adeel289 earned 72 total points
ID: 24784663
I think there is the problem in Flat file's format as mentioned by rrjegan17.
Thak you
Adeel Shafqat
0
 
LVL 57

Assisted Solution

by:Raja Jegan R
Raja Jegan R earned 428 total points
ID: 24785075
>> I think there is the problem in Flat file's format

To make it clear, its not with Format as three records were inserted successfully.
Its the problem with the data.
0
 
LVL 57

Assisted Solution

by:Raja Jegan R
Raja Jegan R earned 428 total points
ID: 24785147
I meant "Its the problem with the incoming data or the destination table structure"
0
 
LVL 1

Author Comment

by:Rawasi
ID: 24791982
i save the flat file as csv and i open it  by msexcel, i didn't see any deffernet between the records
anyway this the debug information:
SSIS package "Package.dtsx" starting.
Information: 0x4004300A at Data Flow Task, SSIS.Pipeline: Validation phase is beginning.
Information: 0x4004300A at Data Flow Task, SSIS.Pipeline: Validation phase is beginning.
Warning: 0x80049304 at Data Flow Task, SSIS.Pipeline: Warning: Could not open global shared memory to communicate with performance DLL; data flow performance counters are not available.  To resolve, run this package as an administrator, or on the system's console.
Information: 0x40043006 at Data Flow Task, SSIS.Pipeline: Prepare for Execute phase is beginning.
Information: 0x40043007 at Data Flow Task, SSIS.Pipeline: Pre-Execute phase is beginning.
Information: 0x402090DC at Data Flow Task, Flat File Source [1]: The processing of file "\\mssql-srv\d$\SMDR.dat" has started.
Information: 0x4004300C at Data Flow Task, SSIS.Pipeline: Execute phase is beginning.
Warning: 0x8020200F at Data Flow Task, Flat File Source [1]: There is a partial row at the end of the file.
Information: 0x402090DE at Data Flow Task, Flat File Source [1]: The total number of data rows processed for file "\\mssql-srv\d$\SMDR.dat" is 893.
Information: 0x402090DF at Data Flow Task, OLE DB Destination [346]: The final commit for the data insertion in "component "OLE DB Destination" (346)" has started.
Information: 0x402090E0 at Data Flow Task, OLE DB Destination [346]: The final commit for the data insertion  in "component "OLE DB Destination" (346)" has ended.
Information: 0x40043008 at Data Flow Task, SSIS.Pipeline: Post Execute phase is beginning.
Information: 0x402090DD at Data Flow Task, Flat File Source [1]: The processing of file "\\mssql-srv\d$\SMDR.dat" has ended.
Information: 0x4004300B at Data Flow Task, SSIS.Pipeline: "component "OLE DB Destination" (346)" wrote 892 rows.
Information: 0x40043009 at Data Flow Task, SSIS.Pipeline: Cleanup phase is beginning.
SSIS package "Package.dtsx" finished: Success.
 
 it's always insert the half of records.
notes: i use sqlserver2008
thanks.
 
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 57

Assisted Solution

by:Raja Jegan R
Raja Jegan R earned 428 total points
ID: 24792279
Ok.. Have you captured the error records by targeting into a error file.
And kindly check why those records failed as mentioned in my earlier comment 24779949
Some constraint / trigger is causing this insert failures.
0
 
LVL 1

Author Comment

by:Rawasi
ID: 24792651
sorry i don't understand....
0
 
LVL 57

Assisted Solution

by:Raja Jegan R
Raja Jegan R earned 428 total points
ID: 24793165
Route the error records into a log file.
Kindly analysis why those records failed.

It would be any of the reasons mentioned in my earlier comment 24779949
Hope this clarifies.
0
 
LVL 1

Author Comment

by:Rawasi
ID: 24793245
how i can do the Route the error records into a log file. ?
0
 
LVL 57

Assisted Solution

by:Raja Jegan R
Raja Jegan R earned 428 total points
ID: 24793475
In Figure 7 of the below link:

http://aspalliance.com/889_Extracting_Data_from_a_Flat_File_with_SQL_Server_2005_Integration_Services.2

You have error output tab in the left. Use this and route the error records to a flat file and just analyze the records failed using the above comment.
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 26061145
Open your CSV file in NOTEPAD
This line in your log:
     Warning: 0x8020200F at Data Flow Task, Flat File Source [1]: There is a partial row at the end of the file.
Means that your data is not formed properly. This will probably be hidden in Excel.
For example if your file has 6 columns, and its a comma delimited file, then every line needs to have six commas in it.

 
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

863 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

28 Experts available now in Live!

Get 1:1 Help Now