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.
| 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. |
 |
|
|
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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
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? |
|
|
|
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 doesntie using IF EXISTSor alternatively check if it exists, delete from target and then insert new data again------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
Importing populating csv file