June 29, 2006 at 11:13 pm
Task Objective:
Iterate through rows in a table. Each table row contains a filename that needs to be FTP'd from a remote server.
Task Development Effort:
I have a Foreach ADO Enumerator Loop that is selecting rows from a SQL table. I am returning the variable User::strHeader_FileName from the table.
Within this Foreach Loop, I am using a script task with a Msgbox function to display the value of User::strHeader_FileName
When I execute the loop, the first iteration displays: "outbound.2005.pgp" which is the correct value from the first row of the SQL table. The remaining iterations also display the correct value for each row returned.
Now that I think I have this figured out.
Within the Foreach Loop, I added a FTP task. I've set the FTP task's isRemotePathVariable param to "True". The variable User:RemotePath is selected as for RemotePath parameter.
The EvaluateAsExpresion property for the User:RemotePath variables is set to "True"
The Expression property for the User:RemotePath variable, is set to "/outbound/" + @[User::strHeader_FileName]
Ready to test my task.
When I executed the loop, I expecting the first iteration of the loop to evaluate the User:RemotePath variable to:
"/outbound/outbound.2005.pgp" Just as it did in my Msgbox example.
That did not happen and the task ended with an error that I could not determine.
What I can determine happened is that the User:RemotePath did not evaluate within the expression parameter the value of the @[User::strHeader_FileName] and the User:RemotePath evaluated to "/outbound/" NOT "/outbound/outbound.2005.pgp"
The FTP task faild because the User:RemotePath is not pointing to a valid filepath on the remote server...
My question is:
Why does the Msgbox is the script task display the correct value of @[User::strHeader_FileName]
while the User:RemotePath variable expression does not recognize the value of @[User::strHeader_FileName] ?
Side Note:
If I set User:RemotePath Expression value to "/outbound/" + "outbound.2005.pgp", the FTP executes perfectly.
This tells me that you can use the expression parameter of the FTP task "RemotePath" parameter.
Any thoughts ? Is this a bug ?
Steve Taylor
July 3, 2006 at 11:57 am
After working through the task above many times, the problem (I think) ended up being a bad column value within the table I was looping through.
Still new to SSIS and not sure why I could not see any error logging for this. It would have made the debugging easier..
The solution is just as I have outlined above.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply