Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Problem loading csv files with SSIS


Problem loading csv files with SSIS

Author
Message
ron.grace
ron.grace
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 55
I receive a number of csv files from a third party on a regular basis, it could be 1 it could be 20 and they'll never be the same file names. The records all end with a comma which causes a problem when I try to load in to a sql table as sql is expecting there to be another column. If I manually remove the trailing comma and run my package the files loads correctly. Is there a quick and easy way of removing the character from each record of each file, I assume it will be some sort of script task inside a foreach loop container, but I don't know what code to use, any ideas would be appreciated
Chris Harshman
Chris Harshman
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2681 Visits: 3278
ron.grace (3/9/2013)
I receive a number of csv files from a third party on a regular basis, it could be 1 it could be 20 and they'll never be the same file names...

You should be able to handle this easily with a Foreach Loop Container that's setup using a Foreach File Enumerator. What I typically do is use this pointed at a specific folder, then the last task in the container is to move the file I've just done into a processed folder.

ron.grace (3/9/2013)
...The records all end with a comma which causes a problem when I try to load in to a sql table as sql is expecting there to be another column...

I wouldn't bother trying to script removal of the commas, this seems like extra work. If you edit the flat file connection in the SSIS connection manager, you should be able to have an extra column in there that just isn't mapped to anything in your data flow destination task. You may see a yellow triangle in SSIS warning you that it isn't mapped, but it will still run, no big deal.
Mahmood.BilalB
Mahmood.BilalB
Right there with Babe
Right there with Babe (775 reputation)Right there with Babe (775 reputation)Right there with Babe (775 reputation)Right there with Babe (775 reputation)Right there with Babe (775 reputation)Right there with Babe (775 reputation)Right there with Babe (775 reputation)Right there with Babe (775 reputation)

Group: General Forum Members
Points: 775 Visits: 162
Excellent suggestion!!!

Ron just keep in mind to change change the package for DelayValidation = False, otherwise your package will fail.
ron.grace
ron.grace
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 55
Thanks for the responses guys, I'll give 'em a go
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search