May 29, 2014 at 3:11 am
Hey,
I have a SSDT db project in a visual studio solution that will get published on deployment day.
We have a different tool that generates the canonical model for the messages we work with. The same tool creates a bunch of insert scripts for data that needs to be inserted post deployment.
So the way I have set it up for now is that I have a post deployment script called autoexec.sql. This one truncates the tables in the right order, then proceeds with the inserts.
The insert looks as follows:
/*
Post-Deployment Script Template
--------------------------------------------------------------------------------------
This file contains SQL statements that will be appended to the build script.
Use SQLCMD syntax to include a file in the post-deployment script.
Example: :r .\myfile.sql
Use SQLCMD syntax to reference a variable in the post-deployment script.
Example: :setvar TableName MyTable
SELECT * FROM [$(TableName)]
--------------------------------------------------------------------------------------
*/
Exec sp_truncatedata
GO
SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER ON;
SET NUMERIC_ROUNDABORT OFF;
GO
print 'Autoexec file'
:r .\Data\Data.Channels.sql
:r .\Data\Data.Endpoints.sql
The path is relative to the visual studio paths. But I have to edit this by hand.
So I was thinking, there must be a way to iterate through the files. Perhaps create a CTE or something like that.
my 1st brainfart was to use xp_dirtree. But xp_dirtree shows the files on the sql server. I need a way to iterate through the files in the project. The insert statements is in a subdirectory of the PostInstallScript folder.
So any advice will be awesome! The alternative is just adding them to the script by hand, however an automated solution will be much better.
May 29, 2014 at 11:21 am
Okay, I don't know how to do this within an SSDT project, but you might be able to create a PoSH script that iterates through script files in the post-deployment folder and writes out the path to the PostDeployment script. Then call the PoSH script as a pre-build event. I've never tried it, but in theory it could work. If you are using TFS you could even import the TFS cmdlets and check-out and check-in everything as part of the process.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 30, 2014 at 12:51 am
I will have to run with this. Thanks!! Appreciate the feedback.
May 30, 2014 at 6:56 am
If you get this working I'd be interested in seeing what you did.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply