CTE in OLE DB Command Data Flow Transformation

I am trying to use a CTE in an OLE DB Command data flow transformation object. However, when I enter the cte and corresponding query in the SqlCommand field of the OLE DB command editor dialog, I get a syntax error. Can CTE's be used data flow objects? I have been able to use them in an Execute SQL Control Flow Item, but not in any data flow item.

I was able to paste a query using a CTE inside of the OLE DB Command with no problem(just a quick copy and paste- no parameters invloved); then I think CTEs are not the problem. May be is the parameter mapping or something else. Could you post the query and the error so folks around here can get a better picture of the problem.|||

Thank you for your reply. I was able to successfully use a CTE in an OLE DB Source data flow component. I had a silly syntax error.

I am now running into a problem using a CTE in an OLEDB command object. There are two levels of the problem.

1. I can use a straight forward CTE in an OLEDB command object if I use the "Native OLE DB\SQL Native Client" provider.

For example, this works:

/**BEGIN QUERY**/

with TestCTE2(prod_id) as
(
select ProductID from Production.Product p
where p.ProductID = 321
)

select prod_id from TestCTE2

/**END QUERY**/

but if I try to modify and use a parameter, like:

/**BEGIN QUERY**/

with TestCTE2(prod_id) as
(
select ProductID from Production.Product p
where p.ProductID= ?
)

select prod_id from TestCTE2

/**END QUERY**/

I get the following error:

Error 2 Validation error. Data Flow Task: OLE DB SQL Native Client [863]: An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Syntax error, permission violation, or other nonspecific error". Package.dtsx 0 0

2. If I try and use a CTE in an OLE DB Command data flow object using the "Native OLE DB\Microsoft OLE DB Provider for SQL Server" provider

I can't enter the first query from above at all, i get the following error message:

Error 1 Validation error. Data Flow Task: OLE DB provider for SQL Server [875]: An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80040E14 Description: "Statement(s) could not be prepared.". An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80040E14 Description: "Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.". An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80040E14 Description: "Incorrect syntax near the keyword 'with'.". Package.dtsx 0 0

which I find curious, because there is no previous stataments.

The above queries are going against the adventureworks database.

Any further insight? Thanks for your help.

Kyle Key

|||

Kyle,

That was the test I performed in my initial post; and you are right, as soon as you add a parameter it throws an error.

Just out of curiosity; what is your ultimate goal when trying to combine a CTE and an OLEDB command? Are you trying to update rows or what?

For performance reasons, I try to stay away of OLE DB Commands. In some cases, you could replace the OLE DB Command by a OLEDB Destination that point to a staging table and then in control flow you can use a Execute SQL task to execute the same SQL statement using staging table as a reference to constraint which rows should be affected. The advantage of this procedure is to execute the sql statement only once; instead of 1 per row when an OLE DB command is used.

This does not answer your question but could give you other options

|||

I'm using the CTE to flatten out a hierarchy. I can get a list of nodes in a hierarchy, and was hoping to use the OLE DB Command transformation to loop through those nodes to get the leaf nodes and update those leaf nodes. I was going under the assumption that using a staging/temporary table would be costly to performance, but I may have to rethink my approach. I guess getting it done is better than spinning my wheels.

Do you know if the problem I'm seeing is a bug or functioning as designed? I was going to install the latest CTP for SP2 and see if the behavior is any different.

|||

I actually don't know if that would be fixed in SP2. But I found a work around; just place the SQL Statement inside of a stored procedure and then called it from the OLE DB Command.

I ran a test against AdventureWorks, see here for more details:

http://rafael-salas.blogspot.com/2006/12/passing-parameters-to-ole-db-command.html

Pleas, let me know if you found a diffrent approach.

thanks

|||

Kyle Key wrote:

I'm using the CTE to flatten out a hierarchy. I can get a list of nodes in a hierarchy, and was hoping to use the OLE DB Command transformation to loop through those nodes to get the leaf nodes and update those leaf nodes. I was going under the assumption that using a staging/temporary table would be costly to performance, but I may have to rethink my approach. I guess getting it done is better than spinning my wheels.

Do you know if the problem I'm seeing is a bug or functioning as designed? I was going to install the latest CTP for SP2 and see if the behavior is any different.

Kyle,

This probably isn't the optimum way of going about this. As Rafael says the OLE DB Command is not at all performant (we can elaborate as to why if needs be).

In your case it does seem as though using T-SQL will be a better bet. If you really do want to execute this functionality in the data-flow and not have to operate on the data row-by-row a la the OLE DB Command then you could use an asychsronous script component.

It would help to understand the problem better. How are your values in the data-flow being used to update the existing data?

-Jamie