August 13, 2010 at 3:29 pm
I have a text file in a folder and i want to use a part of the file name as a row in a database.
File Ex: 072010abc.txt - here i want to use the 072010 as a column so i could use this to query the data for individual months.
I tried using the method mentioned in this post:
http://www.sqlservercentral.com/articles/SSIS/67871/
But this method fails as the file name is not stored anywhere. I went to the discussions and they mentioned to add a variable as a file name, but my file name changes every month so this fails. How should i solve this problem?
August 16, 2010 at 4:57 am
In the beginning of the article, it is explained how you can add a variable to the variable mappings of the for each loop container. This variable will contain the filename. The contents of this variable will change every time the loop picks up a new file.
If you only need a part of the filename, create another variable and use an expression to substract only the part you need from the original variable (with substring). Finally, put the variable value in the rows. You can do this afterwards with a simple UPDATE statement, or you can add an extra OLE DB source with the statement SELECT ? As Filename, where the ? maps to the variable. Merge this input with your flat file input.
Note: the flat file source has an option to include the filename in the rows, but that will give you the full filename. http://sqlblog.com/blogs/jamie_thomson/archive/2010/03/31/filenamecolumnname-property-flat-file-source-adapter-ssis-nugget.aspx
You can use this and then issue an UPDATE statement against your table to replace the full filename with only the part you need.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply