SSIS to import CSV Files from Folder

Brickwall
Brickwall used Ask the Experts™
on
I have a Folder full OF CSV Files, that have File Names LIke this  PropertName-4-18.csv  with the last art of the file name as the date.  I am needing to import all this into SQL Database with SSIS.  The Data in the files is all numeric ad formated like this.

$100.00,52,23000
$200.00,79,34125

... And So on.

I am trying to figure al this out, and know i am going to need to do a for each loop.  but I am needing the Data when it is Imported to look like this:

PropertName,4-18-2012,$100.00,52,23000
PropertName,4-18-2012,$200.00,79,34125

So i need touse the date from either the file name or date the actual file was created.   Please advise.  There are literally hundreds of file so i don't want to have to process each one individually.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018

Commented:
Do the money fields have a thousands seperator ?

Does it have to be an SSIS package ?

Is this a once-off or a recurring job ?

Can we get a sample to test (are column headers in there) ?
Sr. BI  Developer
Commented:
OK, that's easy, you'll need the capture the file name anyway in a variable while you're doing the "foreach Loop"

http://www.sqlis.com/post/Looping-over-files-with-the-Foreach-Loop.aspx


You may only add an expression to that variable to shred the extension and keep only the name in a "derived column" and that will be your new column.

Also regarding the date, which date you need to capture exactly? because if it's today's date that will be easy in the same derived column, if it's the file creation date, that's easy too but you'll need to use a script task and utilize another variable to get the date.

http://microsoft-ssis.blogspot.com/2011/03/get-file-properties-with-ssis.html


Let me know if you help with any step mentioned here.
ValentinoVBI Consultant
Most Valuable Expert 2011

Commented:
As a side-note: if you have any influence on the naming of those files, I suggest to change the convention to something like:

20120418-PropertName.csv

Advantages:
year is in the file name as well, it will prevent you from throwing your PC out the window next year
files can be sorted chronologically on name - not to be underestimated: the SSIS ForEach loops over files sorted on name

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial