Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Problem loading csv files with SSIS Expand / Collapse
Author
Message
Posted Saturday, March 9, 2013 2:31 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, November 27, 2013 4:05 AM
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
Post #1428859
Posted Monday, March 11, 2013 1:08 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 10:01 AM
Points: 1,913, Visits: 2,064
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.
Post #1429449
Posted Monday, March 11, 2013 10:39 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Monday, October 27, 2014 12:13 AM
Points: 692, Visits: 141
Excellent suggestion!!!

Ron just keep in mind to change change the package for DelayValidation = False, otherwise your package will fail.
Post #1429573
Posted Tuesday, March 12, 2013 1:13 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, November 27, 2013 4:05 AM
Points: 20, Visits: 55
Thanks for the responses guys, I'll give 'em a go
Post #1429603
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse