SFTP Task RemoteFilter FLEE expression

[mag...@gmail.com]

Hi,

Is there a way to use a DTS package variables in the RemoteFilter expression on the SFTP task?

I'm trying to do something like:

Modifiedtime >[User::LastDate] and Name.EndsWith([User::Filter])

but I'm not sure how to pass the Variables to the FLEE expression.

Thank you

Maged

[Ivan Peev]

Hi Maged,

Yes, you can use variables in the RemoteFilter expression. You have to setup the expression like this:

"Modifiedtime > " + [User::LastDate] + " and Name.EndsWith(" + [User::Filter] + ")"

[mag...@gmail.com]

Hi Ivan,

I tried that, but it still produces a syntax error. It doesn't look like the variables could be passed directly to the FLEE expression.

 Do I need to set this expression into another variable than use the IsRemotFilterVarible=True and pass the new variable to Remote Filter?

thanks

Maged

[Ivan Peev]

Maged,

I would recommend you check Microsoft's documentation how to setup proper SSIS expressions. I suspect the issue below is that LastDate variable contains value of time DateTime and you have to convert it to String type to use it. 

[Ivan Peev]

Maged,

I think you have to put quotes around the Filter variable. Give it a try.

On Nov 18, 2014, at 12:54 PM, Maged Kalliney <mag...@gmail.com> wrote:

Ivan,

I don't think it the date/sting format issue. I know for the expression I have to pass the date as a literal surrounded by # like #08/02/2014#.

I get the same syntax error even if I remove the LastDate form the expression and just have the RemoteFilter= "Name.EndsWith( " + [User::Filter] +" )"  I also tried "Name.EndsWith( " + @[User::Filter] +" )"

the error I'm getting is:

SntaxError: Unexpected token "["; expected one of "-", <INTEGER>, <REAL>, <STRING_LITERAL>, "True", "False",
<HEX_LITERAL>, <CHAR_LITERAL>, "null", <DATETIME>, <TIMESPAN>, "(", <IDENTIFIER>, "if", or "cast"
Line: 1, Column: 21

Thanks

Maged

[Ivan Peev]

Maged,

You have not specified the variable correctly.

On Nov 18, 2014, at 1:10 PM, Maged Kalliney <mag...@gmail.com> wrote:

Tried that "Name.EndsWith( \"" + [User::Filter] +"\" )"  as well, but still same error. Please see attached

<error.png>

[Maged Kalliney]

Tried that "Name.EndsWith( \"" + [User::Filter] +"\" )"  as well, but still same error. Please see attached

[Maged Kalliney]

Ivan,

I don't think it the date/sting format issue. I know for the expression I have to pass the date as a literal surrounded by # like #08/02/2014#.

I get the same syntax error even if I remove the LastDate form the expression and just have the RemoteFilter= "Name.EndsWith( " + [User::Filter] +" )"  I also tried "Name.EndsWith( " + @[User::Filter] +" )"

the error I'm getting is:

SntaxError: Unexpected token "["; expected one of "-", <INTEGER>, <REAL>, <STRING_LITERAL>, "True", "False",
<HEX_LITERAL>, <CHAR_LITERAL>, "null", <DATETIME>, <TIMESPAN>, "(", <IDENTIFIER>, "if", or "cast"
Line: 1, Column: 21

Thanks

Maged

On Tue, Nov 18, 2014 at 12:35 PM, Ivan Peev <ivan...@cozyroc.com> wrote:

[mag...@gmail.com]

here is what worked for me:

1. create a Variable @[User::RemoteFilter] and set the EvaluateAsExpression property on it to True

2. Set the expression of the  @[User::RemoteFilter] to the Filter needed ( "Name.StartsWith( \"" +  @[User::Filter] + "\")")

3. On the SFTP Task Set the IsRemoteFilterVariable to True

3. On the SFTP Task Set the RemoteFilter property to User::RemoteFilter

Trying to build the expression directly in the RemoteFilter property on the SFTP task didn't work for me.

thanks

Maged