CODEWITHSUNDEEP
visual-studiosql-server-data-tools
Mike Caputo
Mike Caputo

Reputation: 69

Replacing database names with variables in SSDT

We are using SSDT to create a build and deploy pipeline using Git and ADO. In order for the solution to build, all cross-database references must have a corresponding Database Reference in the project and must be referred to using a [$(DatabaseVariable)]. This means that every time a change is made and synched using Schema Compare, the database names have to be replaced either manually or with a find-and-replace. There are of course numerous downsides to the find-and-replace approach, including the fact that it will find and replace references in the project files that should not be database variables. I'm hoping someone knows of a way to automate this process that doesn't involve such a brute force method as find-and-replace.

I have searched around extensively for this and found nothing helpful.

Here's an example view that contains cross-database references:

CREATE view [Migration].[vwCHILDS_Allegation_Allegation]
as
with src as (
select
    cast('' as nvarchar(50)) CEAllegationIdentifier
    , (select AllegationTypeId from [$(CWNS_Migration)].Allegation.AllegationType where Code = dfrvmi1.DestinationDataFieldReferenceValueCode) AllegationTypeId
    , cast(1 as int) SourceSystemId
    , cast(src.IDNBR as nvarchar(64)) SourceSystemIdentifier
    , src.IDNBR SourceSystemIdentifier_Numeric
    , case when src.CRET_DT_TM = '0001-01-01' then null else src.CRET_DT_TM end SourceSystemCreatedDateTime
    , case when src.MOD_DT_TM = '0001-01-01' then null else src.MOD_DT_TM end SourceSystemModifiedDateTime
    , (
        select
            max(pe.PersonId) 
        from
            [$(CWNS_Migration)].PersonIdentity.PersonIdentifier pe
            join [$(CHILDSDB2)].VLCHA.STAFF_USERID st on cast(st.FK_STAFFFK_PERSID as nvarchar(64)) = pe.Identifier
                and pe.PersonIdentificationSystemId = 4
        where
            st.USERID = ltrim(rtrim(src.MOD_USR_ID))) SourceSystemModifiedPersonId
from
    [$(CHILDSDB2)].VLCHA.ALGTN src
    left join [$(DataCatalog)].dbo.DataFieldReferenceValueMappingInfo dfrvmi1 on dfrvmi1.SourceDataFieldReferenceValueDataFieldId = 216
        and dfrvmi1.SourceDataFieldReferenceValueCode = ltrim(rtrim(src.FK_ALGTN_PRIORICTG))
        and dfrvmi1.DestinationDataFieldReferenceValueDataFieldId = 20605
)
select
    src.*
from
    src
    left join [$(CWNS_Migration)].Allegation.Allegation tgt on tgt.SourceSystemId = src.SourceSystemId and tgt.SourceSystemIdentifier = src.SourceSystemIdentifier
    left join [$(CWNS_Migration)].Quarantine.Allegation q on q.SourceSystemId = src.SourceSystemId and q.SourceSystemIdentifier = src.SourceSystemIdentifier
        and q.QExecutionId = 1
where
    q.QExecutionId is null
    and (
        isnull(src.AllegationTypeId, 0) <> isnull(tgt.AllegationTypeId, 0)
        or isnull(try_cast(src.SourceSystemCreatedDateTime as datetime), '') <> isnull(tgt.SourceSystemCreatedDateTime, '')
        or isnull(try_cast(src.SourceSystemModifiedDateTime as datetime), '') <> isnull(tgt.SourceSystemModifiedDateTime, '')
        or isnull(src.SourceSystemModifiedPersonId, 0) <> isnull(tgt.SourceSystemModifiedPersonId, 0)
    )

I'm also hoping there's a way to avoid having Schema Compare always show the variables as differences with the database.

Upvotes: 1

Views: 1435

Answers (1)

Dmitrij Kultasev
Dmitrij Kultasev

Reputation: 5745

Ideally you need to include all databases as the reference to your project (right click on the project and do Add database reference, or something like that). You can do that in 2 ways: * Create project for each database and import all or only objects that being used by your database * Extract dacpac from the live database and use it as a reference

Then create synonyms for every single cross database objects in your main database. For example if we have object [$(CWNS_Migration)].PersonIdentity.PersonIdentifier then you'll need to create synonym as 

CREATE SYNONYM PersonIdentity.PersonIdentifier
    FOR [$(CWNS_Migration)].PersonIdentity.PersonIdentifier;

and then use PersonIdentity.PersonIdentifier (2 part name) in your code instead of the 3-4 part names. That's the project setup.

Now, when you right click on the project and do "Publish", it will pop up the dialogue where you can specify the vale for the CWNS_Migration variable and change it to whatever you need. After that you have the option to save this settings (Save As) and the result of that save is called as "Publish Profile". So, for every environment you just need to create different publish profile and use it when publish your changes.

You might want to try to start with synonyms first, it might work (need to verify), but I would recommend to add database references anyway.

Upvotes: 2

Related Questions