Optionally including scripts in SQL Server Projects 2012

Question

I am building a SQL Publish Script that will be used to generate a database to our internal servers, and then used externally by our client.

The problem I have is that our internal script will automate quite a few things for us, in which the actual production environment will require these completed manually.

For example, internally we would use the following script

-- Global variables
:setvar EnvironmentName 'Local'

-- Script.PostDeployment.sql

:r .\PopulateDefaultValues.sql

IF ($(EnvironmentName) = 'Test')
BEGIN
   :r .\GivePermissionsToDevelopmentTeam.sql
   :r .\PopulateTestData.sql
   :r .\RunETL.sql
END
ELSE IF ($(EnvironmentName) = 'Client_Dev')
BEGIN
   :r .\GivePermissionsToDevWebsite.sql
END

This would generate a script like this:

-- (Ignore syntax correctness, its just the process I'm after)

IF($(EnvironmentName) = 'Test')
BEGIN
    CREATE LOGIN [Developer1] AS USER [MyDomain\Developer1] WITH DEFAULT SCHEMA=[dbo];
    CREATE LOGIN [Developer2] AS USER [MyDomain\Developer2] WITH DEFAULT SCHEMA=[dbo];
    CREATE LOGIN [Developer3] AS USER [MyDomain\Developer3] WITH DEFAULT SCHEMA=[dbo];

    -- Populate entire database (10000's of rows over 100 tables)
    INSERT INTO Products ( Name, Description, Price ) VALUES
    ( 'Cheese Balls', 'Cheesy Balls ... mm mm mmmm', 1.00),
    ( 'Cheese Balls +', 'Cheesy Balls with a caffeine kick', 2.00),
    ( 'Cheese Squares', 'Cheesy squares with a hint of ginger', 2.50);

    EXEC spRunETL 'AUTO-DEPLOY';
END
ELSE IF($(EnvironmentName) = 'Client_Dev')
BEGIN
    CREATE LOGIN [WebLogin] AS USER [FABRIKAM\AppPoolUser];
END
END IF

This works fine, for us. When this script is taken on site, the script fails because it cannot authenticate the users of our internal environment.

One item I thought about permissions was to just give our internal team sysadmin privileges, but the test data just fills the script up. When going on site, having all of this test data just bloats the published script and isn't used anyway.

Is there any way to exclude a section entirely from a published file, so that all of the test data and extraeous inserts are removed, without any manual intervention of the published file?

We use something really similar and haven't had this issue. We definitely have logins/users that don't exist in other environments and the post-deploy script passes over those. Are they running the script in SQLCMD mode? — Peter Schott, Nov 22 '13 at 18:46
Although the SQLCMD does happily skip over it, its more of the code bloat I'm trying to avoid, or the auto-inclusion of external scripts into the main body. — Dominic Zukiewicz, Nov 23 '13 at 19:11
Got it. I don't think that's possible w/ the Pre and Post deploy scripts. There might be some way to code them so you could parse the generated script to remove sections, but it would be outside of SSDT. — Peter Schott, Nov 23 '13 at 23:57

score 0 · Answer 1 · answered Oct 11 '14 at 02:32

Unfortunately, there is currently no way to remove the contents of a referenced script from the generated file entirely.

The only way to achieve this is to post-process the generated script (Powershell/Ruby/scripting language of choice) to find and remove the parts you care about using some form of string and file manipulation.

Based on: My experience with doing this exact same thing to remove a development-environment-only script which was sizable and bloated the Production deployment script with a lot of 'noise', making it harder for DBA's to review the script sensibly.

Optionally including scripts in SQL Server Projects 2012

1 Answers1