Sql script files deployment


(Tom) #1

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


#3

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


(Tom) #4

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


#5

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