Solved

SSIS Import data problem

Posted on 2009-07-05
11
2,476 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

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?
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

622 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