using SSIS to import a CSV file to a table while 3 x field must be unique`

marrowyung
marrowyung used Ask the Experts™
on
Dear all,

we are running SQL server 2005 and we want to import files to a table and then make sure that 3 x unique column can proof that the data is good quality, this mean there are only 1 record base on the combination of 3 x column, how SSIS help on this.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
I see two possible conditions here:

1)  If there is a duplicate, no records are to be imported.

2)  If there is a duplicate, import only the first record (or the last record, depending on specification).


In either case, the issue is that you do not know if there are duplicates until the second duplicate record is loaded.  However, you don't want the duplicate data loaded.

In case (2), then importing with an error handler for duplicate rows should work fine.  The first insert works, but subsequent inserts of duplicate rows (based on the unique columns) fails.  The import continues.

In case (1), I would use a different process.  I would import into a copy of the destination table first (a staging table), then delete any duplicated records, then finally copy records from the staging table to the final destination.
marrowyungSenior Technical architecture (Data)

Author

Commented:
it seems 2 is better, but does SSIS provide any tools for us that allow we specify the 3 x colume is unique that based on this, no row can be duplicate?
marrowyungSenior Technical architecture (Data)

Author

Commented:
should I use select distinct column A, B and C on that target table using SSIS? anyway to do it ?

How about adding one more unique column in the target table, this column must not exist in ithe original csv file?

Senior Technical architecture (Data)
Commented:
Dear sir,

I found an error when I am importing CSV file to SQL server, I user SQL native client to write data to the target SQL server, I get this error that make my SSIS package can't complete at all:

"SQL Server destination [14] Error: An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Could not bulk load because SSIS file mapping object 'Global\D
marrowyungSenior Technical architecture (Data)

Author

Commented:
please close this question and have the point refund.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial