January 25, 2018 at 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
January 28, 2018 at 11:25 pm
BeatleBoy - Thursday, January 25, 2018 2:42 PMHello,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.
January 29, 2018 at 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.
February 5, 2018 at 2:00 am
BeatleBoy - Monday, January 29, 2018 9:28 AMHello,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.
February 5, 2018 at 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.
February 5, 2018 at 11:42 pm
BeatleBoy - Monday, February 5, 2018 12:16 PMHi,
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 ?
February 6, 2018 at 7:25 am
subramaniam.chandrasekar - Monday, February 5, 2018 11:42 PMNow, 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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply