Showing posts with label ssis. Show all posts
Showing posts with label ssis. Show all posts

Tuesday, March 20, 2012

Connecting to Oracle RDB from SSIS

Hi Gurus,

I am trying to build a Data Warehouse using SSIS in SQL Server 2005. My source data is in Oracle RDB. Now when I am trying to connect to oracle RDB using the OLE DB Source component, I am getting the following error.

Test connection failed because of an error in initializing provider. Oracle client and networking componenets were not found. These components are supplied by Oracle Corporation and part of the Oracle Version 7.3.3 or late client software installtion.

Provider is unable to funtion until these components are installed.

I did install the Oracle RDB client software .Apparently it looks like Microsoft OLE DB for Oracle supports Oracle 7.3.3 and above. At the same time I tried to use ODBC for Oracle RDB driver from ODBC data sources. But the OLE DB Source Component in SSIS can't recognize ODBC. So now I am stuck on how to load the data from Oracle RDB to SQL Server 2005 staging area.

Any thoughts on this would be really appreciated. Ideally we are looking to port the data directly from Oracle RDB to SQL Server without any intermediate flat files (flat files etc)

Thanks,
SK

sk3636 wrote:

Hi Gurus,

I am trying to build a Data Warehouse using SSIS in SQL Server 2005. My source data is in Oracle RDB. Now when I am trying to connect to oracle RDB using the OLE DB Source component, I am getting the following error.

Test connection failed because of an error in initializing provider. Oracle client and networking componenets were not found. These components are supplied by Oracle Corporation and part of the Oracle Version 7.3.3 or late client software installtion.

Provider is unable to funtion until these components are installed.

I did install the Oracle RDB client software .Apparently it looks like Microsoft OLE DB for Oracle supports Oracle 7.3.3 and above. At the same time I tried to use ODBC for Oracle RDB driver from ODBC data sources. But the OLE DB Source Component in SSIS can't recognize ODBC. So now I am stuck on how to load the data from Oracle RDB to SQL Server 2005 staging area.

Any thoughts on this would be really appreciated. Ideally we are looking to port the data directly from Oracle RDB to SQL Server without any intermediate flat files (flat files etc)

Thanks,
SK

I think you should use the ADO.Net connection manager to connect to ODBC sources.

-Jamie

|||

There is an existing post:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=640502&SiteID=1

which could help you out.
Overall, first, you need to install Oracle client. Second, you need to configure tnsnames.ora file.

Regards,
Yitzhak

|||

Hi,

I was able to make a connection to Oracle RDB using ADO.net. But when I used the ADO.net connection, I am getting the following error for one of the columns in Data Source Reader component.

The phone data type for the external column and Output column is Unicode string [DT_WSTR] and SSIS is not letting me change the data type on the output column. The phone column is defined as varchar in SQL Server 2005.

Error: 0xC020902A at Data Flow Task, DataReader Source [1736]: The "component "DataReader Source" (1736)" failed because truncation occurred, and the truncation row disposition on "output column "PHONE" (2469)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.

Error: 0xC02090F5 at Data Flow Task, DataReader Source [1736]: The component "DataReader Source" (1736) was unable to process the data.

Error: 0xC0047038 at Data Flow Task, DTS.Pipeline: The PrimeOutput method on component "DataReader Source" (1736) returned error code 0xC02090F5. 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.

Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: Thread "SourceThread0" has exited with error code 0xC0047038.

Error: 0xC0047039 at Data Flow Task, DTS.Pipeline: 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.

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

Thanks,
SK

|||

sk3636 wrote:

Hi,

I was able to make a connection to Oracle RDB using ADO.net. But when I used the ADO.net connection, I am getting the following error for one of the columns in Data Source Reader component.

The phone data type for the external column and Output column is Unicode string [DT_WSTR] and SSIS is not letting me change the data type on the output column. The phone column is defined as varchar in SQL Server 2005.

Error: 0xC020902A at Data Flow Task, DataReader Source [1736]: The "component "DataReader Source" (1736)" failed because truncation occurred, and the truncation row disposition on "output column "PHONE" (2469)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.

Error: 0xC02090F5 at Data Flow Task, DataReader Source [1736]: The component "DataReader Source" (1736) was unable to process the data.

Error: 0xC0047038 at Data Flow Task, DTS.Pipeline: The PrimeOutput method on component "DataReader Source" (1736) returned error code 0xC02090F5. 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.

Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: Thread "SourceThread0" has exited with error code 0xC0047038.

Error: 0xC0047039 at Data Flow Task, DTS.Pipeline: 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.

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

Thanks,
SK

So when you said "SSIS is not letting me change the data type on the output column" that's not actually true is it? It HAS let you change it and at execution-time you are now getting these errors.

The error is pretty self explanatory. You are trying to push a value into a column in the pipeline that is not wide enough for it. Hence, you should widen the column.

-Jamie

|||

Hi Jamie,

It HAS let you change it and at execution-time you are now getting these errors.

Not really. I just changed the property in the output column properties and when I say OK, it throws the following error.

Error at Data Flow Task [DataReader Source [4188]]: The data type of output columns on the component "DataReader Source" (4188) cannot be changed.

Error at Data Flow Task [DataReader Source [4188]]: System.Runtime.InteropServices.COMException (0xC020837D)
at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.SetOutputColumnDataTypeProperties(Int32 iOutputID, Int32 iOutputColumnID, DataType eDataType, Int32 iLength, Int32 iPrecision, Int32 iScale, Int32 iCodePage)
at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostSetOutputColumnDataTypeProperties(IDTSManagedComponentWrapper90 wrapper, Int32 iOutputID, Int32 iOutputColumnID, DataType eDataType, Int32 iLength, Int32 iPrecision, Int32 iScale, Int32 iCodePage)

Thanks,
SK

|||

sk3636 wrote:

Hi Jamie,

It HAS let you change it and at execution-time you are now getting these errors.

Not really. I just changed the property in the output column properties and when I say OK, it throws the following error.

Error at Data Flow Task [DataReader Source [4188]]: The data type of output columns on the component "DataReader Source" (4188) cannot be changed.

Error at Data Flow Task [DataReader Source [4188]]: System.Runtime.InteropServices.COMException (0xC020837D)
at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.SetOutputColumnDataTypeProperties(Int32 iOutputID, Int32 iOutputColumnID, DataType eDataType, Int32 iLength, Int32 iPrecision, Int32 iScale, Int32 iCodePage)
at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostSetOutputColumnDataTypeProperties(IDTSManagedComponentWrapper90 wrapper, Int32 iOutputID, Int32 iOutputColumnID, DataType eDataType, Int32 iLength, Int32 iPrecision, Int32 iScale, Int32 iCodePage)

Thanks,
SK

Oh OK, sorry. In that case you will have to use a Data Conversion component or Derived Column component to convert to the datatype that you wish to convert to.

-Jamie

|||

I did try to use the Data conversion transformation and I got the same error. The error is coming at the output column from the data source reader. So the data flow component didn't traverse upto Data Conversion component to check whether the conversion is taking place or not.

I found similiar threads related to the same issue that I am facing.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=392224&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=278744&SiteID=1

SK

|||Have you or anyone else found a resolution for this? I'm experiencing the same problem.

Connecting to Oracle RDB from SSIS

Hi Gurus,

I am trying to build a Data Warehouse using SSIS in SQL Server 2005. My source data is in Oracle RDB. Now when I am trying to connect to oracle RDB using the OLE DB Source component, I am getting the following error.

Test connection failed because of an error in initializing provider. Oracle client and networking componenets were not found. These components are supplied by Oracle Corporation and part of the Oracle Version 7.3.3 or late client software installtion.

Provider is unable to funtion until these components are installed.

I did install the Oracle RDB client software .Apparently it looks like Microsoft OLE DB for Oracle supports Oracle 7.3.3 and above. At the same time I tried to use ODBC for Oracle RDB driver from ODBC data sources. But the OLE DB Source Component in SSIS can't recognize ODBC. So now I am stuck on how to load the data from Oracle RDB to SQL Server 2005 staging area.

Any thoughts on this would be really appreciated. Ideally we are looking to port the data directly from Oracle RDB to SQL Server without any intermediate flat files (flat files etc)

Thanks,
SK

sk3636 wrote:

Hi Gurus,

I am trying to build a Data Warehouse using SSIS in SQL Server 2005. My source data is in Oracle RDB. Now when I am trying to connect to oracle RDB using the OLE DB Source component, I am getting the following error.

Test connection failed because of an error in initializing provider. Oracle client and networking componenets were not found. These components are supplied by Oracle Corporation and part of the Oracle Version 7.3.3 or late client software installtion.

Provider is unable to funtion until these components are installed.

I did install the Oracle RDB client software .Apparently it looks like Microsoft OLE DB for Oracle supports Oracle 7.3.3 and above. At the same time I tried to use ODBC for Oracle RDB driver from ODBC data sources. But the OLE DB Source Component in SSIS can't recognize ODBC. So now I am stuck on how to load the data from Oracle RDB to SQL Server 2005 staging area.

Any thoughts on this would be really appreciated. Ideally we are looking to port the data directly from Oracle RDB to SQL Server without any intermediate flat files (flat files etc)

Thanks,
SK

I think you should use the ADO.Net connection manager to connect to ODBC sources.

-Jamie

|||

There is an existing post:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=640502&SiteID=1

which could help you out.
Overall, first, you need to install Oracle client. Second, you need to configure tnsnames.ora file.

Regards,
Yitzhak

|||

Hi,

I was able to make a connection to Oracle RDB using ADO.net. But when I used the ADO.net connection, I am getting the following error for one of the columns in Data Source Reader component.

The phone data type for the external column and Output column is Unicode string [DT_WSTR] and SSIS is not letting me change the data type on the output column. The phone column is defined as varchar in SQL Server 2005.

Error: 0xC020902A at Data Flow Task, DataReader Source [1736]: The "component "DataReader Source" (1736)" failed because truncation occurred, and the truncation row disposition on "output column "PHONE" (2469)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.

Error: 0xC02090F5 at Data Flow Task, DataReader Source [1736]: The component "DataReader Source" (1736) was unable to process the data.

Error: 0xC0047038 at Data Flow Task, DTS.Pipeline: The PrimeOutput method on component "DataReader Source" (1736) returned error code 0xC02090F5. 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.

Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: Thread "SourceThread0" has exited with error code 0xC0047038.

Error: 0xC0047039 at Data Flow Task, DTS.Pipeline: 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.

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

Thanks,
SK

|||

sk3636 wrote:

Hi,

I was able to make a connection to Oracle RDB using ADO.net. But when I used the ADO.net connection, I am getting the following error for one of the columns in Data Source Reader component.

The phone data type for the external column and Output column is Unicode string [DT_WSTR] and SSIS is not letting me change the data type on the output column. The phone column is defined as varchar in SQL Server 2005.

Error: 0xC020902A at Data Flow Task, DataReader Source [1736]: The "component "DataReader Source" (1736)" failed because truncation occurred, and the truncation row disposition on "output column "PHONE" (2469)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.

Error: 0xC02090F5 at Data Flow Task, DataReader Source [1736]: The component "DataReader Source" (1736) was unable to process the data.

Error: 0xC0047038 at Data Flow Task, DTS.Pipeline: The PrimeOutput method on component "DataReader Source" (1736) returned error code 0xC02090F5. 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.

Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: Thread "SourceThread0" has exited with error code 0xC0047038.

Error: 0xC0047039 at Data Flow Task, DTS.Pipeline: 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.

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

Thanks,
SK

So when you said "SSIS is not letting me change the data type on the output column" that's not actually true is it? It HAS let you change it and at execution-time you are now getting these errors.

The error is pretty self explanatory. You are trying to push a value into a column in the pipeline that is not wide enough for it. Hence, you should widen the column.

-Jamie

|||

Hi Jamie,

It HAS let you change it and at execution-time you are now getting these errors.

Not really. I just changed the property in the output column properties and when I say OK, it throws the following error.

Error at Data Flow Task [DataReader Source [4188]]: The data type of output columns on the component "DataReader Source" (4188) cannot be changed.

Error at Data Flow Task [DataReader Source [4188]]: System.Runtime.InteropServices.COMException (0xC020837D)
at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.SetOutputColumnDataTypeProperties(Int32 iOutputID, Int32 iOutputColumnID, DataType eDataType, Int32 iLength, Int32 iPrecision, Int32 iScale, Int32 iCodePage)
at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostSetOutputColumnDataTypeProperties(IDTSManagedComponentWrapper90 wrapper, Int32 iOutputID, Int32 iOutputColumnID, DataType eDataType, Int32 iLength, Int32 iPrecision, Int32 iScale, Int32 iCodePage)

Thanks,
SK

|||

sk3636 wrote:

Hi Jamie,

It HAS let you change it and at execution-time you are now getting these errors.

Not really. I just changed the property in the output column properties and when I say OK, it throws the following error.

Error at Data Flow Task [DataReader Source [4188]]: The data type of output columns on the component "DataReader Source" (4188) cannot be changed.

Error at Data Flow Task [DataReader Source [4188]]: System.Runtime.InteropServices.COMException (0xC020837D)
at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.SetOutputColumnDataTypeProperties(Int32 iOutputID, Int32 iOutputColumnID, DataType eDataType, Int32 iLength, Int32 iPrecision, Int32 iScale, Int32 iCodePage)
at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostSetOutputColumnDataTypeProperties(IDTSManagedComponentWrapper90 wrapper, Int32 iOutputID, Int32 iOutputColumnID, DataType eDataType, Int32 iLength, Int32 iPrecision, Int32 iScale, Int32 iCodePage)

Thanks,
SK

Oh OK, sorry. In that case you will have to use a Data Conversion component or Derived Column component to convert to the datatype that you wish to convert to.

-Jamie

|||

I did try to use the Data conversion transformation and I got the same error. The error is coming at the output column from the data source reader. So the data flow component didn't traverse upto Data Conversion component to check whether the conversion is taking place or not.

I found similiar threads related to the same issue that I am facing.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=392224&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=278744&SiteID=1

SK

|||Have you or anyone else found a resolution for this? I'm experiencing the same problem.

Connecting to Oracle DB using SSIS

Can anybody point me how to connect to an Oracle DB using SSIS? I use the ole DB Connection Manager and pick the Microsoft OLE DB Provider for Oracle but it doesn't seem to work. Any suggestions are greatly apprecaited.

Thanks.

Sam.

Why doesn't it "seem to work"? What errors are you getting? What isn't happening? What IS happening?

Have you got Oracle client installed? Have you configured OLE DB Driver with the name of the TNS entry?

-Jamie

|||

This is the error msg I get.

Test Connection failed because of an error in initializing provider. ORA-12514: TNS listener does not currently know of service requested in connect descriptor.

I have Oracle client installed.

How do I configure OLE DB Driver with the name of the TNS entry? I am new to Oracle...so please bear with me..

Thanks a lot..

|||

First of all you need to verify that you can connect to oracle using the configured driver before you try to do it in SSIS.

There is a file on your client called tnsnames.ora. That file should contain what is called a TNS entry that contains all teh connection details for your Oracle server. Your Oracle DBA will give you the TNS entry.

Verify that you can connect and then connect the name of the TNS entry (it usually ends in ".world") into the OLE DB Driver config UI.

-Jamie

|||

Hi Jamie,

Thanks for a quick reply. I have an entry in the tnsnames.ora file for the connection details. So I checked to see if I can connect with the host string in the tnsnames.ora along with the user name/password. To do that I used Oracle SQL* PLUS. I am able to connect.

I couldn't understand the second step...

"then connect the name of the TNS entry (it usually ends in ".world") into the OLE DB Driver config UI." Can you please explain in more detail?

Thanks a lot...I really appreciate your help.

|||

When you build the SSIS Connection Manager using the microsoft OLE DB Provider for Oracle you will see a box labelled Server Name. Copy the name of the tns entry into that box.

-Jamie

|||

I got it Jamie. I was having two locations for tnsnames.ora and that was the reason for the error...

I appreciate your help.

Thanks.

|||

Hi Jamie,

I have the same problem now in production environment. I have to connect to oracle db from production too and I downloaded the client tools on my 64 bit windows server 2003. I edited the tnsnames to have an entry. When I try to connect to oracle, it gives me an error saying client and networking tools not installed, but I did install them. Can you please help me?

Thanks.

Sam.

Connecting to Oracle DB using SSIS

Can anybody point me how to connect to an Oracle DB using SSIS? I use the ole DB Connection Manager and pick the Microsoft OLE DB Provider for Oracle but it doesn't seem to work. Any suggestions are greatly apprecaited.

Thanks.

Sam.

Why doesn't it "seem to work"? What errors are you getting? What isn't happening? What IS happening?

Have you got Oracle client installed? Have you configured OLE DB Driver with the name of the TNS entry?

-Jamie

|||

This is the error msg I get.

Test Connection failed because of an error in initializing provider. ORA-12514: TNS listener does not currently know of service requested in connect descriptor.

I have Oracle client installed.

How do I configure OLE DB Driver with the name of the TNS entry? I am new to Oracle...so please bear with me..

Thanks a lot..

|||

First of all you need to verify that you can connect to oracle using the configured driver before you try to do it in SSIS.

There is a file on your client called tnsnames.ora. That file should contain what is called a TNS entry that contains all teh connection details for your Oracle server. Your Oracle DBA will give you the TNS entry.

Verify that you can connect and then connect the name of the TNS entry (it usually ends in ".world") into the OLE DB Driver config UI.

-Jamie

|||

Hi Jamie,

Thanks for a quick reply. I have an entry in the tnsnames.ora file for the connection details. So I checked to see if I can connect with the host string in the tnsnames.ora along with the user name/password. To do that I used Oracle SQL* PLUS. I am able to connect.

I couldn't understand the second step...

"then connect the name of the TNS entry (it usually ends in ".world") into the OLE DB Driver config UI." Can you please explain in more detail?

Thanks a lot...I really appreciate your help.

|||

When you build the SSIS Connection Manager using the microsoft OLE DB Provider for Oracle you will see a box labelled Server Name. Copy the name of the tns entry into that box.

-Jamie

|||

I got it Jamie. I was having two locations for tnsnames.ora and that was the reason for the error...

I appreciate your help.

Thanks.

|||

Hi Jamie,

I have the same problem now in production environment. I have to connect to oracle db from production too and I downloaded the client tools on my 64 bit windows server 2003. I edited the tnsnames to have an entry. When I try to connect to oracle, it gives me an error saying client and networking tools not installed, but I did install them. Can you please help me?

Thanks.

Sam.

Connecting to Oracle 9i Server using ODBC from an SSIS package

I am trying to connect to a Oracle 9i Server to execute a sql task, but the connection when tested seems to fail with the following error from the Integration Services Project:

Test connection failed because of an error in initializing provider. ERROR [NA000][Microsoft][ODBC driver for Oracle][Oracle]ORA-12541: TNS:no listener

ERROR[IM006][Microsoft ODBC Driver Manager] Driver's SQLSetConnectAttr failed

ERROR[0100][Microsoft ODBC Driver Manager] The driver does not support the version of ODBC behavior that the application requested (see SQLSetEnvAttr).

The connection was configured and tested from Oracle's SQLPlus session. Please note that the port that the TNS Server is listening is not the default. Is there somewhere I can specify the port in the SSIS connection manager to resolve the issue.

I have tried OLEDB connection and that does not work either.

Any inputs will be appreciated.

Thanks,

Monisha

I use the ODBC Connection in my jobs connecting to Oracle without a problem. Generally this message occurs because the Oracle listener is down. You don't have to specify the port in the SSIS connection manager.|||

I have the DSN created using Microsoft ODBC Driver for Oracle and from the same box I can connect the Oracle server from a SqlPlus session, but when I drag an execute sql task in a SSIS package and create a ODBC connection using system datasource name. I specify the username and password. When I hit the test connection button I get the error that I mentioned earlier.

Test connection failed because of an error in initializing provider. ERROR [NA000][Microsoft][ODBC driver for Oracle][Oracle]ORA-12541: TNS:no listener

ERROR[IM006][Microsoft ODBC Driver Manager] Driver's SQLSetConnectAttr failed

ERROR[0100][Microsoft ODBC Driver Manager] The driver does not support the version of ODBC behavior that the application requested (see SQLSetEnvAttr).

|||Hi,

Im runnign into the same problem. MY DSN works fine when i test in the Data Access tool while creating it. When i use it with in my activex script task it throws an error when i execute a SQL the same error. Did you find a solution for this. Please suggest. Im trying to connect from my DTS Package.

thank you,
Raja

Connecting to Oracle 9i Server using ODBC from an SSIS package

I am trying to connect to a Oracle 9i Server to execute a sql task, but the connection when tested seems to fail with the following error from the Integration Services Project:

Test connection failed because of an error in initializing provider. ERROR [NA000][Microsoft][ODBC driver for Oracle][Oracle]ORA-12541: TNS:no listener

ERROR[IM006][Microsoft ODBC Driver Manager] Driver's SQLSetConnectAttr failed

ERROR[0100][Microsoft ODBC Driver Manager] The driver does not support the version of ODBC behavior that the application requested (see SQLSetEnvAttr).

The connection was configured and tested from Oracle's SQLPlus session. Please note that the port that the TNS Server is listening is not the default. Is there somewhere I can specify the port in the SSIS connection manager to resolve the issue.

I have tried OLEDB connection and that does not work either.

Any inputs will be appreciated.

Thanks,

Monisha

I use the ODBC Connection in my jobs connecting to Oracle without a problem. Generally this message occurs because the Oracle listener is down. You don't have to specify the port in the SSIS connection manager.|||

I have the DSN created using Microsoft ODBC Driver for Oracle and from the same box I can connect the Oracle server from a SqlPlus session, but when I drag an execute sql task in a SSIS package and create a ODBC connection using system datasource name. I specify the username and password. When I hit the test connection button I get the error that I mentioned earlier.

Test connection failed because of an error in initializing provider. ERROR [NA000][Microsoft][ODBC driver for Oracle][Oracle]ORA-12541: TNS:no listener

ERROR[IM006][Microsoft ODBC Driver Manager] Driver's SQLSetConnectAttr failed

ERROR[0100][Microsoft ODBC Driver Manager] The driver does not support the version of ODBC behavior that the application requested (see SQLSetEnvAttr).

|||Hi,

Im runnign into the same problem. MY DSN works fine when i test in the Data Access tool while creating it. When i use it with in my activex script task it throws an error when i execute a SQL the same error. Did you find a solution for this. Please suggest. Im trying to connect from my DTS Package.

thank you,
Raja

Monday, March 19, 2012

Connecting to multiple SQLserver

Hello,

I am working on to design SSIS package which need to run scripts against multiple sqlserver and need help.

Basically I need to connect several SQLserver machine to get a data and insert to our central respository.

Any help or tip appreciated.

Thanks

--

Farhan

Farhan H Soomro wrote:

Hello,

I am working on to design SSIS package which need to run scripts against multiple sqlserver and need help.

Basically I need to connect several SQLserver machine to get a data and insert to our central respository.

Any help or tip appreciated.

Thanks

--

Farhan

Use multiple connection managers.

-Jamie

|||Is there any way I can read from text file salserver name and connect to particular server and run the scripts?|||

Farhan H Soomro wrote:

Is there any way I can read from text file salserver name and connect to particular server and run the scripts?

Yes. SSIS Configurations are provided to help you do exactly this.

Creating Package Configurations
(http://msdn2.microsoft.com/en-us/library/ms141132.aspx)

-Jamie

|||

If you have a list of servers that you want to run some SQL against, you can create two packages.

The simplest way to do this is create a SQL Server configuration table.
In the first package create a ForEach loop that processes each server in a resultset (I'd stick the list of servers in a SQL table so you can use it in reports).
In the ForEach loop
execute a SQL task to update the connection for the second package (the source server connection).
Then execute the second Package

In the Second package just create two connections, the source server to run the scripts against and the destination server to put the results in. Just use a data flow task to move the data.

|||

Thanks for the help. Just wondering how to setup SQL task to update the connection for the second package ?

TIA

--

Farhan

Sunday, March 11, 2012

Connecting to IBM DB2 USING SSIS

We need to connect to an DB2 server using ODBC provider, so we tried with the folowing:

Driver={IBM DB2 ODBC DRIVER};database=FirstDB; hostname=IP address; port=50000;protocol=TCPIP;

But when we try to connect using the above connection string we get teh following error:

"Test Connection failed because of an error in initializing provider. ERROR [IM004][Microsoft][ODBC Driver Manager] Driver's SQLAllocHandle on SQL_HANDLE_ENV failed"

we are not getting wat is the problem. we are able to connect to this DB2 database from the same DB2 server but we are not able to connect to the server from a remote machine.

Thanks in advance.

What source did you use?

If you set up an ODBC entry in Windows, you can use a DataReader Source to go against that ODBC entry.

|||

Do you mean Provider, we used Odbc Data Provider

When we try create an ODBC entry it is throwing the same error.

|||

Murugan S wrote:

Do you mean Provider, we used Odbc Data Provider

When we try create an ODBC entry it is throwing the same error.

Then this doesn't sound like an SSIS problem. Perhaps the driver isn't installed correctly or is corrupt. Have you tried reinstalling the DB2 client?

|||

I have been trying different connection methods for some time trying to make SSIS "play nicely" with DB2 and unfortunately have not found a solution yet.

If someone out there has had success in doing so it would be very much appreciated if you could share the methodology and specific components you used to successfully retrieve data.

We're still using DTS on 2000 to export our IBM related data to SQL.

TR

|||

What type of connection are you using in DTS?

|||

in DTS I'm simply using an "Other Connection" and specifying the DSN that is connecting using the IBM Client Access Driver.

tr

Connecting to IBM DB2 USING SSIS

We need to connect to an DB2 server using ODBC provider, so we tried with the folowing:

Driver={IBM DB2 ODBC DRIVER};database=FirstDB; hostname=IP address; port=50000;protocol=TCPIP;

But when we try to connect using the above connection string we get teh following error:

"Test Connection failed because of an error in initializing provider. ERROR [IM004][Microsoft][ODBC Driver Manager] Driver's SQLAllocHandle on SQL_HANDLE_ENV failed"

we are not getting wat is the problem. we are able to connect to this DB2 database from the same DB2 server but we are not able to connect to the server from a remote machine.

Thanks in advance.

What source did you use?

If you set up an ODBC entry in Windows, you can use a DataReader Source to go against that ODBC entry.

|||

Do you mean Provider, we used Odbc Data Provider

When we try create an ODBC entry it is throwing the same error.

|||

Murugan S wrote:

Do you mean Provider, we used Odbc Data Provider

When we try create an ODBC entry it is throwing the same error.

Then this doesn't sound like an SSIS problem. Perhaps the driver isn't installed correctly or is corrupt. Have you tried reinstalling the DB2 client?

|||

I have been trying different connection methods for some time trying to make SSIS "play nicely" with DB2 and unfortunately have not found a solution yet.

If someone out there has had success in doing so it would be very much appreciated if you could share the methodology and specific components you used to successfully retrieve data.

We're still using DTS on 2000 to export our IBM related data to SQL.

TR

|||

What type of connection are you using in DTS?

|||

in DTS I'm simply using an "Other Connection" and specifying the DSN that is connecting using the IBM Client Access Driver.

tr

connecting to dbf

Hi,

I am having trouble connecting to a dbf file inside an SSIS package. How do I connect to a dbf file in SSIS.

Thanks

Brian

Check out the discussion @. http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=228933&SiteID=1

Thanks,
Loonysan

Saturday, February 25, 2012

Connecting through ODBC

Did anyone try fetching data from any database which is connected through ODBC. If so can you please share the details. In the data flow task of SSIS there is a facility for only oledb and not thru ODBC.

From what I understand, ODBC was not implemented by MS in SSIS.

See thread here

|||An ODBC Destination was not implemented due to time constraints, but you can certainly read from a database via ODBC.
You need to use the ODBC Provider which is a member of the ADO.Net connection types. You then need to use the DataReader source to query your database.
Larry Pope
|||ODBC works as a source but not a destination.

Tuesday, February 14, 2012

Connect to SSIS Service on machine "Servername" failed: Error loading type library/DLL

Got above error on clustered sql2k5 x86 when connect to SSIS, any solution?

Check the article on "How to: Configure Integration Services on a Cluster" @. http://msdn2.microsoft.com/en-us/library/ms345193.aspx

Thanks,
Loonysan

|||Thanks for the info, but it's not related to my problem. SSIS installation is fine, the issue is I got above error when register SSIS in SSMS on active node. I can register same SSIS remotely on passive node without error.

Connect to SSIS Service on machine "Servername" failed: Error loading type library

Hi,

Anyone who can tell me why I get this error !

I can connect to Integration services on the server from another client.

Pls help

//T

I have the same problem from one server but it's working fine from another server.

Scenario:
Our developer have set up a SQL 2005 x32 with Integration Services. Everything works fine.

I set up the production server (x64) clustered and without SSIS. Later on I installed SSIS and configured it according to the guide for failover clustering. From the "old" x32 dev sever we can connect to both SSIS instances but not from the "new" prod server. We get the same error as in the initial post.

Ideas anyone?

|||

I'm getting the same problem and they only solution I've found is to add the user to the local admin group of hte remote server which is obviously not the desired approach.

Did you every manage to resolve this?

thanks

|||

TSZ wrote:

Hi,

Anyone who can tell me why I get this error !

I can connect to Integration services on the server from another client.

Pls help

//T

Is it timing out? It may be a firewall issue, where you would have to have port 135 open between your server and you. Also, you'll want to make sure that your user is in a trusted domain to SSIS.

Brian

|||

Per J wrote:

I have the same problem from one server but it's working fine from another server.

Scenario:
Our developer have set up a SQL 2005 x32 with Integration Services. Everything works fine.

I set up the production server (x64) clustered and without SSIS. Later on I installed SSIS and configured it according to the guide for failover clustering. From the "old" x32 dev sever we can connect to both SSIS instances but not from the "new" prod server. We get the same error as in the initial post.

Ideas anyone?

I know it's a bit OT, but I have the same scenario, but was not able to install SSIS on a failover cluster.
Can you point me to the "Guide" that you used for installation?

Thanks|||

Re: Clustering SSIS, there's a great whitepaper on TechNet or I have a video that can be seen here on how to cluster SSIS (about 10 mins in length): http://www.jumpstarttv.com/Media.aspx?vid=36

Brian

|||I ran into the exact same problem. I found, however, that its not really a SSIS issue, but actually a problem with a DLL, or a registry value from the client tools that was either erased or overwritten with a different version. With that in mind I removed the client tools and reinstalled them and that solved the problem

Connect to SSIS Service on machine "Servername" failed: Error loading type library

Got above error on clustered sql2k5 x86 when connect to SSIS, any solution?

Check the article on "How to: Configure Integration Services on a Cluster" @. http://msdn2.microsoft.com/en-us/library/ms345193.aspx

Thanks,
Loonysan

|||Thanks for the info, but it's not related to my problem. SSIS installation is fine, the issue is I got above error when register SSIS in SSMS on active node. I can register same SSIS remotely on passive node without error.

Connect to SSIS Service on machine "Servername" failed: Error loading type library

Hi,

Anyone who can tell me why I get this error !

I can connect to Integration services on the server from another client.

Pls help

//T

I have the same problem from one server but it's working fine from another server.

Scenario:
Our developer have set up a SQL 2005 x32 with Integration Services. Everything works fine.

I set up the production server (x64) clustered and without SSIS. Later on I installed SSIS and configured it according to the guide for failover clustering. From the "old" x32 dev sever we can connect to both SSIS instances but not from the "new" prod server. We get the same error as in the initial post.

Ideas anyone?

|||

I'm getting the same problem and they only solution I've found is to add the user to the local admin group of hte remote server which is obviously not the desired approach.

Did you every manage to resolve this?

thanks

|||

TSZ wrote:

Hi,

Anyone who can tell me why I get this error !

I can connect to Integration services on the server from another client.

Pls help

//T

Is it timing out? It may be a firewall issue, where you would have to have port 135 open between your server and you. Also, you'll want to make sure that your user is in a trusted domain to SSIS.

Brian

|||

Per J wrote:

I have the same problem from one server but it's working fine from another server.

Scenario:
Our developer have set up a SQL 2005 x32 with Integration Services. Everything works fine.

I set up the production server (x64) clustered and without SSIS. Later on I installed SSIS and configured it according to the guide for failover clustering. From the "old" x32 dev sever we can connect to both SSIS instances but not from the "new" prod server. We get the same error as in the initial post.

Ideas anyone?

I know it's a bit OT, but I have the same scenario, but was not able to install SSIS on a failover cluster.
Can you point me to the "Guide" that you used for installation?

Thanks|||

Re: Clustering SSIS, there's a great whitepaper on TechNet or I have a video that can be seen here on how to cluster SSIS (about 10 mins in length): http://www.jumpstarttv.com/Media.aspx?vid=36

Brian

|||I ran into the exact same problem. I found, however, that its not really a SSIS issue, but actually a problem with a DLL, or a registry value from the client tools that was either erased or overwritten with a different version. With that in mind I removed the client tools and reinstalled them and that solved the problem

Connect to SSIS on clustered sql2k5

I got following error when try to expand msdb under SSIS in SSMS, the SSIS I connected to is on clustered server. The windows account used is member of local admin on both nodes in the cluster and sysadmin in sql. I tried with host name that SSIS runs on and virtual sql server name for connection, tried connect from remote client machine and server itself, got same error:

Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)

Login timeout expired
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.
Named Pipes Provider: Could not open a connection to SQL Server [2]. (Microsoft SQL Native Client)

Login timeout expired
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.
Named Pipes Provider: Could not open a connection to SQL Server [2]. (Microsoft SQL Native Client)

Anyone knows what's happen? Thanks.

I solve the problem by changing <ServerName>.</ServerName> to <ServerName>virtual_sql_server_name</ServerName> in %ms sql server%\90\dts\binn\MsDtsSrvr.ini.xml file on each node.|||

I have configured SSIS on the cluster after clustered DB installation. I have followed the directions on 'How to: Configure Integration Services on a Cluster' doc on msdn (http://msdn2.microsoft.com/en-us/library/ms345193.aspx) but I am still not able to connect and I get this error message (very similar to the message on beginning of this post)

TITLE: Microsoft SQL Server Management Studio

Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476


ADDITIONAL INFORMATION:

The SQL server specified in SSIS service configuration is not present or is not available. This might occur when there is no default instance of SQL Server on the computer. For more information, see the topic "Configuring the Integration Services Service" in Server 2005 Books Online.

Login timeout expired
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.
Named Pipes Provider: Could not open a connection to SQL Server [2]. (MsDtsSrvr)


BUTTONS:

OK

Appreciate any help you can provide.

Thanks!

Connect to SSIS on clustered sql2k5

I got following error when try to expand msdb under SSIS in SSMS, the SSIS I connected to is on clustered server. The windows account used is member of local admin on both nodes in the cluster and sysadmin in sql. I tried with host name that SSIS runs on and virtual sql server name for connection, tried connect from remote client machine and server itself, got same error:

Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)

Login timeout expired
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.
Named Pipes Provider: Could not open a connection to SQL Server [2]. (Microsoft SQL Native Client)

Login timeout expired
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.
Named Pipes Provider: Could not open a connection to SQL Server [2]. (Microsoft SQL Native Client)

Anyone knows what's happen? Thanks.

I solve the problem by changing <ServerName>.</ServerName> to <ServerName>virtual_sql_server_name</ServerName> in %ms sql server%\90\dts\binn\MsDtsSrvr.ini.xml file on each node.|||

I have configured SSIS on the cluster after clustered DB installation. I have followed the directions on 'How to: Configure Integration Services on a Cluster' doc on msdn (http://msdn2.microsoft.com/en-us/library/ms345193.aspx) but I am still not able to connect and I get this error message (very similar to the message on beginning of this post)

TITLE: Microsoft SQL Server Management Studio

Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476


ADDITIONAL INFORMATION:

The SQL server specified in SSIS service configuration is not present or is not available. This might occur when there is no default instance of SQL Server on the computer. For more information, see the topic "Configuring the Integration Services Service" in Server 2005 Books Online.

Login timeout expired
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.
Named Pipes Provider: Could not open a connection to SQL Server [2]. (MsDtsSrvr)


BUTTONS:

OK

Appreciate any help you can provide.

Thanks!