Package Configuration (SQL Server) to assign location of source flat file???
-
I've got 12 or so flat files that need to be brought into SQL Server. They'll all exist in the same directory. Further, they're all the same format (first line contains col names, all pike delimits, all have same exact cols) Hoorah! Should be easy, right?
My first idea is to have a ForEach looper for the 12 files. Manually map one of them, then change the connection string for the flat file each loop using "dir from package config" + "\" + foreach.filename.
Should I assign the parsed together directory/file string to a user variable? Is there some way to assign this value to the flat file "directory" property directly?
Is there an entirely different way to skin this cat? All the examples I'm finding seem to rely on an XML configuration, but I'm dead set against that.
Here are the first couple lines in my configuration table:
insert into [ssis configurations] (configurationfilter, configuredvalue, packagepath, configuredvaluetype)values ('A1 source directory', 'Z:\special\Trustee Data\','@[User::a1TrusteeDir]','string')insert into [ssis configurations] (configurationfilter, configuredvalue, packagepath, configuredvaluetype)values ('A2 source directory', 'Z:\special\Trustee Data\','@[User::a2TrusteeDir]','string')Can someone please steer me in the correct direction? THANKS!
-
I should mention... the reason I thought to use the package configuration approach is because the directory that it's going to source from will change (based on user input), and I need to be able to accomodate that change.
IOW, the actual path of the source file is Z:\20090430\thesourcedir\, but the next go round, this path will be Z:\20090531\thesourcedir\.
I figured that by putting it into a User Variable as Z:\dateplaceholder\thesourcedir\, I could then do something like "REPLACE(@User::SourceDir,'dateplaceholder',thecorrectdatestring)"
It's starting to sound convoluted, I know. Thanks for the help gang.
-
More precisely...
How can I programmatically change the "Directory" attribute of the ForEach Loop container based on user input?
THANKS!
-
Greg J (5/27/2009)
More precisely...How can I programmatically change the "Directory" attribute of the ForEach Loop container based on user input?
THANKS!
1. Right-click on the For Each Loop container and select Properties.
2. Go to Expressions and setup expression for Directory property.
3. Try to play with the expression you have provided above. You are really close.
---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/
-
My solution was to loop the files (directory), copy (move) the file to fix temp directory and fix name, do the job, move (delete) the file to archive directory.
Izhar Azati
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply