March 9, 2013 at 2:31 am
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
March 11, 2013 at 1:08 pm
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.
March 11, 2013 at 10:39 pm
Excellent suggestion!!!
Ron just keep in mind to change change the package for DelayValidation = False, otherwise your package will fail.
March 12, 2013 at 1:13 am
Thanks for the responses guys, I'll give 'em a go
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply