SSIS data load - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SSIS and Import/Export (2008)

SSIS data load

Author

Topic

preetpalkapoor
Starting Member

6 Posts

Posted - 2013-03-28 : 03:53:52

Hi ,

I have been given an task to load a text file to a database which is simple but the file may contain the duplicate records which are already present in the DB table. So i want to load the correct records and keep other records which are not loaded to table in another text file.

Example:
text file with source data:
ID,Name
1,john
2,maya
3,amir
4,neha

Database table:
ID(primary key),name(varchar(50))
101,sneha
102,michael
1,john
4,neha

Now i want the rows in text file with ID=2,3 get loaded to database table and new text file is created with data 1,john and 4,neha...

Can you please help me in this .

Preetpal kapoor

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-28 : 05:20:15

you can use several methods

one method is to use ssis and then use a lookup task to lookup records against table based on ID,name fields. Then attach nomatch output to your destination table.

ANother method is to use OPENROWSET and use query like


SELECT * 
FROM   OPENROWSET('MSDASQL',  
   'Driver={Microsoft Text Driver (*.txt; *.csv)};DefaultDir=<path here>;', 
   'SELECT * FROM [filename.txt];' 
)  t
WHERE NOT EXISTS (SELECT 1 FROM Table WHERE ID = t.ID AND name = t.name)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources