September 7, 2018 at 4:40 am
Hey,
so I have the following Situation: I have a foreach Loop Container which has a flat file connection inside. I need to change the connection string for that source during runtime. I already have a Script Task which runs just ahead of that FLC and basically retrieves the file (with complete Network path) Name and sets the value for a user variable. Easiest thing would be if I could just use that User Variable as connection string. When I go ahead and evaluate the Connection string he moans that the User Variable is empty.
Should I just set a value for the User Variable which then gets changed during runtime (will this work?) or is there a better solution to this? I'm using VS2010 and the SSIS Server is 2012.
September 7, 2018 at 5:18 am
DinoRS - Friday, September 7, 2018 4:40 AMHey,
so I have the following Situation: I have a foreach Loop Container which has a flat file connection inside. I need to change the connection string for that source during runtime. I already have a Script Task which runs just ahead of that FLC and basically retrieves the file (with complete Network path) Name and sets the value for a user variable. Easiest thing would be if I could just use that User Variable as connection string. When I go ahead and evaluate the Connection string he moans that the User Variable is empty.
Should I just set a value for the User Variable which then gets changed during runtime (will this work?) or is there a better solution to this? I'm using VS2010 and the SSIS Server is 2012.
Should I just set a value for the User Variable which then gets changed during runtime (will this work?)
Yes it will. Put a default value in the variable which allows things to work at design time.
Foreach loops can retrieve file paths too, by the way (assuming you are looping around files).
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
September 7, 2018 at 5:23 am
Thanks for the confirmation, it did work today and I hope tomorrow it'll pass a different value 😉
I'm actually not sure why (someone passed this half way done to me) we're looping around files in this case (as there will be only 1 file in that folder per day anyways), I have one file a day and to my basic knowledge a sequence should do well enough.
September 7, 2018 at 5:29 am
DinoRS - Friday, September 7, 2018 5:23 AMThanks for the confirmation, it did work today and I hope tomorrow it'll pass a different value 😉I'm actually not sure why (someone passed this half way done to me) we're looping around files in this case (as there will be only 1 file in that folder per day anyways), I have one file a day and to my basic knowledge a sequence should do well enough.
The foreach loop is a useful mechanism for handling this, even if there is only one file, for the following reasons:
1) If the package runs and there is no file available, the package completes gracefully, without error.
2) If the file name varies (for example, if it includes the current date), the foreach loop can pick up the file name/path and assign it to a variable for you.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
September 7, 2018 at 5:57 am
Ok, sounds good. however I haven't managed to make use of the FLC to do just this (2) so I'm trying to solve it by a Script Task. I can have the FLC do something against filename_*.txt but to retrieve the current newest filename is above my knowledge scope when it comes to BI and FLC.
September 7, 2018 at 6:03 am
DinoRS - Friday, September 7, 2018 5:57 AMOk, sounds good. however I haven't managed to make use of the FLC to do just this (2) so I'm trying to solve it by a Script Task. I can have the FLC do something against filename_*.txt but to retrieve the current newest filename is above my knowledge scope when it comes to BI and FLC.
If the folder contains multiple files and you want only the latest, the script task is the way to go.
But standard practice is to have a separate Archive folder and to move files there after they have been processed.
This makes your processing logic cleaner, because you always process whatever files are in your 'InProcess' folder & then move them to 'Archive' or (possibly) 'Error'. If you don't do this, your 'InProcess' folder becomes very cluttered and you are never sure whether 'file x' has been processed or not.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
September 7, 2018 at 6:17 am
This particular folder gets every day a new file with file_yyyymmdd.txt, previous days are being removed before a new file appears in that location. There's a separate Archive folder which has previous days aswell, however I do not need to move files there or something, that's handled externally.
September 7, 2018 at 6:26 am
DinoRS - Friday, September 7, 2018 6:17 AMThis particular folder gets every day a new file with file_yyyymmdd.txt, previous days are being removed before a new file appears in that location. There's a separate Archive folder which has previous days aswell, however I do not need to move files there or something, that's handled externally.
Then I do not understand why the FEL does not do what you need.
Use filename_*.txt as your file spec
Retrieve file name: Fully qualified
Variable Mappings: [your FilePathVariable], index 0.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
September 7, 2018 at 6:30 am
I don't see the issue with the FEL but inside the loop is a flat file source which needs a complete filename passed, it doesn't like * inside.
September 7, 2018 at 6:46 am
DinoRS - Friday, September 7, 2018 6:30 AMI don't see the issue with the FEL but inside the loop is a flat file source which needs a complete filename passed, it doesn't like * inside.
Understood. You should use the variable name there (the one you assigned under 'Variable Mappings').
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
September 7, 2018 at 6:55 am
yeah in the Flat File Connection I have defined as Expression ConnectionString @[User::VarFileName] which seems to work. Just the Script Component which retrieves the values for the variable is still bugging me.
September 7, 2018 at 7:22 am
DinoRS - Friday, September 7, 2018 6:55 AMyeah in the Flat File Connection I have defined as Expression ConnectionString @[User::VarFileName] which seems to work. Just the Script Component which retrieves the values for the variable is still bugging me.
It's a script task, not a script component (these are different things).
@[User::VarFileName] should be coming from the foreach loop.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
September 7, 2018 at 7:32 am
You're right, it's a Script Task, not a Component. Not entirely sure how the variable should be coming from the FEL, as the first thing that's needed inside the loop is the flat file connection string.
September 7, 2018 at 7:45 am
DinoRS - Friday, September 7, 2018 7:32 AMYou're right, it's a Script Task, not a Component. Not entirely sure how the variable should be coming from the FEL, as the first thing that's needed inside the loop is the flat file connection string.
Please read this. It shows an example of a foreach loop setting the value of a variable, which can then be used immediately within the FEL.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
September 7, 2018 at 7:52 am
Thank you, will do. Meanwhile I think I sorted my Script Task issue: It's nice to use a Proxy execution account when looking up on a Network path, especially if you're unaware that all SQL Services on this box run under local accounts ... just about to verify hopefully soon if that was the cause of my issue
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply