ole db source query assistance requested

my query is below:

"SELECT * FROM " + @.[User:: TableName] +
" WHERE OrderDate = " + "'" + @.[User::dTrxnDate] + "'"

needless to say, the ole db source editor is giving me syntax errors. the data type of TableName is string. the data type of dTrxnDate is DateTime.

can someone please help me resolve the syntax errors?

thanks in advance.

"SELECT Column FROM " + @.[User::TableName] + "
WHERE DateFilterColumn = '" +
(DT_WSTR,4)YEAR(@.[User::DateTimeVar])
+ RIGHT("0" + (DT_WSTR,2)MONTH(@.[User::DateTimeVar]), 2)
+ RIGHT("0" + (DT_WSTR,2)DAY(@.[User::DateTimeVar]), 2)
+ "'"

Expression Date Functions
(http://wiki.sqlis.com/default.aspx/SQLISWiki/ExpressionDateFunctions.html)

Expression Samples
(http://wiki.sqlis.com/default.aspx/SQLISWiki/ExpressionSamples.html)

|||

DarrenSQLIS wrote:

"SELECT Column FROM " + @.[User::TableName] + "
WHERE DateFilterColumn = '" +
(DT_WSTR,4)YEAR(@.[User::DateTimeVar])
+ RIGHT("0" + (DT_WSTR,2)MONTH(@.[User::DateTimeVar]), 2)
+ RIGHT("0" + (DT_WSTR,2)DAY(@.[User::DateTimeVar]), 2)
+ "'"

Expression Date Functions
(http://wiki.sqlis.com/default.aspx/SQLISWiki/ExpressionDateFunctions.html)

Expression Samples
(http://wiki.sqlis.com/default.aspx/SQLISWiki/ExpressionSamples.html)

thanks darren, but i'm still getting a syntax error. my query is below:

"SELECT * FROM " + @.[User::TableName] +
" WHERE OrderDate = '" + (DT_WSTR,4)YEAR(@.[User::dTrxnDate]) + RIGHT("0" + (DT_WSTR,2)MONTH(@.[User::dTrxnDate]), 2) + RIGHT("0" + (DT_WSTR,2)DAY(@.[User::dTrxnDate]), 2) + "'"

below is the syntax error:

TITLE: Microsoft Visual Studio

Error at Load FactOrders Data Flow [Source from xtract [7629]]: An OLE DB error has occurred. Error code: 0x80040E14.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Statement(s) could not be prepared.".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Incorrect syntax near '+'.".

ADDITIONAL INFORMATION:

Exception from HRESULT: 0xC0202009 (Microsoft.SqlServer.DTSPipelineWrap)

BUTTONS:

OK|||

That expression works for me, result is -

SELECT * FROM MyTable WHERE OrderDate = '20060915'

Are you sure you have set the full expression in the variable? Have you used the expression builder, which only became available for the variable Expression property with SP1. Previously pasting a multi-line expression into the single line property grid box did not work, you just got the first line.

What does your expression evaluate to in the variable's expression ?

|||

DarrenSQLIS wrote:

That expression works for me, result is -

SELECT * FROM MyTable WHERE OrderDate = '20060915'

Are you sure you have set the full expression in the variable?

i'm evaluating the expression within the ole db source editor. should i evaluate the expression within a variable instead?

my understanding is that the ole db source can evaluate ssis expressions. is this not true?

|||i'm still seeking assistance.|||

Duane Douglas wrote:

i'm still seeking assistance.

Duane,

The Ole DB Source only understands SQL, you cannot use SSIS expression there;tWhat you have to do is to put your expression in a variable(create a variable and then press F4 to get access to the propety pannel) and then in your OLE DB Source component choose Data Access Mode : SQL Command from variable.

Rafael Salas

|||

Rafael Salas wrote:

Duane Douglas wrote:

i'm still seeking assistance.

Duane,

The Ole DB Source only understands SQL, you cannot use SSIS expression there;tWhat you have to do is to put your expression in a variable(create a variable and then press F4 to get access to the propety pannel) and then in your OLE DB Source component choose Data Access Mode : SQL Command from variable.

Rafael Salas

that's what i thought, but i wasn't sure.

thanks.

|||

Rafael Salas wrote:

Duane Douglas wrote:

i'm still seeking assistance.

Duane,

The Ole DB Source only understands SQL, you cannot use SSIS expression there;tWhat you have to do is to put your expression in a variable(create a variable and then press F4 to get access to the propety pannel) and then in your OLE DB Source component choose Data Access Mode : SQL Command from variable.

Rafael Salas

that's what i thought, but i wasn't sure.

thanks.