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

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

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

Connecting to Analysis Services

I am having trouble getting my IS package to connect to an AS database when I schedule the package as a job.

The package executes fine through BI Development Studio, but I get the following error in the package log when I run the job step:
OnError,<server name>,NT AUTHORITY\SYSTEM,Process Cube,{C4951602-2594-4572-9587-72E539A2FB6B},{AD6B3CE0-FA38-4DC6-93C0-59D339751EC9},8/22/2007 2:05:19 PM,8/22/2007 2:05:19 PM,0,0x,A connection cannot be made. Ensure that the server is running.

Here is the connection string I am using in both BI Dev Studio and the job:
Data Source=<server name>;User ID=<user>;Password=<password>;Initial Catalog=<DB name>;Provider=MSOLAP.3;Persist Security Info=True;Impersonation Level=Impersonate;

The AS server is set up to allow remote connections, and the user I am connecting as is an administrator on the AS server.

-Randy

Dalmut1 wrote:

I am having trouble getting my IS package to connect to an AS database when I schedule the package as a job.

The package executes fine through BI Development Studio, but I get the following error in the package log when I run the job step:
OnError,<server name>,NT AUTHORITY\SYSTEM,Process Cube,{C4951602-2594-4572-9587-72E539A2FB6B},{AD6B3CE0-FA38-4DC6-93C0-59D339751EC9},8/22/2007 2:05:19 PM,8/22/2007 2:05:19 PM,0,0x,A connection cannot be made. Ensure that the server is running.

Here is the connection string I am using in both BI Dev Studio and the job:
Data Source=<server name>;User ID=<user>;Password=<password>;Initial Catalog=<DB name>;Provider=MSOLAP.3;Persist Security Info=True;Impersonation Level=Impersonate;

The AS server is set up to allow remote connections, and the user I am connecting as is an administrator on the AS server.

-Randy

It looks to me as though you're connecting using NT AUTHORITY\SYSTEM. Is that account an administrator on the AS server?

-Jamie

|||I think I should clarify: the server running the job is not on the same box as the analysis server. Should it matter what user is executing the job as long as the connection string for the AS DB is correct?
|||

Dalmut1 wrote:

I think I should clarify: the server running the job is not on the same box as the analysis server. Should it matter what user is executing the job as long as the connection string for the AS DB is correct?

Absolutely. The conenction will be made usig the credentials of whoever is running teh job. Unless you have set up a proxy account this will be the account that SQL Agent is running under.

-Jamie

|||

Jamie Thomson wrote:

Dalmut1 wrote:

I think I should clarify: the server running the job is not on the same box as the analysis server. Should it matter what user is executing the job as long as the connection string for the AS DB is correct?

Absolutely. The conenction will be made usig the credentials of whoever is running teh job. Unless you have set up a proxy account this will be the account that SQL Agent is running under.

-Jamie

Aha. I set up a proxy account that is working like a charm. Thanks.

Connect to Sybase in a DTS Package

How would I connect to a Sybase ASE 12.5 Server in a data transformation step in a DTS package (MS SQL Server 2000)? I am not sure of the driver to specify, etc.
Thanks.You must install sybase client package which included network communication files,odbc driver,oledb driver first.
Then you must create a database alias in dsedit same as client network configuration tools of SQL Server.Please remember the communication port of sybase is 4100.Test if it works well. see pic-1
Then,you need create an odbc connection in odbc manager.Please fill with the alias name which has been named in dsedit in server name input-box. see pic-2
And now,you can choose other connection using sybase odbc driver in DTS package designing. see pic-3|||Picture-1,2,3