How investigate type of connection to DB( MS SQL or Oracle) during execution of a packet?

How investigate type of a connection to DB( MS SQL or Oracle) during execution of a packet?

In the time of executon of packet I must determine type of connection? What do good practices exists?

Thanks In Advance.

I don't understand the question. The way I read it is counter-intuitive for the way SSIS works. SSIS needs to know upfront which connection to use.|||

Yes, you are right. SSIS needs to know upfront which connection to use IN DESIGN TIME.

I need provide ETL operations for MS SQL and ORACLE databases, but I can't to design two similar packages for Oracle and MS SQL.

Therefore I want:

After deployment into another server I can change type of connection.

For example, I have two databases in ORACLE 9i and MS SQL 2005. Every of these databases has table X for same structure ( field1 int, field2 varchar(X) ). I need to load data from table X.

During design time:

1) In SSIS package I created connection to MS SQL server.

2) I loaded data from DB for some purpose.

After deployment:

I can make a change in connection string in Execute package Utility. For instance, switch connection from MS SQL to Oracle.

During runtime:

I need to know which type of a server this connection to be connected (MS SQL or Oracle). I need it to form a SQL-request in a Task script for SQL-execute script.

|||Yes, you are right. SSIS needs to know upfront which connection to use IN DESIGN TIME.

I need provide ETL operations for MS SQL and ORACLE databases, but I can't to design two similar packages for Oracle and MS SQL.

Therefore I want:

After deployment into another server I can change type of connection.

For example, I have two databases in ORACLE 9i and MS SQL 2005. Every of these databases has table X for same structure ( field1 int, field2 varchar(X) ). I need to load data from table X.

During design time:

1) In SSIS package I created connection to MS SQL server.

2) I loaded data from DB for some purpose.

After deployment:

I can make a change in connection string in Execute package Utility. For instance, switch connection from MS SQL to Oracle.

During runtime:

I need to know which type of a server this connection to be connected (MS SQL or Oracle). I need it to form a SQL-request in a Task script for SQL-execute script.

|||Since the connection string is run-time dynamic , look at the connection string at runtime. In particular, you can use the class OleDbConnectionStringBuilder inside a script task.

To that point, you can get the provider, the data source, and all the other key value pairs as properties (or method calls) against the OleDbConnectionStringBuilder. The provider property will give you the conditionals you're looking for (see http://msdn2.microsoft.com/en-us/library/system.data.oledb.oledbconnectionstringbuilder.aspx ) for the properties on this object.

Also, if you're passing in the connection string as a variable, you don't need a script task, you can just parse out the provider using an SSIS expression.