Connecting to Paradox

Hello,
I am trying to connect to paradox database for the purpose of updating
the record(s) in DTS ActiveX Script Task.
I used the following connection string. But I get an error while
connection. I have "UMTest.db" and "UMTest.px" files in the
"C:\MYDBPath" in my local drive.
Set objRs =3D CreateObject("ADODB.RecordSet"=AD)
DB_STRING =3D "Provider=3DMicrosoft.Jet.OLEDB.=AD4.0;Data
Source=3DC:\MYDBPath;Extended Properties=3DParadox 5.x;"
strSQL =3D " UPDATE UMTest SET Field1 =3D 1.222 WHERE Col1ID =3D '" & Value1
& "' AND Col2 =3D '" & Value2 & "'"
objRs.Open strSQL, DB_STRING
I get the following error.
Error Source: Microsoft Jet Database engine.
Error Description: No Value given for one or more required parameters.
Connection string looks alright to me. I don't know what is other
reason which is causing this error to happen.=20
Thanks.
You may try to create a User DSN first using ODBC Administrator. Instead of
DB_STRING="..", you can set DB_STRING=UserDSNNameYouCreated. To simplify it,
please do not include update command. Try to connect it first and see if it
works.
Thanks
Bei
"Yunus's Group" <yunusasmath@.gmail.com> wrote in message
news:1123266364.868066.278640@.g43g2000cwa.googlegr oups.com...
Hello,
I am trying to connect to paradox database for the purpose of updating
the record(s) in DTS ActiveX Script Task.
I used the following connection string. But I get an error while
connection. I have "UMTest.db" and "UMTest.px" files in the
"C:\MYDBPath" in my local drive.
Set objRs = CreateObject("ADODB.RecordSet"X)
DB_STRING = "Provider=Microsoft.Jet.OLEDB.X4.0;Data
Source=C:\MYDBPath;Extended Properties=Paradox 5.x;"
strSQL = " UPDATE UMTest SET Field1 = 1.222 WHERE Col1ID = '" & Value1
& "' AND Col2 = '" & Value2 & "'"
objRs.Open strSQL, DB_STRING
I get the following error.
Error Source: Microsoft Jet Database engine.
Error Description: No Value given for one or more required parameters.
Connection string looks alright to me. I don't know what is other
reason which is causing this error to happen.
Thanks.

Connecting to Paradox

Hello,
I am trying to connect to paradox database for the purpose of updating
the record(s) in DTS ActiveX Script Task.
I used the following connection string. But I get an error while
connection. I have "UMTest.db" and "UMTest.px" files in the
"C:\MYDBPath" in my local drive.
Set objRs =3D CreateObject("ADODB.RecordSet"=AD)
DB_STRING =3D "Provider=3DMicrosoft.Jet.OLEDB.=AD4.0;Data
Source=3DC:\MYDBPath;Extended Properties=3DParadox 5.x;"
strSQL =3D " UPDATE UMTest SET Field1 =3D 1.222 WHERE Col1ID =3D '" & Value1
& "' AND Col2 =3D '" & Value2 & "'"
objRs.Open strSQL, DB_STRING
I get the following error.
Error Source: Microsoft Jet Database engine.
Error Description: No Value given for one or more required parameters.
Connection string looks alright to me. I don't know what is other
reason which is causing this error to happen.=20
Thanks.You may try to create a User DSN first using ODBC Administrator. Instead of
DB_STRING="..", you can set DB_STRING=UserDSNNameYouCreated. To simplify it,
please do not include update command. Try to connect it first and see if it
works.
Thanks
Bei
"Yunus's Group" <yunusasmath@.gmail.com> wrote in message
news:1123266364.868066.278640@.g43g2000cwa.googlegroups.com...
Hello,
I am trying to connect to paradox database for the purpose of updating
the record(s) in DTS ActiveX Script Task.
I used the following connection string. But I get an error while
connection. I have "UMTest.db" and "UMTest.px" files in the
"C:\MYDBPath" in my local drive.
Set objRs = CreateObject("ADODB.RecordSet"_)
DB_STRING = "Provider=Microsoft.Jet.OLEDB._4.0;Data
Source=C:\MYDBPath;Extended Properties=Paradox 5.x;"
strSQL = " UPDATE UMTest SET Field1 = 1.222 WHERE Col1ID = '" & Value1
& "' AND Col2 = '" & Value2 & "'"
objRs.Open strSQL, DB_STRING
I get the following error.
Error Source: Microsoft Jet Database engine.
Error Description: No Value given for one or more required parameters.
Connection string looks alright to me. I don't know what is other
reason which is causing this error to happen.
Thanks.

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 a table

Hi

Please let me how to put a specific query to a database in the task and get the values from the db.

I have this code:

ConnectionManager cm = Microsoft.SqlServer.Dts.Runtime.DtsConvert.ToConnectionManager(

ComponentMetaData.RuntimeConnectionCollection["conRun"].ConnectionManager);

cm.Name = "TestConn";

cm.ConnectionString = "Data Source=srcServerName;Initial Catalog=srcDBName;" +

"Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;" +

"Auto Translate=False;";

After doing this, if i need to write a specific query to a table. How to do it.

Thanks

Vipul

Hi Vipul:

Not sure if this is exactly what you're trying to do, but I think this might be close to what you need.

I'm using the following code to extract a stored flat file date/time stamp from a SQL Server table.
Notice that I'm using a DataAdapter to populate a DataTable with the rows returned by the in-line
SQL statement. This function returns just the first column of the first row of the DataTable, but could
just as easily return the entire DataTable, or just one row from the table:

Private Function GetPreviousDateTimeStamp(ByVal pstrFileName As String) As Date
Dim cn As Data.SqlClient.SqlConnection
Dim cm As Data.SqlClient.SqlCommand
Dim da As Data.SqlClient.SqlDataAdapter
Dim dt As DataTable
Dim strSQL As String

Try
cn = New SqlConnection

'Set up a Connection object:
cn = CType(Dts.Connections("My_Connection_ADO" _
).AcquireConnection(Nothing), Data.SqlClient.SqlConnection)

strSQL = "SELECT PreviousDateTimeStamp " _
& "FROM dbo._FileTimeStamps " _
& "WHERE ImportFileName = '" & pstrFileName & "'"

'Establish a Command object with this SQL statement:
cm = New Data.SqlClient.SqlCommand(strSQL)

'Set Command object's Connection property:
cm.Connection = cn

'Set up a SqlDataAdapter:
da = New SqlDataAdapter(cm)

'Instantiate a DataTable:
dt = New DataTable

'Populate the DataTable:
da.Fill(dt)

'Return the first cell of the first row:
Return CType(dt.Rows(0).Item(0), Date)

Catch ex As Exception

Throw ex

End Try

End Function

Hope this helps! BTW: I could only get this code to work with an ADO connection,
and could not get it to work with an OLEDB connection. I'm sure someone here knows
why I couldn't use OLEDB, but it's working for me and I'm okay with that.

- Mike

|||

Hi Mike:

Thanks for the reply. I was able to do it. This is my piece of code and i have made use of oledb only.

ConnectionManager cm = Microsoft.SqlServer.Dts.Runtime.DtsConvert.ToConnectionManager(

ComponentMetaData.RuntimeConnectionCollection["Source"].ConnectionManager);

ConnectionManagerOleDb cmoledb = cm.InnerObject as ConnectionManagerOleDb;

OleDbCommand oledbCommand;

String connMgrString = cm.ConnectionString;

OleDbConnection conn = new OleDbConnection(connMgrString);

conn.Open();

String query = "INSERT INTO Tab1 (First, Second) " + " VALUES ('TEST', 'TEST')";

oledbCommand = new OleDbCommand(query, conn);

oledbCommand.ExecuteNonQuery();

conn.Close();

You can check it if this helps.

Thanks

Vipul

|||

Hi Vipul:

Is your code working now? It looks okay to me.

- Mike

|||

Yes it works..

Thanks

Vipul

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.

Connecting Execute SQL Task with Firebird Connection Manager

Hi,

I'm using Integration Service between Firebird and MS SQL 2005. I can able to move data from Firebird db tables to MS SQL db tables using DataReader source (Firebird Connection Manager) and SQL Server Destination (SQL Server Connection Manager) component.

Now I want to execute parametrized SQL statement to fetch data from Firebird DB using Execute SQL Task Component. In this process while trying to set Connection property to this component I can able to view only MS SQL Connection Manager and it is not listing Firebird Connection Manager.

Now I need a help to get connect this Execute SQL Task component with Firebird connection Manager. Help me to find out where I'm making mistake.

FYI. I'm using Firebird .Net Provieder 2.0.1
Thanks in advance.

Suku

More clarification on my query

Here, the mentioned Firebird Connection Manager is a custom created connection manager and we created using Firebird .Net Provider 2.0.1

I tried with Lookup component too, it shows only the destination connection Manager (MS SQL) but not the Source one (Firebird Custom Connection Manager).

Suku

|||

Could you try to set the connection type to Ado .NET in the Execute SQL Task UI?

Lookup can only use OLE DB connections not Ado .NET.

Thanks.

|||

Hi,

Were you able to resolve this issue? I also have a custom connection manager that I am trying to use from Execute SQL Task. Unfortunatelly I don't see my connection object in Execute SQL Task Editor, though it gets added as a new connection successfully. My custom connection manager is basically a very simplified version of the Microsoft's SqlConnectionManager Sample. I am trying to make a custom connection manager which will hide the login info.

Thanks!

Connecting Execute SQL Task with Custom Connection Manager

Hi,

I'm using Integration Service between Firebird and MS SQL 2005. I can able to move data from Firebird db tables to MS SQL db tables using DataReader source (Firebird Connection Manager) and SQL Server Destination (SQL Server Connection Manager) component.

Now I want to execute parametrized SQL statement to fetch data from Firebird DB using Execute SQL Task Component. In this process while trying to set Connection property to this component I can able to view only MS SQL Connection Manager and it is not listing Firebird Connection Manager.

Now I need a help to get connect this Execute SQL Task component with Firebird connection Manager. Help me to find out where I'm making mistake.

FYI. I'm using Firebird .Net Provieder 2.0.1
Thanks in advance.

Suku

More clarification on my query

Here, the mentioned Firebird Connection Manager is a custom created connection manager and we created using Firebird .Net Provider 2.0.1

I tried with Lookup component too, it shows only the destination connection Manager (MS SQL) but not the Source one (Firebird Custom Connection Manager).

Suku

|||

Could you try to set the connection type to Ado .NET in the Execute SQL Task UI?

Lookup can only use OLE DB connections not Ado .NET.

Thanks.

|||

Hi,

Were you able to resolve this issue? I also have a custom connection manager that I am trying to use from Execute SQL Task. Unfortunatelly I don't see my connection object in Execute SQL Task Editor, though it gets added as a new connection successfully. My custom connection manager is basically a very simplified version of the Microsoft's SqlConnectionManager Sample. I am trying to make a custom connection manager which will hide the login info.

Thanks!