Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2511
  • Last Modified:

SSIS Import data problem

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
Rawasi
Asked:
Rawasi
7 Solutions
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
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
 
adeel289Commented:
I think there is the problem in Flat file's format as mentioned by rrjegan17.
Thak you
Adeel Shafqat
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
>> 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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
Raja Jegan RSQL Server DBA & ArchitectCommented:
I meant "Its the problem with the incoming data or the destination table structure"
0
 
RawasiAuthor Commented:
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
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
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
 
RawasiAuthor Commented:
sorry i don't understand....
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
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
 
RawasiAuthor Commented:
how i can do the Route the error records into a log file. ?
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
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
 
nmcdermaidCommented:
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!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now