One Box, One DB/SSIS Instance, How to use VS to Switch between a Dev and Prod Environment

  • quinn.jay

    SSCertifiable

    Points: 5505

    Hello,

    I have one box, Windows Server 2012 R2 Standard, and the SQL Server 2016 SP1 app running, with a single instance of the DB engine, and SSIS, and a couple of Tabular SSAS instance, one for Prod, and a new one for Dev use. I'm running VS Community 2015, to develop my SSIS packages and SSAS models, my SSIS packages are in TFS.

    Currently all my SSIS packages use the Connection Manager where it is created in the package itself. The connections I use in this environment, connect to Teradata, and I use the Teradata driver v15 and the Attunity 4.0 Provider.

    What do I need to do, to be able to switch between a Prod and Dev environment within VS and using Connection Manager, or use Project.Parameters?

    I understand I'd have to copy the DB's/Tables that are today considered Production, like dbo.employee to dbo.employee_DEV, as well as create another folder in the existing SSISDB like today Prod called BizStuff to BizStudff_DEV. And restore a backup from prod in the new Dev Tab SSAS instance, with a rename to CubeName_DEV.

    What are all the steps involved with converting the Connection Manager in the Package to be a Project Parameter, and then the Project Parameter to be switchable between the Prod env and the Dev env, and any other changes/where and what, to make sure I'm getting at the correct _Dev database/table, and executing the load on the correct _DEV cube, then switch back to Prod when the development is complete?

    Thank you

  • AlexSQLForums

    SSChampion

    Points: 14240

    quinn.jay - Tuesday, March 6, 2018 10:00 AM

    Hello,

    I have one box, Windows Server 2012 R2 Standard, and the SQL Server 2016 SP1 app running, with a single instance of the DB engine, and SSIS, and a couple of Tabular SSAS instance, one for Prod, and a new one for Dev use. I'm running VS Community 2015, to develop my SSIS packages and SSAS models, my SSIS packages are in TFS.

    Currently all my SSIS packages use the Connection Manager where it is created in the package itself. The connections I use in this environment, connect to Teradata, and I use the Teradata driver v15 and the Attunity 4.0 Provider.

    What do I need to do, to be able to switch between a Prod and Dev environment within VS and using Connection Manager, or use Project.Parameters?

    I understand I'd have to copy the DB's/Tables that are today considered Production, like dbo.employee to dbo.employee_DEV, as well as create another folder in the existing SSISDB like today Prod called BizStuff to BizStudff_DEV. And restore a backup from prod in the new Dev Tab SSAS instance, with a rename to CubeName_DEV.

    What are all the steps involved with converting the Connection Manager in the Package to be a Project Parameter, and then the Project Parameter to be switchable between the Prod env and the Dev env, and any other changes/where and what, to make sure I'm getting at the correct _Dev database/table, and executing the load on the correct _DEV cube, then switch back to Prod when the development is complete?

    Thank you

    Try these links:

    https://www.hansmichiels.com/2016/09/04/using-the-project-deployment-model-with-environments-ssis-series/
    http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/159270/

    Alex S

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply