February 5, 2009 at 2:39 am
Hi All,
I'm new to SSIS so sorry for the simple question.
My structure is as follows
EXECUTE SQL TASK:
This returns it's data set to a variable called User::objFiles
FOREACH LOOP (ADO Enumerator)
This uses the above object User::objFiles and populates to variables.
User::ServerFileName and User::UserFileName
FILE SYSTEM TASK (with my loop container)
This task is used for copying files based on the data in my objFiles variable.
Basically what I want to do is to set the source of the File task to be a concatenation of a variable called:
User::SourcePath + User::ServerFileName
The User::SourcePath default value is "C:\Files\"
Now I can't concatenate these two in the actual Task (not that I can see or work out)
so was wondering how people deal with this situation?
Thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
February 5, 2009 at 2:51 am
In the file connection manager, click on properties and then click on expressions.
Create an expression for connection string and use the expression builder to add your user variable adn the system variable
February 5, 2009 at 2:56 am
what type of connection should I be creating?
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
February 5, 2009 at 3:00 am
OK found another solution.
I changed the properties of my variable to be an expression and then build the expression based on that
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
February 5, 2009 at 3:03 am
Were you using a Flat-File Connection ?
February 5, 2009 at 3:15 am
actually didn't use a connection all.
I just used the paths as variables.
However I have come across another problem in that if the files doesn't exist my I get errors my code doesn't continue.
should I be checking to see if the file exists first? if so how?
Or should I be turning of the error handling for this container? if so I can't find how to do this with a File system task 🙁
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
February 5, 2009 at 3:27 am
i haven't really used the file system task a lot so may not be much help.
Checking if the file exits with a 'dir' command or something would be the best, but i can not find a way to do this. Other wise failing the package may be the only option.
Maybe someone else has some better idea..
February 5, 2009 at 3:41 pm
I'd do the concatenation, file exist check and file copy in a Script Task.
February 16, 2009 at 2:39 am
Hi Christopher!
I have a few comments/suggestion around this:
- if the only purpose of the SQL Task is to get you a set of file names(?) then you don't need it!
- instead, use the full power of the ForEachLoop:
- Rightclick(Edit): go to Collection/ForEach Loop Editor/Expressions and hit the little box with the three dots to the right:
- in the Property Expressions Editor, select the Directory property and set it's expression to "C:\Files\"
- Now, back in Collection:
- select "Fully qualified" under 'Retrieve file name'
- finally: Leave Collection, go to 'Variable Mappings':
- state i.e. "MyFileTaskSource" as name of your first(only) Variable
- set it's Index to 0
And now, voilá, by doing this you will automatically get your desired 'concatenation' value automatically into this
variable "MyFileTaskSource"!
So for each lap the ForEachLoop component do, finding yet another file, it will catch the path+name and store it in this variable.
...and you then use FileSystemTask component(s) to rename, delete, move or whatever you like do to with the captured file(s).
A last comment, I suggest you always try to investigate the 'standard power' of SSIS before running into the "I solve this in a Script Component, writing tons of VB-code"!
Cheers,
BF
February 19, 2009 at 5:39 am
See the below link for File exists SSIS script task:
http://204.9.76.233/Community/forums/p/3176/17209.aspx#17209
February 19, 2009 at 10:43 am
You can also create variables that are concatentations of other variables. I sometimes do this if I am going to reuse a certain combination of variables (such as path + filename) often. It can reduce the opportunity for errors (such as selecting the wrong combination of variables in an expression) especially if you have a lot of variables (provided you name them clearly).
If it was easy, everybody would be doing it!;)
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply