Used Stored procedure in SSIS with different database name in DEV, Test and Prod

  • patla4u

    SSCommitted

    Points: 1938

    Hi,

    Can you please give me Idea, how can i use stored procedure in SSIS with different database name in Dev, Test and Prod ?

    In stored procedure, I am going to use source server as Phm_Dev ( Reading the data ) and load into  Med_Dev (destination server)

    So when I move my SSIS In test,, Database name will be Phm_Test and Load into MED_TEST.. same like production..

    How can i make stored procedure dynamic ?

    Thanks

    B

     

  • Thom A

    SSC Guru

    Points: 98407

    Sounds like you're after Environmental Variables. I assume you're using SQL Server 2012+ and SSISDB?

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.

  • patla4u

    SSCommitted

    Points: 1938

    Yes,,  Using SSIS - 2012.

    How can i use stored procedure in Data flow task ?

    Am i need to write dynamic SQL ? Is there any other option  ?

    Thanks

    B

     

    T

  • Phil Parkin

    SSC Guru

    Points: 243766

    patla4u wrote:

    Hi,

    Can you please give me Idea, how can i use stored procedure in SSIS with different database name in Dev, Test and Prod ?

    In stored procedure, I am going to use source server as Phm_Dev ( Reading the data ) and load into  Med_Dev (destination server)

    So when I move my SSIS In test,, Database name will be Phm_Test and Load into MED_TEST.. same like production..

    How can i make stored procedure dynamic ?

    Thanks

    B

    Are your Dev, Test and Prod databases all on the same SQL Server instance? If so, ...  <Phil swallows his tongue>

    If not, why do they have different names? Keep the same DB names, it makes everything simpler.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • Thom A

    SSC Guru

    Points: 98407

    patla4u wrote:

    How can i use stored procedure in Data flow task ?

    I assume you mean as a source, like you would any other time you want to use SQL; with a SQL Statement: EXEC Schema.Procedure {Parameters}.

    Phil raises a good point about the same instance; I've assumed you at least have different instances. if you don't, you can still do this, but there's more work, and we therefore need to know if we need to include that information.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.

  • patla4u

    SSCommitted

    Points: 1938

    All Databases is on same Instant but instance are different as well.. This is requirement as per DBA.

    So We have different instance for Dev, Test and prod & database name is different too..

    Only Advantage is that, both database on same server for each Dev, test and Prod.

  • Lowell

    SSC Guru

    Points: 323416

    one of the core caveats of the development life cycle is that  the code is tested in dev, and is promoted UNCHANGED to the next layer for acceptance and testing.

    if you have to modify procedures based on where they get deployed, your system is not following the rules.

    the procedure should not care about database names. if it is making a cross database query, it should be from database [Phm].[dbo].[TargetTable]  to [Med].dbo.[TargetTable]

    So f you should have Three SQL instances, for DEV/QA and Prod., i would expect two identically named databases on

    MyServer\SQL2012DEV

    MyServer\SQL2012QA

    MyServer\SQL2012Prod

     

    of you have ONE SQL isntance, with six databases, you will need to create synonyms for the objects instead.

    CREATE SYNONYM dbo.synTargetTable FOR [Phm].[dbo].TargetTable

     

    then the procedure should target the synonym [synTargetTable] so the code never changes, but the underlying pointer/Synonym can be changes per environment.

     

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Thom A

    SSC Guru

    Points: 98407

    patla4u wrote:

    All Databases is on same Instant but instance are different as well.. This is requirement as per DBA.

    Huh? A instance can't be the same instance and different instances. That's like saying "All Employees drive the Company's only company car, and drive their company car." How can they all drive their own, when the company only has one?

    Which is it, many cars, or one car?

     

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.

  • Brahmanand Shukla

    SSChasing Mays

    Points: 660

    Solution for Stored Procedures/View/Trigger/Function :

    You can leverage the Synonyms. Instead of hard-coding the database name you can create the synonyms in each database and refer the synonyms instead of referring the actual table/stored procedure.

    Read the below article to know more about Synonyms.

    https://docs.microsoft.com/en-us/sql/relational-databases/synonyms/synonyms-database-engine?view=sql-server-ver15

     

    Solution for SSIS package :

    1. Store the Database name in SSIS package config file
    2. Load the values from config file and assign it to Package Variable using script task.
    3. Make use of expression in the Data Flow Task, Execute SQL Task, Script Task or wherever you want.
  • gvoshol 73146

    Hall of Fame

    Points: 3107

    Use project connection managers for your db connections in the SSIS package.

    Deploy the SSIS from your development tool (e.g. Visual Studio) to Integration Services Catalog SSISDB in your dev environment.

    Once dev is complete, deploy your SQL objects (tables, views, SP's etc) to your test environment - you have scripts for this, correct?

    Then deploy the SSIS from the Dev SSISDB to Test SSISDB, and use Configure to change the connection managers in the project to the Test server/db.

    When testing is complete, repeat to promote from Test to Prod.

     

    One downside is that if you find the package needs to be changed while testing, you have to repeat the deployment process (tool to dev environment to test environment) so you know everything is still in sync.

  • patla4u

    SSCommitted

    Points: 1938

    Thank you.

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

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