Sql script files deployment

[Winterman (Tom)]

Hi there,

I have defined a 2 step deployment:
Deploy an IIS website
Execute a sql script file

According to a forumpost i have to add the sql script to the package that also contains the website. So far so good and i have everything running; the website deploys and the sql script is executed with the SQL - Execute Script File template.

But… the sql script file is also deployed to the website and that is something i want to prevent from happening.
How can i manage this so who can point me in the right direction.

Thanks a bunch,

Tom

[Alex.Rolley]

Hi Tom,

Thanks for getting in touch!

If I understand the issue correctly you are bundling a SQL script file within your website package so that you can call and run it from the SQL - Execute Script File step?

What I would suggest as a possible alternative is to have the script file added as a second package to your deployment using the Deploy a Package step and enabling the Custom Install Directory option which is available under Configure Features in the top right right hand corner. What this will let you do is deploy your script file to the deployment target at a known location that you can then call in your SQL script step.

The advantage here is that you can then have a following script step at the end of your process that then removes this directory (or the files in the directory) as part of you deployment, which means that your SQL file should only be there for the duration of the deployment. I would also suggest selecting the Purge this directory option as part of configuring your Custom Install Directory, just as a safety measure to ensure that only one SQL file can be present at any time.

I hope that helps, please let me know if there is anything else that I can assist with, or if I’ve misunderstood the issue at all.

Regards,
Alex

[Winterman (Tom)]

Hello Alex,

Thank you for the answer. You have understood the issue perfectly well.

I have one small question though, can you point me in the direction of an script that deletes the mentioned custom install directory? That would be awesome.

Thanks in advance and with best regards,

Tom

[Alex.Rolley]

Hi Tom,

No problems. The below script will do what you are wanting, you will just need to change $path to the location of your custom directory:

$path = "c:\SQLScript\"

if (-not ($path | test-path)) {
    throw "The folder doesn't exist, exiting."
} else {
   Remove-Item $path -Recurse -Force
}

As a safety this script checks that the folder path exists before running the delete, and will exit with an error if the directory isn’t there.

Hope that helps, let me know if you have any other questions,

Regards,
Alex

[Winterman (Tom)]

Hi Alex,

I have another question about this.

How can I make this script more generic and pass in a Tenant variable for the $path?

Thanks in advance,

Met vriendelijke groet,

Tom de Winter

Hullenbergweg 250

Postbus 22223

1100 CE Amsterdam Zuidoost

Telefoon +31 (0)88 7378290

t.dewinter@bnsolutions.nl

www.bnsolutions.nl

[Alex.Rolley]

Hi Tom,

That’s an easy change to the script, the magic is in scoping the variable correctly. On that front I would recommend reading up on tenant scoped variables to get a basic understanding.

Once you have done that, you can use a variable that you create (for example #{Tenant.Deployment.Path}) that is scoped per Tenant and add it to your script

$path = #{Tenant.Deployment.Path}

if (-not ($path | test-path)) {
    throw "The folder $path doesn't exist, exiting."
} else {
   Remove-Item $path -Recurse -Force
}

Getting started with Tenant variables can be confusing so please let me know if you need any further assistance.

Regards,
Alex