SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Help with passing FLEE expression into SSIS user variable


Help with passing FLEE expression into SSIS user variable

Author
Message
BeatleBoy
BeatleBoy
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 Visits: 45
Hello,

I have a SSIS component that does SFTP tasks. One such task is to fetch a list of remote files. The tasks allows me to use a FLEE expression string as a file filter in the fetch. When I developed the package in Visual Studio I have a string variable Config_RemoteFileFilter with the value Modifiedtime > Now.AddDays(-1) and Name.EndsWith(".txt"). Running the package there works flawlessly and I get all files updated in the last day that have a .txt extension. However, now I'm testing deploying this package to SQL Server Agent and when I put that value into the job "Set Values" configuration tab and run the DTEXEC command line that it generates in the "Command Line" tab, the job it doesn't fetch any of the files.

To debug, I put a script step into the package that outputs all of my variable values and found that when I run the package via DTEXEC, the runtime value of the variable Config_RemoteFileFilter is Modifiedtime "and" Name.EndsWith(".txt"). My hunch is that the FLEE expression is being treated as an SSIS variable expression and evaluating to this - but I am very new to SSIS so that is just a hunch. I figured the experts here would be able to take one look at it and help me figure out how to enter an escaped value into the job so that my variable is set as I expect.

Thanks in advance everyone for taking the time to read and potentially help.
Sean
Subramaniam Chandrasekar
Subramaniam Chandrasekar
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3344 Visits: 531
BeatleBoy - Thursday, January 25, 2018 2:42 PM
Hello,

I have a SSIS component that does SFTP tasks. One such task is to fetch a list of remote files. The tasks allows me to use a FLEE expression string as a file filter in the fetch. When I developed the package in Visual Studio I have a string variable Config_RemoteFileFilter with the value Modifiedtime > Now.AddDays(-1) and Name.EndsWith(".txt"). Running the package there works flawlessly and I get all files updated in the last day that have a .txt extension. However, now I'm testing deploying this package to SQL Server Agent and when I put that value into the job "Set Values" configuration tab and run the DTEXEC command line that it generates in the "Command Line" tab, the job it doesn't fetch any of the files.

To debug, I put a script step into the package that outputs all of my variable values and found that when I run the package via DTEXEC, the runtime value of the variable Config_RemoteFileFilter is Modifiedtime "and" Name.EndsWith(".txt"). My hunch is that the FLEE expression is being treated as an SSIS variable expression and evaluating to this - but I am very new to SSIS so that is just a hunch. I figured the experts here would be able to take one look at it and help me figure out how to enter an escaped value into the job so that my variable is set as I expect.

Thanks in advance everyone for taking the time to read and potentially help.
Sean

Please if you could post the error message fully. Also kindly explain why you've not gone with For each loop container ? Just a suggestion.

BeatleBoy
BeatleBoy
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 Visits: 45
Hello,

Thanks for the reply.

There is no error message - the result is merely that the variable value is set to the unexpected value of Modifiedtime "and" Name.EndsWith(".txt") rather than Modifiedtime > Now.AddDays(-1) and Name.EndsWith(".txt")

I didn't do a for each because it seemed more efficient to just pass the filter value and not have to add the extra logic to filter in SSIS and let the SFTP module only return the relevant files.

Subramaniam Chandrasekar
Subramaniam Chandrasekar
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3344 Visits: 531
BeatleBoy - Monday, January 29, 2018 9:28 AM
Hello,

Thanks for the reply.

There is no error message - the result is merely that the variable value is set to the unexpected value of Modifiedtime "and" Name.EndsWith(".txt") rather than Modifiedtime > Now.AddDays(-1) and Name.EndsWith(".txt")

I didn't do a for each because it seemed more efficient to just pass the filter value and not have to add the extra logic to filter in SSIS and let the SFTP module only return the relevant files.

Basically while designing SSIS packages we use variables within the scope of SSIS package and deploy the package to File system / SQL server / Package store / Catalog. Please could you explain why you had provided / set up the variable value into the job "Set Values" configuration tab ?

Alternatively you can declare your variables within SSIS and get the package to deployed to SQL Server. Hope this can help you ? Anything else, Kindly revert back.

BeatleBoy
BeatleBoy
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 Visits: 45
Hi,
Basically I created my package so that I set configuration options with variables so that at runtime I can tweak the behavior of the SSIS package. For example, I have an SSIS variable that is the TargetServerName and that parameterizes a data destination. So when I deploy the package, I can change where the data gets sent by merely updating the value at runtime and not have to change the package itself. In the case of the issue I'm facing, the remote SFTP filefilter is a text string that may need to change in the future. So I was hoping to have that value set in the package runtime so that I can easily update that filter in the future without having to modify the package.
Subramaniam Chandrasekar
Subramaniam Chandrasekar
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3344 Visits: 531
BeatleBoy - Monday, February 5, 2018 12:16 PM
Hi,
Basically I created my package so that I set configuration options with variables so that at runtime I can tweak the behavior of the SSIS package. For example, I have an SSIS variable that is the TargetServerName and that parameterizes a data destination. So when I deploy the package, I can change where the data gets sent by merely updating the value at runtime and not have to change the package itself. In the case of the issue I'm facing, the remote SFTP filefilter is a text string that may need to change in the future. So I was hoping to have that value set in the package runtime so that I can easily update that filter in the future without having to modify the package.

If you're facing problem while passing values at run time, Could you go / consider with the command line prompt called DTEXEC. By opening cmd.exe you can run package and even pass values into a package variable. This functionality is pretty much same as SQL Server Agent job.

Now, Post creation of your ssis package and provide your own parameters and try to excute as below,

C:\Program Files\Microsoft SQL Server\100\DTS\Binn>

DTExec.exe /f “E:\w\Integration Services Project1\Integration Services Project1\Package.dtsx” /SET \Package.Variables[User::Input_One].Properties[Value];”29″ /SET \Package.Variables[User::Input_Two].Properties[Value];”29″ /SET \Package.Variables[User::Name].Properties[Value];”TestUSer”

And Run the above

Something similar we'd used previously in our test environment to implement the same.

Could you try this approach ?

Sue_H
Sue_H
SSC Guru
SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)

Group: General Forum Members
Points: 67200 Visits: 13960
subramaniam.chandrasekar - Monday, February 5, 2018 11:42 PM
Now, Post creation of your ssis package and provide your own parameters and try to excute as below,

C:\Program Files\Microsoft SQL Server\100\DTS\Binn>

DTExec.exe /f “E:\w\Integration Services Project1\Integration Services Project1\Package.dtsx” /SET \Package.Variables[User::Input_One].Properties[Value];”29″ /SET \Package.Variables[User::Input_Two].Properties[Value];”29″ /SET \Package.Variables[User::Name].Properties[Value];”TestUSer”


Be careful though - the shared files directories are based on the version of SQL Server. 100 is 2008. For SQL Server 2012, it would be 110. And it isn't necessarily on C:\

Sue




Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search