Hello,

I've been battling this issue since morning. Some Internet forums
point out that it's some sort of a MS bug, but I couldn't find a
definative answer.


I'm trying to use SSIS OLE DB Source to get some data from remote
database. Problem is I have to use a parameter in the query, and it
spits out an error as soon as I put the "?" symbol where the parameter
should be (if i put a simple constant - no error).

here's the error content:

Parameters cannot be extracted from the SQL command. The provider
might not help to parse parameter information from the command. In
that case, use the "SQL command from variable" access mode, in which
the entire SQL command is stored in a variable.


Wonderful.

My query is complex, and there's not much my department can do insofar
creating a stored procedure at the database i'm trying to query, so it
can't be simplified much. Funny thing is that if I simplify (as a
test) my query in the "SQL Statement Text" box - it begins to work
normally, even with parameters.




Is this really a bug, and if so, how come it hasn't been fixed for so
long? I've seen dozens of users complain about this problem, and no
answer was ever given (or found at any rate).


note: this solution
http://rafael-salas.blogspot.com/200...inside-of.html

is not a good idea because my sql statment is so long that if I
stretch it into one line as the Variable Expression box requires - it
crashes too:

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



Thanks!

Hi Sergei,

I think the MS query parser struggles to understand complex queries -
in my experience this is particularly if there are any sub-queries or
derived tables in them. I have had limited success with reformatting
these queries in the past, literally by moving parts of the WHERE
clause around - I either moved all the parameterised bits either to
the top or the bottom of the where clause if I remember correctly.
This was basic trial-and-error though, I simply had time and was
trying to see what the parser would or would not accept.

I agree that the property expression option is not a good one with a
long query. In-fact I believe there is a fixed maximum number of
characters that SSIS allows you to put into this box, so you're likely
to run out of characters. The other option (as suggested in the error
message) is to save the entire SQL statement to a package variable,
and set the task to get it's command string from the variable. The
best way to do this is using a simple script task to build up your
string, and then assigning it to a variable.

Good luck!
J

Hi Sergei,

I think the MS query parser struggles to understand complex queries -
in my experience this is particularly if there are any sub-queries or
derived tables in them. I have had limited success with reformatting
these queries in the past, literally by moving parts of the WHERE
clause around - I either moved all the parameterised bits either to
the top or the bottom of the where clause if I remember correctly.
This was basic trial-and-error though, I simply had time and was
trying to see what the parser would or would not accept.

I agree that the property expression option is not a good one with a
long query. In-fact I believe there is a fixed maximum number of
characters that SSIS allows you to put into this box, so you're likely
to run out of characters. The other option (as suggested in the error
message) is to save the entire SQL statement to a package variable,
and set the task to get it's command string from the variable. The
best way to do this is using a simple script task to build up your
string, and then assigning it to a variable.

Good luck!
J

Hi Sergei,

I think the MS query parser struggles to understand complex queries -
in my experience this is particularly if there are any sub-queries or
derived tables in them. I have had limited success with reformatting
these queries in the past, literally by moving parts of the WHERE
clause around - I either moved all the parameterised bits either to
the top or the bottom of the where clause if I remember correctly.
This was basic trial-and-error though, I simply had time and was
trying to see what the parser would or would not accept.

I agree that the property expression option is not a good one with a
long query. In-fact I believe there is a fixed maximum number of
characters that SSIS allows you to put into this box, so you're likely
to run out of characters. The other option (as suggested in the error
message) is to save the entire SQL statement to a package variable,
and set the task to get it's command string from the variable. The
best way to do this is using a simple script task to build up your
string, and then assigning it to a variable.

Good luck!
J

Hi Sergei,

I think the MS query parser struggles to understand complex queries -
in my experience this is particularly if there are any sub-queries or
derived tables in them. I have had limited success with reformatting
these queries in the past, literally by moving parts of the WHERE
clause around - I either moved all the parameterised bits either to
the top or the bottom of the where clause if I remember correctly.
This was basic trial-and-error though, I simply had time and was
trying to see what the parser would or would not accept.

I agree that the property expression option is not a good one with a
long query. In-fact I believe there is a fixed maximum number of
characters that SSIS allows you to put into this box, so you're likely
to run out of characters. The other option (as suggested in the error
message) is to save the entire SQL statement to a package variable,
and set the task to get it's command string from the variable. The
best way to do this is using a simple script task to build up your
string, and then assigning it to a variable.

Good luck!
J

Hi Sergei,

I think the MS query parser struggles to understand complex queries -
in my experience this is particularly if there are any sub-queries or
derived tables in them. I have had limited success with reformatting
these queries in the past, literally by moving parts of the WHERE
clause around - I either moved all the parameterised bits either to
the top or the bottom of the where clause if I remember correctly.
This was basic trial-and-error though, I simply had time and was
trying to see what the parser would or would not accept.

I agree that the property expression option is not a good one with a
long query. In-fact I believe there is a fixed maximum number of
characters that SSIS allows you to put into this box, so you're likely
to run out of characters. The other option (as suggested in the error
message) is to save the entire SQL statement to a package variable,
and set the task to get it's command string from the variable. The
best way to do this is using a simple script task to build up your
string, and then assigning it to a variable.

Good luck!
J

Hi Sergei,

I think the MS query parser struggles to understand complex queries -
in my experience this is particularly if there are any sub-queries or
derived tables in them. I have had limited success with reformatting
these queries in the past, literally by moving parts of the WHERE
clause around - I either moved all the parameterised bits either to
the top or the bottom of the where clause if I remember correctly.
This was basic trial-and-error though, I simply had time and was
trying to see what the parser would or would not accept.

I agree that the property expression option is not a good one with a
long query. In-fact I believe there is a fixed maximum number of
characters that SSIS allows you to put into this box, so you're likely
to run out of characters. The other option (as suggested in the error
message) is to save the entire SQL statement to a package variable,
and set the task to get it's command string from the variable. The
best way to do this is using a simple script task to build up your
string, and then assigning it to a variable.

Good luck!
J

Hi Sergei,

I think the MS query parser struggles to understand complex queries -
in my experience this is particularly if there are any sub-queries or
derived tables in them. I have had limited success with reformatting
these queries in the past, literally by moving parts of the WHERE
clause around - I either moved all the parameterised bits either to
the top or the bottom of the where clause if I remember correctly.
This was basic trial-and-error though, I simply had time and was
trying to see what the parser would or would not accept.

I agree that the property expression option is not a good one with a
long query. In-fact I believe there is a fixed maximum number of
characters that SSIS allows you to put into this box, so you're likely
to run out of characters. The other option (as suggested in the error
message) is to save the entire SQL statement to a package variable,
and set the task to get it's command string from the variable. The
best way to do this is using a simple script task to build up your
string, and then assigning it to a variable.

Good luck!
J

Hi Sergei,

I think the MS query parser struggles to understand complex queries -
in my experience this is particularly if there are any sub-queries or
derived tables in them. I have had limited success with reformatting
these queries in the past, literally by moving parts of the WHERE
clause around - I either moved all the parameterised bits either to
the top or the bottom of the where clause if I remember correctly.
This was basic trial-and-error though, I simply had time and was
trying to see what the parser would or would not accept.

I agree that the property expression option is not a good one with a
long query. In-fact I believe there is a fixed maximum number of
characters that SSIS allows you to put into this box, so you're likely
to run out of characters. The other option (as suggested in the error
message) is to save the entire SQL statement to a package variable,
and set the task to get it's command string from the variable. The
best way to do this is using a simple script task to build up your
string, and then assigning it to a variable.

Good luck!
J

On Apr 8, 8:22*pm, jhofm... (AT) googlemail (DOT) com wrote:
Thanks, your suggestion to move the "?" around worked! Could have
saved me half a day or so.

However, i then ran into another problem of blank column list and had
to partially give up and use server-bound SP to get a workaround.

thread about blank column list:
http://groups.google.ru/group/micros...01f34314d302c1


Thanks for your suggestion though, it will be useful in the future as
it's not the first time we've had this issue around the office.


S.