Post reply

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

  • October 21, 2019 at 2:50 pm

    #3690615

    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

     

  • October 21, 2019 at 3:08 pm

    #3690620

    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.
    Larnu.uk

  • October 21, 2019 at 3:26 pm

    #3690626

    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

  • October 21, 2019 at 3:43 pm

    #3690639

    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.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • October 21, 2019 at 3:47 pm

    #3690644

    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.
    Larnu.uk

  • October 21, 2019 at 4:49 pm

    #3690673

    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.

  • October 21, 2019 at 7:20 pm

    #3690724

    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!

  • October 22, 2019 at 8:06 am

    #3690889

    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.
    Larnu.uk

  • October 22, 2019 at 11:00 am

    #3690913

    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.

    SQL Server Carpenter - Consultant & Trainer
    Author of Learn T-SQL from Scratch

  • October 31, 2019 at 11:49 am

    #3693483

    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.

  • November 1, 2019 at 4:48 pm

    #3693893

    Thank you.

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

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