none
Choose Provider (SQL or Oracle) at Deployment Time

    Pergunta

  • 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

    sexta-feira, 9 de março de 2007 17:34

Todas as Respostas

  • 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.
    sexta-feira, 9 de março de 2007 18:03
  •  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

     

    sexta-feira, 9 de março de 2007 18:06
  •  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

     

    sexta-feira, 9 de março de 2007 18:08