Solved

SSIS Import data problem

Posted on 2009-07-05
11
2,458 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
Free Webinar: AWS Backup & DR

Join our upcoming webinar with experts from AWS, CloudBerry Lab, and the Town of Edgartown IT to discuss best practices for simplifying online backup management and cutting costs.

 
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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

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.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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.

756 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