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 2012 Forums
 SSIS and Import/Export (2012)
 Importing populating csv file

Author  Topic 

danieladam143
Starting Member

2 Posts

Posted - 2013-10-02 : 03:44:09
I have a CSV file which is getting information about every 30 seconds from a third party program and I want that data to be imported into my SQL DB. I tried using the import export wizard but it didn't like it because the file was being used by the third party program.

Is anyone able to provide any insights? Apologies for my noobyness, I am brand new to SQL!

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2013-10-02 : 04:29:27
It sounds like you don't have access to the file because it is locked. No real way round that - you need to get a copy of the file so that you can import that.
It's always better to do a copy as that is more likely to ensure that it is complete and free. Best is to get the process that creates the file to create the copy which is specific to your process. Then you can move or delete it after you are done and that gives an easy way of detecting whther you have a problem or are behind - also makes it easy to reprocess files as you just dump them in a folder.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-04 : 02:10:54
what you cam do is to create a ssis package to have a file system task which will take a copy of the file (use copy file task) to a working folder that you set up. then do the processing on the copy of file created. after processing is over, either move file to an archive location (use Move File option in File System Task) or delete it from working folder (Delete File in File System Task). I would prefer former as it will make sure file copy is available for any review later.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

danieladam143
Starting Member

2 Posts

Posted - 2013-10-07 : 03:10:53
Hi guys, I've spent the last couple of days playing around with SSIS and tried the file system task option which worked great. Just one issue though, I need to run the SSIS package several times and it adds duplicate rows every time I run it. Is there an easy way to avoid this?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-07 : 05:52:25
quote:
Originally posted by danieladam143

Hi guys, I've spent the last couple of days playing around with SSIS and tried the file system task option which worked great. Just one issue though, I need to run the SSIS package several times and it adds duplicate rows every time I run it. Is there an easy way to avoid this?


yep...add a check using execute sql task to see if row already exists and do insert only if it doesnt

ie using IF EXISTS

or alternatively check if it exists, delete from target and then insert new data again

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -