SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 SSIS and Import/Export (2012)
 Importing populating csv file
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

danieladam143
Starting Member

2 Posts

Posted - 10/02/2013 :  03:44:09  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 10/02/2013 :  04:29:27  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
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

India
52317 Posts

Posted - 10/04/2013 :  02:10:54  Show Profile  Reply with Quote
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 - 10/07/2013 :  03:10:53  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 10/07/2013 :  05:52:25  Show Profile  Reply with Quote
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

Edited by - visakh16 on 10/07/2013 05:57:16
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000