SSIS: Use of parametrized SQL statement in OLE DB

#31

On Apr 10, 4:13*pm, jhofm... (AT) googlemail (DOT) com wrote:

Quote:

Hi Sergei,

Glad I could help with the parameter issue - it was a while ago that I
did this, so it might be useful if you post your findings somewhere
regarding when "?"'s are accepted and when they aren't

Regarding the column list issue; as one of the posts in your link
suggested, this might be due to the use of temporary tables in your
stored procedure. *Basically SSIS doesn't like them! *There is a work-
around, but it can result in the stored proc being executed 5x instead
of just once ... hardly ideal. *Otherwise you can try changing any
temp tables into table variables - these should work fine with SSIS.

I've also had problems in the past with the column list when using
parameterised SP calls. *To get around this, I usually set up task
using hard-coded SP parameters. *This usually allows the parser to get
a resultset for preview and define the column list. *Once the columns
are set then you can parameterize the statement and the metadata will
remain.

Good luck!
J

I had to use a mix of both: SP that uses table variable. Basically,
when I enabled SP with a temp table, SSIS completely refused to show
me the columns. And when I tried using table variable from within OLE
DB query - it refused to accept the "?" parameter, no matter how much
I moved it around. So I ended up having an SP with table variable
inside. Haha. Performance is a bit worse than direct query test I did
with hardcoded SQL straight from OLE DB object, but what can you do?

I had the trick about hardcoding the parameter to get column headers
figured out already.

Thanks!

S.

#32

On Apr 10, 4:13*pm, jhofm... (AT) googlemail (DOT) com wrote:

Quote:

Hi Sergei,

Glad I could help with the parameter issue - it was a while ago that I
did this, so it might be useful if you post your findings somewhere
regarding when "?"'s are accepted and when they aren't

Regarding the column list issue; as one of the posts in your link
suggested, this might be due to the use of temporary tables in your
stored procedure. *Basically SSIS doesn't like them! *There is a work-
around, but it can result in the stored proc being executed 5x instead
of just once ... hardly ideal. *Otherwise you can try changing any
temp tables into table variables - these should work fine with SSIS.

I've also had problems in the past with the column list when using
parameterised SP calls. *To get around this, I usually set up task
using hard-coded SP parameters. *This usually allows the parser to get
a resultset for preview and define the column list. *Once the columns
are set then you can parameterize the statement and the metadata will
remain.

Good luck!
J

I had to use a mix of both: SP that uses table variable. Basically,
when I enabled SP with a temp table, SSIS completely refused to show
me the columns. And when I tried using table variable from within OLE
DB query - it refused to accept the "?" parameter, no matter how much
I moved it around. So I ended up having an SP with table variable
inside. Haha. Performance is a bit worse than direct query test I did
with hardcoded SQL straight from OLE DB object, but what can you do?

I had the trick about hardcoding the parameter to get column headers
figured out already.

Thanks!

S.

#33

On Apr 10, 4:13*pm, jhofm... (AT) googlemail (DOT) com wrote:

Quote:

Hi Sergei,

Glad I could help with the parameter issue - it was a while ago that I
did this, so it might be useful if you post your findings somewhere
regarding when "?"'s are accepted and when they aren't

Regarding the column list issue; as one of the posts in your link
suggested, this might be due to the use of temporary tables in your
stored procedure. *Basically SSIS doesn't like them! *There is a work-
around, but it can result in the stored proc being executed 5x instead
of just once ... hardly ideal. *Otherwise you can try changing any
temp tables into table variables - these should work fine with SSIS.

I've also had problems in the past with the column list when using
parameterised SP calls. *To get around this, I usually set up task
using hard-coded SP parameters. *This usually allows the parser to get
a resultset for preview and define the column list. *Once the columns
are set then you can parameterize the statement and the metadata will
remain.

Good luck!
J

I had to use a mix of both: SP that uses table variable. Basically,
when I enabled SP with a temp table, SSIS completely refused to show
me the columns. And when I tried using table variable from within OLE
DB query - it refused to accept the "?" parameter, no matter how much
I moved it around. So I ended up having an SP with table variable
inside. Haha. Performance is a bit worse than direct query test I did
with hardcoded SQL straight from OLE DB object, but what can you do?

I had the trick about hardcoding the parameter to get column headers
figured out already.

Thanks!

S.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SSIS: Use of parametrized SQL statement in OLE DB

microsoft.public.sqlserver.dts microsoft.public.sqlserver.dts

Re: SSIS: Use of parametrized SQL statement in OLE DB - 04-11-2008 , 03:07 AM

Re: SSIS: Use of parametrized SQL statement in OLE DB - 04-11-2008 , 03:07 AM

Re: SSIS: Use of parametrized SQL statement in OLE DB - 04-11-2008 , 03:07 AM