OLEDBcommand is too slow

Hello,

I'm using an OLEDB Command in a DataFlow which performs a parametric query to update thousands of rowsets but it is very slow.

Is there an alternative ?

it's not the component's fault it is slow. SQL faster at set based operations.

You can insert all the rows into a temporary table and then using a SQL task, run the update by joining the two tables together.|||

Thanks!

I've tested this solution, but it seems to persisted a certains slowness.

My dataflow uses these components:

OLEDB Source --> Lookup --

|||As with anything, you have to find out where the bottleneck is. Is it the source? is it the lookup? is it the dest?

Start simple. How quickly does the source get the records? Dump everything into Trash Destination. Then add the lookup? is the lookup taking a while to cache the rows? Are you selecting the whole table or just the keys that you need? etc etc.

Finally, having a poor query in SQL source will result in a slow data flow. Are the tables correctly indexed in the source query. The dest? Two many indexes? Lookup? Indexes etc etc.

Is the final SQL update correctly indexed?

Listing two components in your data flow and saying they slow is the vaguest statement you could say.

Many reasons, more possible solutions.sql

OLEDB Wait type

I ran the command dbcc sqlperf(waitstats) to monitor the waits in our db
server, i have noticed that more than 95% of the wait time SQL Server is
experiencing is in the "OLEDB Wait" type
Can some one tell me more about OLEDB Wait type, and what causes the server
to experience such a high rate of OLEDB waits?
Thanks,
Ram
Have a look here:
http://sqldev.net/articles/WaitTypes.htm
Some of the more common things I have found with OLEDB waits are if you are
running a lot of traces, lots of DTS packages or linked servers.
Andrew J. Kelly SQL MVP
"Ram" <Ram@.discussions.microsoft.com> wrote in message
news:A26BBA12-FA18-4DF8-AFB1-B1A9263A3A51@.microsoft.com...
>I ran the command dbcc sqlperf(waitstats) to monitor the waits in our db
> server, i have noticed that more than 95% of the wait time SQL Server is
> experiencing is in the "OLEDB Wait" type
> Can some one tell me more about OLEDB Wait type, and what causes the
> server
> to experience such a high rate of OLEDB waits?
> Thanks,
> Ram
|||Thanks Andrew..
"Andrew J. Kelly" wrote:

> Have a look here:
> http://sqldev.net/articles/WaitTypes.htm
> Some of the more common things I have found with OLEDB waits are if you are
> running a lot of traces, lots of DTS packages or linked servers.
> --
> Andrew J. Kelly SQL MVP
>
> "Ram" <Ram@.discussions.microsoft.com> wrote in message
> news:A26BBA12-FA18-4DF8-AFB1-B1A9263A3A51@.microsoft.com...
>
>
|||The following article has more information on the wait types
and troubleshooting issues with different wait types:
http://www.sqldev.net/articles/WaitTypes.htm
-Sue
On Wed, 19 Jan 2005 07:21:02 -0800, Ram
<Ram@.discussions.microsoft.com> wrote:

>I ran the command dbcc sqlperf(waitstats) to monitor the waits in our db
>server, i have noticed that more than 95% of the wait time SQL Server is
>experiencing is in the "OLEDB Wait" type
>Can some one tell me more about OLEDB Wait type, and what causes the server
>to experience such a high rate of OLEDB waits?
>Thanks,
>Ram

OLEDB Wait type

I ran the command dbcc sqlperf(waitstats) to monitor the waits in our db
server, i have noticed that more than 95% of the wait time SQL Server is
experiencing is in the "OLEDB Wait" type
Can some one tell me more about OLEDB Wait type, and what causes the server
to experience such a high rate of OLEDB waits?
Thanks,
RamHave a look here:
http://sqldev.net/articles/WaitTypes.htm
Some of the more common things I have found with OLEDB waits are if you are
running a lot of traces, lots of DTS packages or linked servers.
Andrew J. Kelly SQL MVP
"Ram" <Ram@.discussions.microsoft.com> wrote in message
news:A26BBA12-FA18-4DF8-AFB1-B1A9263A3A51@.microsoft.com...
>I ran the command dbcc sqlperf(waitstats) to monitor the waits in our db
> server, i have noticed that more than 95% of the wait time SQL Server is
> experiencing is in the "OLEDB Wait" type
> Can some one tell me more about OLEDB Wait type, and what causes the
> server
> to experience such a high rate of OLEDB waits?
> Thanks,
> Ram|||Thanks Andrew..
"Andrew J. Kelly" wrote:

> Have a look here:
> http://sqldev.net/articles/WaitTypes.htm
> Some of the more common things I have found with OLEDB waits are if you ar
e
> running a lot of traces, lots of DTS packages or linked servers.
> --
> Andrew J. Kelly SQL MVP
>
> "Ram" <Ram@.discussions.microsoft.com> wrote in message
> news:A26BBA12-FA18-4DF8-AFB1-B1A9263A3A51@.microsoft.com...
>
>|||The following article has more information on the wait types
and troubleshooting issues with different wait types:
http://www.sqldev.net/articles/WaitTypes.htm
-Sue
On Wed, 19 Jan 2005 07:21:02 -0800, Ram
<Ram@.discussions.microsoft.com> wrote:

>I ran the command dbcc sqlperf(waitstats) to monitor the waits in our db
>server, i have noticed that more than 95% of the wait time SQL Server is
>experiencing is in the "OLEDB Wait" type
>Can some one tell me more about OLEDB Wait type, and what causes the server
>to experience such a high rate of OLEDB waits?
>Thanks,
>Ramsql

OLEDB Wait type

I ran the command dbcc sqlperf(waitstats) to monitor the waits in our db
server, i have noticed that more than 95% of the wait time SQL Server is
experiencing is in the "OLEDB Wait" type
Can some one tell me more about OLEDB Wait type, and what causes the server
to experience such a high rate of OLEDB waits?
Thanks,
RamHave a look here:
http://sqldev.net/articles/WaitTypes.htm
Some of the more common things I have found with OLEDB waits are if you are
running a lot of traces, lots of DTS packages or linked servers.
--
Andrew J. Kelly SQL MVP
"Ram" <Ram@.discussions.microsoft.com> wrote in message
news:A26BBA12-FA18-4DF8-AFB1-B1A9263A3A51@.microsoft.com...
>I ran the command dbcc sqlperf(waitstats) to monitor the waits in our db
> server, i have noticed that more than 95% of the wait time SQL Server is
> experiencing is in the "OLEDB Wait" type
> Can some one tell me more about OLEDB Wait type, and what causes the
> server
> to experience such a high rate of OLEDB waits?
> Thanks,
> Ram|||Thanks Andrew..
"Andrew J. Kelly" wrote:
> Have a look here:
> http://sqldev.net/articles/WaitTypes.htm
> Some of the more common things I have found with OLEDB waits are if you are
> running a lot of traces, lots of DTS packages or linked servers.
> --
> Andrew J. Kelly SQL MVP
>
> "Ram" <Ram@.discussions.microsoft.com> wrote in message
> news:A26BBA12-FA18-4DF8-AFB1-B1A9263A3A51@.microsoft.com...
> >I ran the command dbcc sqlperf(waitstats) to monitor the waits in our db
> > server, i have noticed that more than 95% of the wait time SQL Server is
> > experiencing is in the "OLEDB Wait" type
> >
> > Can some one tell me more about OLEDB Wait type, and what causes the
> > server
> > to experience such a high rate of OLEDB waits?
> >
> > Thanks,
> > Ram
>
>|||The following article has more information on the wait types
and troubleshooting issues with different wait types:
http://www.sqldev.net/articles/WaitTypes.htm
-Sue
On Wed, 19 Jan 2005 07:21:02 -0800, Ram
<Ram@.discussions.microsoft.com> wrote:
>I ran the command dbcc sqlperf(waitstats) to monitor the waits in our db
>server, i have noticed that more than 95% of the wait time SQL Server is
>experiencing is in the "OLEDB Wait" type
>Can some one tell me more about OLEDB Wait type, and what causes the server
>to experience such a high rate of OLEDB waits?
>Thanks,
>Ram

oledb source issues with parameters

Hi,

I been having issues trying to use the OLE DB Source in the DataFlowTask. If I use the SQL Command to build a SQL Statement, i.e. "select * from tablea a join tableb b on a.column1 = b.column1 where column2 = ?", I can't get the query to parse and it won't allow me to set a value to the parameter. It seems to be a bug as the only way I can see to get around it is to use a variable to place my sql statement into and use the "SQLCommand with Variable" option in the OLEDB Source. This seems pretty clunky to me as I should be able to just put my select statement in the SQL Command window, right? Is this going to be fixed in SP1?

Here's the error message I get:
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.

Any insight would be appreciated.
Thanks,
AndyAndy,
There are some "funnies" involved with using parameters which are all due to your choice of OLE DB Provider. Kirk has more info here: http://www.sqljunkies.com/WebLog/knight_reign/archive/2005/10/05/17016.aspx

However...don't do that. Use "SQLCommand from variable" option. This is not clunky, it is far far better. It lets you build your SQL statement dynamically and cannot fall victim to the vagaries of OLE DB Providers.

-Jamie|||You won't be able to parse a query with parameters. That is a known bug. But you should be able to map the parameters if you click on the Parameters button. For this kind of select query, specify the parameters name as 0, 1, 2 etc, and map them to your variables.|||ok, thanks guys. It's just hard to see your query in the variable. If I want to go see what my query is, I have to go and copy it out of the variable and put in a bunch of carriage returns to see my query. It would be nice if I could just use regular parameters. Oh well.|||Andy,
I agree - its annoying. SP1 will contain functionality that will make it easier to do this (i.e. Build your expression using the expression editor that you see in other places).

You can use the watch window to look at the value of your variables at debugtime as shown here: http://blogs.conchango.com/jamiethomson/archive/2005/12/05/2462.aspx

-Jamie|||In case your query is actually a stored proc returning a recordset, and not a select .... statement, the parameter name mappings must mach names used in the stored proc definition, at least that was my experience...|||Looking forward to SP1!! Thanks for the info.sql

oledb source issues with parameters

Hi,

I been having issues trying to use the OLE DB Source in the DataFlowTask. If I use the SQL Command to build a SQL Statement, i.e. "select * from tablea a join tableb b on a.column1 = b.column1 where column2 = ?", I can't get the query to parse and it won't allow me to set a value to the parameter. It seems to be a bug as the only way I can see to get around it is to use a variable to place my sql statement into and use the "SQLCommand with Variable" option in the OLEDB Source. This seems pretty clunky to me as I should be able to just put my select statement in the SQL Command window, right? Is this going to be fixed in SP1?

Here's the error message I get:
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.

Any insight would be appreciated.
Thanks,
AndyAndy,
There are some "funnies" involved with using parameters which are all due to your choice of OLE DB Provider. Kirk has more info here: http://www.sqljunkies.com/WebLog/knight_reign/archive/2005/10/05/17016.aspx

However...don't do that. Use "SQLCommand from variable" option. This is not clunky, it is far far better. It lets you build your SQL statement dynamically and cannot fall victim to the vagaries of OLE DB Providers.

-Jamie|||You won't be able to parse a query with parameters. That is a known bug. But you should be able to map the parameters if you click on the Parameters button. For this kind of select query, specify the parameters name as 0, 1, 2 etc, and map them to your variables.|||ok, thanks guys. It's just hard to see your query in the variable. If I want to go see what my query is, I have to go and copy it out of the variable and put in a bunch of carriage returns to see my query. It would be nice if I could just use regular parameters. Oh well.|||Andy,
I agree - its annoying. SP1 will contain functionality that will make it easier to do this (i.e. Build your expression using the expression editor that you see in other places).

You can use the watch window to look at the value of your variables at debugtime as shown here: http://blogs.conchango.com/jamiethomson/archive/2005/12/05/2462.aspx

-Jamie|||In case your query is actually a stored proc returning a recordset, and not a select .... statement, the parameter name mappings must mach names used in the stored proc definition, at least that was my experience...|||Looking forward to SP1!! Thanks for the info.

OLEDB command in SSIS

I AM working on DW building and i m using SSIS.I haev problem with data transformation OLEDB command.I have written a query to clean data in OLEDB command box ,but it takes whole lot of time because at a time it slects 6000 rows from the source and put in to destination but i have 300000 rows to process.How can i increase the size.The OLE DB command executes for every row in the data flow. Are you sure you're stating the correct component?

You should use an OLE DB Source component hooked to an OLE DB Destination component to move your data.|||I want to delete some data while transfering from one to another i.e i want to do cleaning.|||If you want this to be fast, try writing the keys for the rows to delete to a temporary table, then use an Execute SQL task to issue a batch DELETE statement. Since the OLEDB Command transform executes once for each row that passes through it, it will be slower than doing a batch.

OLEDB Command giving error for decalre and set statements at the top of the SQL script

Hi All,

I have an OLEDB command in my package that has to execute some SQL script.

But when I declare and set a variable at the top of all code, The OLEDB gives an error in column mappings tab.

My DQL script is as shown below

DECLARE @.Cost AS money

SET @.Cost=?

--Some update statements a table

OLEDB Command works if write the declare and set statements after update statements. Like below. But I don’t need it.

--Some update statements a table

DECLARE @.Cost AS money

SET @.Cost=?

I also observer that,Oledb Command gives error for the code given below.

Just paste the following Script in OLEDB command, it gives error in column mapping tab

DECLARE @.Cost AS money

SET @.Cost=?

Any Idea on this behaviour?

Thanks in advance..

Is there a solution for this?
|||I don't think you can parameterize a SET statement like that. You should use a stored procedure.
|||

You can parameterize a SET statement provided you must have INSERT/UPDATE statements at the top.

for example :

INSET INTO table(col1)

values(?)

DECLARE @.VAR1 as int

Set @.VAR1=?

Works fine...

In my case ,I have only two queries,Do you think putting those in SP is a better option.

OLEDB command gets compile errors but works in Query analyzer.

The following statement is valid in query analyzer but will not compile as a prepared statement in an OLEDB command in DTS 2005.

delete
from
purEncumbrance_Fct
where
AgreementId = ?
and FundId = ?
and AccountId = ?
and coalesce(PODistributionId,0) = coalesce(?,0)
and coalesce(VoucherDistributionId,0) = coalesce(?,0)

Why does this statement not compile?

KenTry putting square brackets around the table and column names. I have a vague recollection of this working for me in the dim and distant past.

-Jamie|||Jamie,

I tried your suggestion with great hopes, but it did not work.

I wonder if this is a bug or a limitation with prepared statements. I know this command works in query analyzer, so maybe if I place it in a stored procedure it will work. I don't want to have to manage another piece of code, but if that is what it takes, I will.|||Not tested, but I know the statement prepare stuff and OLE-DB parameters can be rather fussy. Try loosing the coalesce(?, 0) and just use ?. Assuming that works, handle the coalesce values through a derived column, e.g.

ISNULL(Column) ? 0 : Column

oledb command

I am transfering large data.

I use oledb command to insert and update as i need to make some modifications to incoming data.I do my modifications in the procedure.

But the command does not insert as the data is huge at one shot.

if i try to send small data it works fine

Its shows warning(yellow color)

How can i achive inserting huge data effeciently please help.

Yellow just indicates that the task is still working, not that there is a problem. Red indicates a problem.

The OLEDB command is not the best for moving large amounts of data. When you need to perform an update, you might try writing the update data to a temporary table, then using an Execute SQL task to perform a batch update, as that usually has better performance.

|||

Even if i had to transfer to temporary table i need to using OLEDB connection or oledb command etc to store my update data and then use execute sql task in the control flow.

As i said earlier i am using oledb command as i try making some modifications in my procedure (updating several table at one time)

How can this be done?

|||you need to insert data with an OLE DB Destination, not the command transformation. Or the SQL Server destination. The OLE DB Command is not really designed for INSERTS as it executes for every row of data going through it.

OLEDB AS400 Pipeline threads

A simple dataflow :

Data source OLEDB AS400 : - data access sql command from variable

SELECT 'DEN' AS "Company","TDEN.IHD".* FROM "TDEN.IHD" WHERE (ORDNI1 > 48960)

Data destination : OLEDB Sql Server File

- No Problem when Data Source is executed As Sql command

Purpose :

Load (new) data from AS400-files and load them in the corresponding sql server table. Therefore each day the maximum ordernumber in the sql database is searched And given to a script which makes an sql command user::SqlSelect.

All This this to prevent loading each day all records again.

I choose to make the data access by an variable because I have more then one company with the same formatted data files. e.g.

Company XXX has a file named FXXX.AAA

Company YYY " FYYY.AAA and so on ....

All data for all companies has to be imported in one sql server tabel. Ofcourse with a company field.

- ODBC Does not allow sql command from variable

Errors
[OLE DB Source [100]] Error: An OLE DB error has occurred. Error code: 0x80040E00.

[OLE DB Source [100]] Error: An OLE DB error has occurred. Error code: 0x80040E00. [DTS.Pipeline] Error: The PrimeOutput method on component "OLE DB Source" (100) returned error code 0xC0202009. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.

[DTS.Pipeline] Error: The PrimeOutput method on component "OLE DB Source" (100) returned error code 0xC0202009. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.

[DTS.Pipeline] Error: Thread "SourceThread0" has exited with error code 0xC0047038.

[DTS.Pipeline] Error: Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.

[DTS.Pipeline] Error: Thread "WorkThread0" has exited with error code 0xC0047039.

Is there anyone who had the same problems ?

By the way the sql command from variable is double checked a thousand times :)

Thanks

Ronny

0x80040E00 is not an SSIS error code. Instead it is most likely coming from the provider so you should see if the AS400 OLEDB provider you are using lists this error and what it means.

Matt

|||

Hello Matt,

This could be , but why was it then working under Sql server 2000 ? Also the ODBC connections in DTS200 were far more ?ntelligent".

regards

Ronny

OLE DB Source parameters ignored when subquery?

I am using an OLE DB Source with SQL command for the data access mode. I defined parameters, then added a complex query with subqueries. It seems like the parameters for a query are not being filled correctly (because I am getting too many rows returned). At one point I saw an error message the said something to the effect that the parameters would be ignored when there was a subquery in the SQL.

Can anyone shed some light on this?

Thanks,

Laurence

I don't know if that is true or not but regardless of that you should stop using parameters as explained here: http://blogs.conchango.com/jamiethomson/archive/2005/12/09/2480.aspx

-Jamie

|||

Jamie,

I have read your blog many times and it is really helpful. Thanks.

In this instance, the query is long and complex, and I might as well go to a script task as a string variable.

I am disappointed that parameters do not seem to be working correctly, thereby making my life more difficult.

Laurence

|||

Yeah, script task is a good option in those circumstances.

N.B. SP1 will have an expression builder attached to variables which will make it easier to build your expressions.

-Jamie

|||Would you mind sharing the long, complex query that didnt work for you?

OLE Db Source and Variables

I created a OLE DB Source, and created a sql command text.

The issue is with parameters, I have to use the '?' to identify.

What if I have multiple variables of the same type scatter around, how can I define that?

Somthing like this would be good...

Declare @.month
set @.month = ?

select blah,@.month where month=@.month

or

How could I use the ADO Syntax and just put @.month?

Thanks,

Mardo

Hi Mardo,

OLE DB only supports '?' style parameters. You can use ADO style named parameters if you define an ADO.NET connection manager. Ensure you set the ConnectionType property of the Exec SQL Task to ADO.NET in order to reference the new connection manager.

Cheers,

Nick

http://nickbarclay.blogspot.com

|||another solution would be to use variables and expressions to dynamically create the sql statement.|||Execuse my ignorance but I only see a OLE DB Data Flow Source. I understand that ADO.NET can used name paramters but I can only seem to use that in Data Flow Destinations.

Marty|||

Guys, my apologies for being a bit misleading.

The .NET connection manager can be used in conjunction with the Execute SQL task (in the control flow) - when using this type of connection manager you are able to use named parameters. The OLE DB source adapter (in the data flow) is specifically OLE DB i.e. it must use an OLE DB connection manager to access the DB. As Duane posted earlier you can also "use variables and expressions to dynamically create the SQL statement", other than that you will have to use "?" as parameter placeholders.

Cheers,

Nick

http://nickbarclay.blogspot.com

OLE DB Source & SQL Command

Hi,

I am trying to set the OLE DB Source Editor. I am using the following option for Data Access mode

SQL Command from a variable and uses my sqlQuery variable.

sqlQuery variable contains a sql statement but I am getting the following error

ox80040E0C, Command text was not set for command object,

Additional information HRESULT 0xC0202009

Does it mean I have to give the variable name containing the stored procedure?

Please Guide

Do you get the error at design-time or runtime?

What value is in @.[User::sqlQuery]?

-Jamie

|||

Ignore this, When I pass the name of stored proc it works fine

Thanks for you help

OLE DB Source - Data Access Mode - SQL Command

I got a package with data flow task. Within the data flow task I have flat file with Fiscal Calendar defined. I got another data source within the data flow task, which is OLE DB Source. I want to use SQL Command as data access mode. SQL similar to one the below is in there.

-

DECLARE @.startdate DATETIME
DECLARE @.enddate DATETIME
DECLARE @.date DATETIME
DECLARE @.id INT

SET @.startdate = '1993-09-26' --Change these to
SET @.enddate = '2010-09-25' --whatever you want
SET @.id = 1
SET @.date = DATEADD(dd, @.id, @.startdate)

WHILE @.date <= @.enddate
BEGIN
select @.date CalendarDate,
DATEPART(dd, @.date) CalendarDayMonth,
DATEPART(dy, @.date) CalendarDayYear,
DATEPART(dw, @.date) CalendarDayWeek,
DATENAME(dw, @.date) CalendarDayName

SET @.id = @.id + 1
SET @.date = DATEADD(dd, @.id, @.startdate)

END

-

This SQL works fine in SSMS and returns around 6000 rows. But when I plug the same SQL in OLE DB Source it returns only the first record. It is not going through the WHILE loop.

Has anyone came across this?

Thanks

Sutha

Hey,

OLEDB Source connection to what database?

Brian

|||

I am connecting to my warehouse DB but the source is just the SQL, it doesn't need to extract anything from DB, as the SQL should give the result set.

What I should ideally use is "Execute SQL Task", which is in Control Flow Task.

Maybe I could achieve this by putting into a temp table and source it from the temp table. I am going to check it out.

Thanks

Sutha

OLE DB Command with property expression

Hi,

I am trying to use an OLE DB Command to run a different SQL command for each row in the data flow. I have a script component that builds the SQL command and puts it in a data flow variable, and a property expression in the data flow mapping [OLE DB Command].[Sql Command] to that variable.

The problem is that the OLE DB Command has a validation error, saying that "the command text was not set for the command object", and it doesn't run.

Did I miss anything? should I tell the OLE DB Command that the SQL command would come from expression? or is it a bug?

Thanks.

This validation error can be ignored if you set DataFlow task's DelayValidation to true. However, if you don't set parameters correctly, you may get parameters not bound error later on.

The way I do this is to set up a "dummy" OLEDBCommand first, with the good parameter binding(s), then during execution, the SqlCommand will be replaced by the my real expression value at runtime time - This will work under the assumption that the column metadata does not change overtime, which means, although the SqlCommand will change at runtime, the parameter bindings will remain the same.

Pls try it out and let me know if you have further questions.

Thanks

Wenyang

|||

Thanks.

Unfortunately, my sql commands differ in metadata. I can set it so that the parameters are in the same order for all commands, but some of the commands will not use all the parameters.

anyway, I tried what you suggested with a specific command, but it still doesn't work. the SQL command is deleted whenever I save the package (before running it), and then I get the same validation error.

Isn't there a straight forward solution? I mean, the package knows I set a property expression, otherwise it wouldn't delete the SQL command upon save. If so, why does it through the error? is it a bug?

|||

. It is by design the column metadata has to be the same for each SqlCommand expression value. This is the case not only for OLE DB Command, but also for other dataflow components when using expressions in similar conditions.

. I tried once again, as long as the expression is set correctly, the SqlCommand's value will be set to the expression evaluation result after saving my pkg(before execution). To me there is no bug here. Which version you are on? Did you set "DelayValidation" to true? Can you try again and make sure you set your expression at DataFlow task's "expression" property properly?

Thanks

Wenyang

|||

Thanks, you helped me find (part of) the problem.

I had the sql command set (using a property expression) to a variable that gets its value only during the data flow execution from a script component. the default value for the variable was empty, and when I saved the package it put the empty value into SqlCommand, which is not a valid value.

Setting DelayValidation to true didn't help here, since at the beginning of the data flow execution the variable is still empty, and I get the same validation error at runtime.

What did help is putting a dummy default value to the variable. now it is running without validation errors.

but...

it doesn't change the property of the OLE DB command :-(

the variable gets a different value for each processed row (I check it with a script), but the OLE DB command still runs the default value assigned to it at the beginning.

any ideas?

|||

Your scenario should work as well. Please provide the SqlServer version you are on and the detailed steps of how you set up the expression for OleDbCommand's SqlCommand property and I'll see how I can help.

Thanks

Wenyang

OLE DB Command with property expression

Hi,

I am trying to use an OLE DB Command to run a different SQL command for each row in the data flow. I have a script component that builds the SQL command and puts it in a data flow variable, and a property expression in the data flow mapping [OLE DB Command].[Sql Command] to that variable.

The problem is that the OLE DB Command has a validation error, saying that "the command text was not set for the command object", and it doesn't run.

Did I miss anything? should I tell the OLE DB Command that the SQL command would come from expression? or is it a bug?

Thanks.

This validation error can be ignored if you set DataFlow task's DelayValidation to true. However, if you don't set parameters correctly, you may get parameters not bound error later on.

The way I do this is to set up a "dummy" OLEDBCommand first, with the good parameter binding(s), then during execution, the SqlCommand will be replaced by the my real expression value at runtime time - This will work under the assumption that the column metadata does not change overtime, which means, although the SqlCommand will change at runtime, the parameter bindings will remain the same.

Pls try it out and let me know if you have further questions.

Thanks

Wenyang

|||

Thanks.

Unfortunately, my sql commands differ in metadata. I can set it so that the parameters are in the same order for all commands, but some of the commands will not use all the parameters.

anyway, I tried what you suggested with a specific command, but it still doesn't work. the SQL command is deleted whenever I save the package (before running it), and then I get the same validation error.

Isn't there a straight forward solution? I mean, the package knows I set a property expression, otherwise it wouldn't delete the SQL command upon save. If so, why does it through the error? is it a bug?

|||

. It is by design the column metadata has to be the same for each SqlCommand expression value. This is the case not only for OLE DB Command, but also for other dataflow components when using expressions in similar conditions.

. I tried once again, as long as the expression is set correctly, the SqlCommand's value will be set to the expression evaluation result after saving my pkg(before execution). To me there is no bug here. Which version you are on? Did you set "DelayValidation" to true? Can you try again and make sure you set your expression at DataFlow task's "expression" property properly?

Thanks

Wenyang

|||

Thanks, you helped me find (part of) the problem.

I had the sql command set (using a property expression) to a variable that gets its value only during the data flow execution from a script component. the default value for the variable was empty, and when I saved the package it put the empty value into SqlCommand, which is not a valid value.

Setting DelayValidation to true didn't help here, since at the beginning of the data flow execution the variable is still empty, and I get the same validation error at runtime.

What did help is putting a dummy default value to the variable. now it is running without validation errors.

but...

it doesn't change the property of the OLE DB command :-(

the variable gets a different value for each processed row (I check it with a script), but the OLE DB command still runs the default value assigned to it at the beginning.

any ideas?

|||

Your scenario should work as well. Please provide the SqlServer version you are on and the detailed steps of how you set up the expression for OleDbCommand's SqlCommand property and I'll see how I can help.

Thanks

Wenyang

OLE DB Command transform and Output columns.

Hi All,

I have an OLE DB transform with a SQL Command of:

sp_get_sponsor_parent ?,? OUTPUT

where sp_get_sponsor_parent is defined like:

CREATE PROCEDURE [dbo].[sp_get_sponsor_parent]

@.pEID int,

@.results int OUTPUT

AS

BEGIN

.

.

.

END

I map the columns, refresh & OK out of the component without trouble, but on executing the package it fails during validation on this component. I'm utterly stumped.

Any light shed would be greatly appreciated.

Many thanks in advance,

Tamim.

I don't see any 'EXEC' in your sql commnad...may this be the problem?|||

This wasn't the problem Rafael - the EXEC is optional.

I resolved the issue however, by trialing just this one thing - outputting to a (derived) column within OLE DB Command - in a new/clean package. To that end I would like to bring the following example to everyone's attention: it's concise, comprehensive and clear, and thus can be considered canonical. No doubt there are other such examples out there, but this particular one helped me to push forwards, and thus deserves the publicity:

http://wiki.sqlis.com/default.aspx/SQLISWiki/OLEDBCommandTransformationAndIdentityColumns.html?diff=y

Thanks very much for your input Rafael.

Cheers,

Tamim.

OLE DB Command Transform

Hi All ,

I am creating packages from a template package whicg I have built ,I have managed to implement basically everything sucessfully .Setting Properties on all the different tasks ,Connections etc except for the OLE DB Command transform.

I have not been sucessfull in getting to the properties or collections which allows me to do the mapping of the Command to parameters (Command Below),I am aware that the command executes for every row . I really need help with how to now do the mapping between the columns and the Paramaters programmatically in c#

I have set the sql command properties of the OLE DB Command Transform,as below

//Setting Update Comand ComponentProperties

IDTSComponentMetaData90 oledbCMDUpdate = dataflow.ComponentMetaDataCollection[0];

oledbCMDUpdate.Name = "name" ;

oledbCMDUpdate.RuntimeConnectionCollection[0].ConnectionManagerID = pack.Connections[0].ID;

CManagedComponentWrapper instanceCMD = oledbCMDUpdate.Instantiate();

instanceCMD.SetComponentProperty("SqlCommand", GetUpdateSQL(tablename)) ;

The Sql that is returned by the GetUpdateSQL(tablename)) method is below

UPDATE [ADM_AdjustmentAction]
SET AdjustmentActionCode = ?
,AdjustmentActionName = ?
,AdjustmentActionDescription = ?
,AdjustmentActionEFD = ?
,AdjustmentActionETD = ?
,UserID = ?
,ProcessDatetime = ?
,ModuleID = ?
WHERE AdjustmentActionID = ?

Thanks in Advance

Cedric

[Microsoft follow-up] I've trawled thru MSDN to try and find an answer to this problem but it proved fruitless. Can anyone from MSFT help?

-Jamie

|||

1. After setting up the properties, including SqlCommand, you can call ReinitializeMetadata on the Command transform.

2. If the provider can derive parameter info for the command, the transform will create external columns, one for each parameter, on its input. If the provider cannot derive parameter info, you will need to manually add the external columns.

3. Each external column has a custom property, DBParamInfoFlags, which specifies whether the parameter is in, out or in out. The value of that property corresponds to OLE DB's paraminfo flag.

4. You then map input columns to the external columns. This mapping will establish which column in the data flow buffer corresponds to which parameter.

You can look in the advanced UI for the command transform to see how the external and input columns should be set up. You can also see the DBParamInfoFlags in the UI as well.

|||

Great answer as usual Ted. Thanks.

OLE DB Command Stage: Capturing Rejects

Hello group, I have a question regarding the OLE DB Command Stage. Currently, I am reviewing a Data Flow that runs in production. This Data Flow Inserts to the various dimension tables in our warehouse. For a particular dimension table, the flow is like this:

Read Source records for Product combinations LookUp Product combinations against the current dimProduct table (cached in memory) Rows not found are then subjected to another LookUp on the dimProduct table (not cached). This is to find any rows inserted during the current run Rows not found are then Inserted to dimProduct using a Stored Procedure invoked by an OLE DB Command Successful Inserts then continue on, Rejected Inserts should be captured to a Flat File on our server for review.

Apparently, this last step has never been successful at capturing Rejects. Obviously, we would want to review these records to find the reason for failure. We get an empty file.

Currently, in the Stored Procedure we are using logic like this:

IF @.PRODUCTCOUNT <> 0

BEGIN

RAISERROR ('DUPLICATE PRODUCT!', 10, 1)

RETURN

END

Questions:

Is the RAISERROR command going to give us Output? Can we implement the OUTPUT command in our Proc invocation? I have not found any documentation that says the OLE DB Command Stage supports Error logging (Although columns are available to be added in the Input/Output columns tab?) Should we be using another Stage to accomplish this?

Any thoughts are welcome, thanks for your time!

rg

IF @.PRODUCTCOUNT <> 0

BEGIN

RAISERROR ('DUPLICATE PRODUCT!', 10, 1)

RETURN

END

In my experience, the error disposition on the OLE DB Command does not work. At least I haven't been able to get it to work. It will ignore RAISERROR statements, yet fail the component on a divide by zero, but never redirect a row. Even if the error redirection did work, you wouldn't be able to get the error description you're trying to raise.

Happily, output parameters DO work (which still surprises me since it isn't documented and isn't really intuitive). I recommend you use an output parameter for the error description, assign it to a column in the data flow, and put a Conditional Split right after the OLE DB Command evaluating the column to roll your own error redirection.

|||

Jay, thanks for the tip. The research was leading me in the direction that the error disposition was less than robust. Thanks for the confirmation, I will pursue using OUTPUT parameters for this data flow.

Thanks for the help! I have much more experience with a different ETL toolset, so even the little things right now are a challenge.