OLE-Db connection in Transformation Script Component

Hello,

Using the following documentation as a guide:

http://msdn2.microsoft.com/zh-cn/library/aa337080.aspx

I instantiated a new script component into an existing Data Flow in my SSIS project.

In the Script Transformation Editor, under the Connection Managers section, I associated the name dbConnManager to an already existing Connection Manager in the project.

My Connection Manager is of the type oOLEDB.

I then opened up the script designer and added the following lines of code where it said "Add your code here"

Dim myConnManager As IDTSConnectionManager90 = _

Me.Connections.ECFconnection

Dim dbConn As OleDb.OleDbConnection = _

CType(myConnManager.AcquireConnection(Nothing), OleDb.OleDbConnection)

When I test run the project I get the following error and the new script component is red:

Unable to cast COM object of type 'System.__ComObject' to class type 'System.Data.OleDb.OleDbConnection'. Instances of types that represent COM components cannot be cast to types that do not represent COM components; however they can be cast to interfaces as long as the underlying COM component supports QueryInterface calls for the IID of the interface.

I know the database connection works since I am using it in a component that executes before this new script component.

I am stuck...Any suggestions?

Unfortunately you cannot use a Connection Manager that returns a native type in managed code. This includes OLE DB and Excel.

This limitation is noted in BOL in
http://msdn2.microsoft.com/en-us/library/ms136018.aspx
and
http://msdn2.microsoft.com/en-us/library/aa337080.aspx

with this suggestion:

If you need to call the AcquireConnection method of a connection manager that returns an unmanaged object, use an ADO.NET connection manager. When you configure the ADO.NET connection manager to use an OLE DB provider, it connects by using the .NET Framework Data Provider for OLE DB. In this case, the AcquireConnection method returns a System.Data.OleDb.OleDbConnection instead of an unmanaged object. To configure an ADO.NET connection manager for use with an Excel data source, select the Microsoft OLE DB Provider for Jet, specify an Excel workbook, and then enter Excel 8.0 (for Excel 97 and later) as the value of Extended Properties on the All page of the Connection Manager dialog box.

-Doug

|||

Thank you. That was the nudge in the right direction that I needed.

Greg.

OLE-Db connection in Transformation Script Component

Hello,

Using the following documentation as a guide:

http://msdn2.microsoft.com/zh-cn/library/aa337080.aspx

I instantiated a new script component into an existing Data Flow in my SSIS project.

In the Script Transformation Editor, under the Connection Managers section, I associated the name dbConnManager to an already existing Connection Manager in the project.

My Connection Manager is of the type oOLEDB.

I then opened up the script designer and added the following lines of code where it said "Add your code here"

Dim myConnManager As IDTSConnectionManager90 = _

Me.Connections.ECFconnection

Dim dbConn As OleDb.OleDbConnection = _

CType(myConnManager.AcquireConnection(Nothing), OleDb.OleDbConnection)

When I test run the project I get the following error and the new script component is red:

Unable to cast COM object of type 'System.__ComObject' to class type 'System.Data.OleDb.OleDbConnection'. Instances of types that represent COM components cannot be cast to types that do not represent COM components; however they can be cast to interfaces as long as the underlying COM component supports QueryInterface calls for the IID of the interface.

I know the database connection works since I am using it in a component that executes before this new script component.

I am stuck...Any suggestions?

Unfortunately you cannot use a Connection Manager that returns a native type in managed code. This includes OLE DB and Excel.

This limitation is noted in BOL in
http://msdn2.microsoft.com/en-us/library/ms136018.aspx
and
http://msdn2.microsoft.com/en-us/library/aa337080.aspx

with this suggestion:

If you need to call the AcquireConnection method of a connection manager that returns an unmanaged object, use an ADO.NET connection manager. When you configure the ADO.NET connection manager to use an OLE DB provider, it connects by using the .NET Framework Data Provider for OLE DB. In this case, the AcquireConnection method returns a System.Data.OleDb.OleDbConnection instead of an unmanaged object. To configure an ADO.NET connection manager for use with an Excel data source, select the Microsoft OLE DB Provider for Jet, specify an Excel workbook, and then enter Excel 8.0 (for Excel 97 and later) as the value of Extended Properties on the All page of the Connection Manager dialog box.

-Doug

|||

Thank you. That was the nudge in the right direction that I needed.

Greg.

'OLE DB Destination' failed validation error

Hi,

I'm developing a SSIS package and am coming across this problem:

"Error at myTable [DTS.Pipeline]: component 'OLE DB Destination' (156) failed validation and returned error code 0xC020801C.

Here's the situation with my package. It basically consists of a bunch of tasks to build a database and populate the tables from an Excel file.

The first task runs a SQL file to create all my DB tables, etc. The next set of tasks import data from the Excel file into the tables. All tasks have precedence constraints so that the SQL file will build the DB tables first.

Here's the problem. Everything works perfectly if my database ALREADY exists. The problem comes when the database does not exist. It seems like the SSIS does some sort of schema validation on my dataflow tasks to ensure that everything is ok.

Is there any way to bypass this validation so that my SSIS will run ok without the DB already existing? I know that everything WILL be ok because the ExecuteSQL task will create the structure for me.

I used to work with SQL 2000 DTS, and i never ran into this problem before. Previously, i could run the DTS no problem, however, i just couldn't open the transformations if the DB didn't exist.

Thanks.

Set DelayValidation=True on the Data Flow.

OLE DB Destination Component

when loading the transformed data into OLE DB destination, there is no options to truncate destination table first. Have to insert a middle step to run script to truncate the destination table first.

I'm very confused. We even has the options of keeping or deleting the data in destination table in SQL2000 DTS package. Why we don't have this option in SQL2005?

That's correct. You should use an Execute SQL task in the control flow before the data flow containing the OLE DB Destination.|||

Phil,

is there any component in SSIS package that can let us run flexible SQL Script again the input dataset just like the input dataset is a table?

|||

Jeff_LIU wrote:

Phil,

is there any component in SSIS package that can let us run flexible SQL Script again the input dataset just like the input dataset is a table?

Well, in the control flow, you have the Execute SQL task. In the data flow, you have the OLE DB Command transformation, but beware with that one as it will execute the contained SQL for every row in the input data source.|||

those two components can only use the input dataset as parameters, but can't update the data in input data source.

What I want to know is if we can run SQLScript to directly update the columns in input dataset

|||

Jeff_LIU wrote:

those two components can only use the input dataset as parameters, but can't update the data in input data source.

What I want to know is if we can run SQLScript to directly update the columns in input dataset

No. As I said in your other thread, you can update the columns in the data flow via a lookup transformation and a derived column transformation.

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 & SQL

Does anyone know why SQL doesn't work properly in my OLE DB component?

Underlying DB: DB2

Provider: IBM DB2 UDB for iSeries IBMDA400 OLE DB Provider

If I insert some SQL I get get a preview of the data but it errors when I run it.

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

[DTS.Pipeline] Error: The PrimeOutput method on component "OLE DB Source" (1) 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.

I can set the data access mode to 'table or view/OpenRowset' and that works fine.

:(

Please help....

I also faced the same problem,

Then instead of Oledb Src adapter I configured Data Reader Source.

For the connection manager I created a ADO.Net Connection manager

Create a DSN, used a ADO.Net Provider for ODBC this will create a ADO.Net Connection Manager.

Now you can Configure Data Reader Source...Workaround ...:)

Thanks

Dharmbir

|||

Awesome! That works. Many thanks Dharmbir.

|||

I also used your solution! but you dont have to forget to change the value of the Connect Time out wich standard value ist 0.

|||HI,

I used this option ( ADO.NET - ODBC combination ) but i guess ODBC does not support substring. I need to have substring of a particular column as criteria to extract data from AS400(source) and insert that into the DB2/SQL server(destination) and that's where i'm stuck.

my query looks like this:

"SELECT * FROM itcga.xxxxx where Substr(itgdta.xxxxx.GENLCDE,9,2) = '61'"

Contrast to this if i use the OLEDB source, i get the following error message:

"Error: 0xC0047021 at XXXXXX, DTS.Pipeline: Thread "WorkThread0" has exited with error code 0xC0047039."

Please Help on getting data form as400 with the where substring condition.

Thanks in advance.
Amit S|||

Another possibility is to setup the DB2 database as a linked database in SQL Server 2005, create views on that linked database within a SQL Server 2005 database, and finally create an OLE DB source in Integration Services using the SQL Server 2005 database and linked views.

|||

Amitshah,

ODBC is just a set of API's that connect to the underlying Database.

SISS does not do explicit conversions, so most often "varchar" columns need to be converted.

In the case above, it appears that two explict conversions are being done on the data before it lands in the source DB.

once to ADO.NET and again to ODBC?

I cannot tell what your destination DB is... however, SQL Server DB does not recognize the substr() function. Try Substring()

The Microsoft Whitepaper on the front of the Forum does a great job of explaining these concepts.

|||

This works for me too. But it doesn't solve my problem. If you use Data Reader Source it is not possible to set the select-statement dynamically from a local variable. This is possible with the Oledb Src.

What should I do. Any idea ?

OLE DB & SQL

Does anyone know why SQL doesn't work properly in my OLE DB component?

Underlying DB: DB2

Provider: IBM DB2 UDB for iSeries IBMDA400 OLE DB Provider

If I insert some SQL I get get a preview of the data but it errors when I run it.

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

[DTS.Pipeline] Error: The PrimeOutput method on component "OLE DB Source" (1) 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.

I can set the data access mode to 'table or view/OpenRowset' and that works fine.

:(

Please help....

I also faced the same problem,

Then instead of Oledb Src adapter I configured Data Reader Source.

For the connection manager I created a ADO.Net Connection manager

Create a DSN, used a ADO.Net Provider for ODBC this will create a ADO.Net Connection Manager.

Now you can Configure Data Reader Source...Workaround ...:)

Thanks

Dharmbir

|||

Awesome! That works. Many thanks Dharmbir.

|||

I also used your solution! but you dont have to forget to change the value of the Connect Time out wich standard value ist 0.

|||HI,

I used this option ( ADO.NET - ODBC combination ) but i guess ODBC does not support substring. I need to have substring of a particular column as criteria to extract data from AS400(source) and insert that into the DB2/SQL server(destination) and that's where i'm stuck.

my query looks like this:

"SELECT * FROM itcga.xxxxx where Substr(itgdta.xxxxx.GENLCDE,9,2) = '61'"

Contrast to this if i use the OLEDB source, i get the following error message:

"Error: 0xC0047021 at XXXXXX, DTS.Pipeline: Thread "WorkThread0" has exited with error code 0xC0047039."

Please Help on getting data form as400 with the where substring condition.

Thanks in advance.
Amit S|||

Another possibility is to setup the DB2 database as a linked database in SQL Server 2005, create views on that linked database within a SQL Server 2005 database, and finally create an OLE DB source in Integration Services using the SQL Server 2005 database and linked views.

|||

Amitshah,

ODBC is just a set of API's that connect to the underlying Database.

SISS does not do explicit conversions, so most often "varchar" columns need to be converted.

In the case above, it appears that two explict conversions are being done on the data before it lands in the source DB.

once to ADO.NET and again to ODBC?

I cannot tell what your destination DB is... however, SQL Server DB does not recognize the substr() function. Try Substring()

The Microsoft Whitepaper on the front of the Forum does a great job of explaining these concepts.

|||

This works for me too. But it doesn't solve my problem. If you use Data Reader Source it is not possible to set the select-statement dynamically from a local variable. This is possible with the Oledb Src.

What should I do. Any idea ?