OLEDB VS SQLServer destination

Hi All,

We want to take advantage of the performance benefit provided by SQL server destination in our packages. We are using a configuration variable to specify whether the SQL Server is remote or local to the packages. We are using a conditional split to redirect the process to either SQL Server destination or OLEDB destination based on the value of the variable. Is there any performance benefit in doing such a thing as it seems that the connection is made in both the paths during the runtime instead of in one particular path alone.

Thanks in advance

Kumbs

It may be opening the connection, but the data is sent to the SQL Server Destination, right? So you should get the benefits. If you really don't want to make the second connection, create two data flows, one with the OLEDB Dest., one with the SQL Server Dest.. In the control flow, put an expression of the constraints leading to the data flow to pick which one to execute.

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 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 using table name variable

I have a package-level variable [User::viewName], type = string, containing a view name. I want to setup an OLE DB source to use this variable value as the source, so Data access mode = "Table name or view name variable". The Variable name dropdown contains [User:viewName], so I select it. When I click OK to leave the edit dialog I get the error:

The variable User::viewName is required to be of type "VT_BSTR".

Only variables of type String occur in the Variable name dropdown; if I try changing it to a type other than string it doesn't occur in the dropdown. What is VT_BSTR and how can I change the variable type to it?

I've never seen VT_BSTR in my life, that isn't a data type that I know of. Something is awry here - I wonder if it could be package corruption. Does the same happen when you try a differrent string variable?

-Jamie

|||I'm copying and pasting a package in Solution Explorer, then playing with the copy to see what works, then going back to the original. The package this problem is occurring in is a copy; might that be the problem? This seems like such an ordinary thing to do (get the table or view name from a variable) that I'm really surprised it's happening. Are there known problems with copying packages?|||

I don't know of any - but it does sound as though some corruption has occurred somewhere. Can you share the contents of the .dtsx file? (i.e. open it in notepad and copy the contents to here)?

-Jamie

|||

mruniqueid wrote:

I'm copying and pasting a package in Solution Explorer, then playing with the copy to see what works, then going back to the original. The package this problem is occurring in is a copy; might that be the problem? This seems like such an ordinary thing to do (get the table or view name from a variable) that I'm really surprised it's happening. Are there known problems with copying packages?

Can't think it matters, but be sure you generate a new GUID on the copied package. Control-flow background: right click, properties. Select the drop down in the ID field and generate a new GUID.|||

In my original package Data Flow I had an OLE DB Source with Data access mode = "Table or view". When I try to set Data access mode = "Table name or view name variable" in the original package I run into this trouble.

I tried creating a new package from scratch and realized I've got a knowledge gap. If you create an OLE DB Source and set Data access mode = "Table name or view name variable" right from the start, then how do you define the source output columns since there's no table to derive them from? I have several views with the same structure; in my original package I picked one of these as "Name of the table or the view" which of course defined output columns for the source, then tried to change Data access mode to a variable as described above.

Can I start with Data access mode = variable? If I try that I get the error "A destination table name has not been provided". I created an OLE DB Destination and connected it to the OLE DB Source but the error persists. What is the proper sequence of steps and settings to use a variable name for Data Access Mode in an OLE DB Source?

|||

mruniqueid wrote:

In my original package Data Flow I had an OLE DB Source with Data access mode = "Table or view". When I try to set Data access mode = "Table name or view name variable" in the original package I run into this trouble.

I tried creating a new package from scratch and realized I've got a knowledge gap. If you create an OLE DB Source and set Data access mode = "Table name or view name variable" right from the start, then how do you define the source output columns since there's no table to derive them from?

The name of the table needs to be stored in the variable.

mruniqueid wrote:

I have several views with the same structure; in my original package I picked one of these as "Name of the table or the view" which of course defined output columns for the source, then tried to change Data access mode to a variable as described above.

Can I start with Data access mode = variable? If I try that I get the error "A destination table name has not been provided". I created an OLE DB Destination and connected it to the OLE DB Source but the error persists. What is the proper sequence of steps and settings to use a variable name for Data Access Mode in an OLE DB Source?

1. Create the variable of type string

2. Add the table name into teh variable

3. Create your OLE DB Source and select the variable that you have just chosen.

-Jamie

|||

I didn't realize the variable value was evaluated at design time, since it's set at runtime. Makes sense though - thanks a lot!

- Dana

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

Hi,

Im making a query in my Ole DB Source.

How can I use a variable in my query?

Thank you.

Make your query in an expression on another variable. Then inside that expression you can use whatever variables you desire.

Then, use the "query" variable as the SQL Source for the OLE DB Source.|||

Thank you Phil.

I know how to make a variable, go to properties of that variable and set to evaluate it as an expression.

I'm sorry to ask you this, but can you give me an example of how you fit a query inside an expression?

Thank you as always!!

|||

I tried to put the following inside my variable′s expression builder:

"select * from MyTable r
where r.id= " + (DT_WSTR,50)@.[MyVariable]

However, it doesn′t show up in my Ole DB Source Editor when I set Data Access Mode to "Sql command from variable".

Any ideas?

|||Expression:

"select column1, column2 from table where key = " + (DT_WSTR,50)@.[User::MyKeyVariable] + " and ..........."|||You'll want to use "SQL Command from variable" instead. Also, check the scope of the "query" variable you created. Likely, the scope of it is such that the data flow cannot see it.|||Oh, one other thing... Best practices would be to list out all of the columns you need (and ONLY those columns you need) in your query. That does two things:
1 - Prevents changes to the table from affecting the metadata of the package (column additions, etc...)
2 - Introduces only the data necessary for the data flow and hence reduces the overhead of processing the data.|||

Thank you for all this help!!

My Expression variable is in the scope bu it doesn′t show up in the DropDown list inside Ole DB Source :/

When I evaluate my expression I get:

select * from MyTable r where r.ID = 0

Does it maby matter "when" I set the value of this expression? I just created the expression variable out of the blue (not anywhere in my flow).

Any ideas what could be wrong?

Thank you again!

|||It can't be in scope. Double and triple check.

Any string variable of appropriate scope should show up in the list.|||

Strange my variable pops-up now

Thank you so much!!

|||

One last thing.

Don′t I have to make this expression inside some data flow when the variable that I use inside the expression has a value(else it wil be 0 always)?

Or is the expression evaluated at runtime when I use it in the OLE DB Source?

Thank you thousand times!!

|||The expression will be evaluated at runtime.|||

Dear Phil Brammer,

This worked! I can′t thank you enough!!

I'm really speechless, both for your help and also that this is possable

Yours,

MrHat

OLE DB Destination - table name variable

Hello,

In my Data Flow I have a OLE DB Destination that needs to get the table name to write the data to dynamicaly from a variable I created.

So I select "table name or view name variable" from the Data access mode and select my variable below. So far so good, but when I click "ok" I get the following error message :

Error at Data Flow Task [OLE DB Destination [45]]: A destination table name has not been provided.
Exception from HRESULT: 0xC0202042 (Microsoft.SqlServer.DTSPipelineWrap)

Am I doing something wrong or is there another way to set a variable table name for the OLE DB Destination ?

Using a variable in this way works fine for me on RTM.

I can get that error if I do not supply a valid table table in my variable before trying to use it in the OLE-DB Destination, which in itself makes sense too.|||Thanks ... I forgot to give the variable a valid default value, now it works.

OLE Automation and "global variable"

I have an OLE automation object that I am instantiating inside of a stored
proc using
sp_OACreate 'myObject', @.object OUT.
The stored proc is called multiple times, each time instantiating a new
instance of the object (and then destroying it when it is finished).
Is it possible in SQL Server to create a global variable to refer to the
OLE automation object?
IOW can I instantiate the object outside the proc, assign the @.object
pointer to a global variable and then refererence this global variable
inside my stored proc so I don't have to create the object every time it is
called?
Perhaps a more basic question is: is it possible to create global variables
in T-SQL and if so, how?
Thanks
DaveAs far as I know, OLE Automation objects in SQL Server can only exist inside
the scope where they were created.
There are also no global variables in SQL, but you could create a temporary
(or permanent) table to store values that can be accessed "globally".
I strongly recommend you destroy all objects after use, even those
implicitly created by the methods used. But then again you mentioned that yo
u
already do so. :)
SQL 2005 also supports (and controls) CLR, maybe that can provide you with
more functionality.
ML
http://milambda.blogspot.com/