Choose Provider (SQL or Oracle) at Deployment Time

Is it possible to design a package with one connection manager who's name remains static, but the actual provider changes at deployment time?

For example, I have two connection managers, source and target. Each of these, depending on the environment, may use any combination of native SQL Server, or Oracle.

When I create a connection manager, the provider is specified at design time. Is it possible, using the confguration files, to allow the administrator to determine the provider at deployment time, such that the Control Flow and Data Flow tasks can use the connection mangers without knowing the provider, or more importantly, only one version of the package need be maintained?

Thanks,

Rick

Provided the data types were the same, yes, you might be able to get away with updating the ConnectionString property, however going from Oracle to SQL Server will undoubtedly cause you metadata problems. I'm not sure on that approach though. (Oracle numeric data types come to mind as a problem mapping to SQL Server)

You should probably create two data flows (or as many as you need) and then use expression constraints on your control flow to determine which "flow" to use.|||

RickGaribay.NET wrote:

Is it possible to design a package with one connection manager who's name remains static, but the actual provider changes at deployment time?

For example, I have two connection managers, source and target. Each of these, depending on the environment, may use any combination of native SQL Server, or Oracle.

When I create a connection manager, the provider is specified at design time. Is it possible, using the confguration files, to allow the administrator to determine the provider at deployment time, such that the Control Flow and Data Flow tasks can use the connection mangers without knowing the provider, or more importantly, only one version of the package need be maintained?

Thanks,

Rick

Yes, this is possible. The provider is within the ConenctionString property which can be changed at execution-time using configurations or property expressions.

More on property expressions: http://blogs.conchango.com/jamiethomson/archive/tags/Expressions/default.aspx

-Jamie

|||

Phil Brammer wrote:

Provided the data types were the same, yes, you might be able to get away with updating the ConnectionString property, however going from Oracle to SQL Server will undoubtedly cause you metadata problems. I'm not sure on that approach though.

Probbaly only if you are using data-flows - which need not be the case.

Phil Brammer wrote:

You should probably create two data flows (or as many as you need) and then use expression constraints on your control flow to determine which "flow" to use.

This is a good idea. You can make the connection string property conditional as well using property expressions (see my earlier post).

-Jamie