Solved

SSIS Import data problem

Posted on 2009-07-05
11
2,460 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

737 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