September 16, 2010 at 10:39 pm
Hi
I have created one SSIS package which will load files from one perticular folder into database. as I have no. of files and all the file structure is different so I am using BULK INSERT for loading the files into database but to get the file name I am using Foreach File Enumerator. In this I have to hard code directory specification. Is it possible instead of giving hard code value it should take from variable? in short I want to read files from dynamic directory. Today it is loading from folder1 and tomorrow from folder2 then I want to make this change in the database and based on that value my SSIS package will read that directory. I know i can make this change in SSIS package but I do not want to do that, i want to make change in database and let SSIS read this value from database and use it. Thanks for your help
Regards
D
September 17, 2010 at 7:07 am
Add an Execute SQL Task to get the value from the database.
Put this value into a variable.
Add a For-Each Loop container.
Double-click the For-Each Loop container.
Click on the Collection tab.
In the ForEach Loop Editor section, click on the ellipse (...) button beside "Expressions".
In the Property Expressions Editor screen, click on the Property. It becomes a drop down - select Directory. Set the expression to the variable that builds the directory.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
September 17, 2010 at 7:16 am
dpatelia (9/16/2010)
HiI have created one SSIS package which will load files from one perticular folder into database. as I have no. of files and all the file structure is different so I am using BULK INSERT for loading the files into database but to get the file name I am using Foreach File Enumerator. In this I have to hard code directory specification. Is it possible instead of giving hard code value it should take from variable? in short I want to read files from dynamic directory. Today it is loading from folder1 and tomorrow from folder2 then I want to make this change in the database and based on that value my SSIS package will read that directory. I know i can make this change in SSIS package but I do not want to do that, i want to make change in database and let SSIS read this value from database and use it. Thanks for your help
Regards
D
I would suggest use of WQL Query to implement such requirement.
OR
A FileSystemWatcher on a specified root directory.
Nature: The package will begin execution only when the files are available within the folder structure...
Raunak J
November 12, 2010 at 5:14 pm
WayneS (9/17/2010)
Add an Execute SQL Task to get the value from the database.Put this value into a variable.
Add a For-Each Loop container.
Double-click the For-Each Loop container.
Click on the Collection tab.
In the ForEach Loop Editor section, click on the ellipse (...) button beside "Expressions".
In the Property Expressions Editor screen, click on the Property. It becomes a drop down - select Directory. Set the expression to the variable that builds the directory.
'Directory' is not an option in the Expression builder. Other options?
November 12, 2010 at 5:27 pm
It's there, just got to be on the right tab. There's two sets of expressions to build in that object. One controls external properties, the other internal properties.
The Expressions item on the left is not the one you want. You want the one that hides in the main window under 'collection' on the left.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply