Use expression to build the file connection string instead of using script task to build a variable

I've always used the Expression property to dynamically build the connection strings.  It has always worked without any problem, so I never gave it much thoughts.

However, a package we converted from DTS was built using script task to build the connection string before writing to the file in a data flow task.  The reason it was converted into a script task was because the DTS package was using an ActiveX script to generate the file path, and it was a quick and dirty 5-minutes-job to convert that into VB .NET code.  The package had delay validation switched to True all over the package.

As we are testing, we noticed that the package would fail the first time we run it from Visual Studio or from SQL Job, with an error of "unable to open file "" ", but succeed in all subsequent tries.  This especially doesn't make any sense because the seconds part of getdate() was built into the file name and so should be different every time the package runs, and was confirmed by a trace file we wrote to.

For a moment I thought this could be a network issue, but nothing was consistent enough for us to be able to pinpoint the problem.  If anybody has extensive experience with script task and file destination, please provide some insight.  Thanks.

 

[2010-05-13 Update]

If you click on the variable, go to the property box and select Expression, I can put the expression there instead of on the file connection property.  Will try this next time.

Print | posted on Wednesday, February 3, 2010 10:34 AM

Feedback

No comments posted yet.

Your comment:





 

Copyright © Kevin Shyr

Design by Bartosz Brzezinski

Design by Phil Haack Based On A Design By Bartosz Brzezinski