Enabling "Open Project Folder" for project types that don't support it

One annoying thing about SSIS (2008) and some of the other project types in Visual Studio is the lack of an "Open Project Directory..." option on the project right-click menu.  Visual Studio's "Tools" menu can provide a work-around this.
 
Go to Tools... External Tools... and add a new entry with the following properties:
Title = &Open Project Folder
Command = explorer.exe
Arguments = "$(ProjectDir)"
Initial Directory = "$(ProjectDir)"

Now, when I select my SSIS project, I can choose "Tools... Open Project Folder" (or ALT+T,O,<ENTER>), and the correct folder will open in Windows Explorer.
 
Using macros such as $(ProjectDir), $(SolutionDir), $(Configuration), and others can come in very handy when you find yourself doing the same types of things over and over in Visual Studio.  Here is the documentation for all other built-in VS macros:  http://msdn.microsoft.com/en-us/library/c02as0cs.aspx

Excellent SSIS 2012 Upgrade Video

This presentation gives an excellent overview of the process of upgrading SQL Server 2008 SSIS packages to work with SQL Server 2012 SSIS.  I’m very impressed at the much-improved capabilities for deploying SSIS packages in a less-scary way using package and project parameters, support for SSIS “environments” (“prod”, “dev”, “test”, etc.) and several other improvements including SQL Server 2012 automatically archiving previous versions of your packages when a new version is deployed.  It seems that it may actually be practical to use the built-in SSIS deployment functionality now.

http://channel9.msdn.com/Events/TechEd/NorthAmerica/2012/DBI329

This presentation was delivered by Sven Aelterman at TechEd 2012 – well done Sven!

An Attempt to Make SSIS Package Deployment More Reasonable

Full disclosure: There might be a better way to do this.  I am not aware of it.

Problems:

1. Maintaining multiple DB environments (Local, Shared Dev, Staging, Prod) means needing to maintain multiple SSIS environments.
2. SSIS packages are tricky to configure using the existing toolset – it’s easy to accidentally use the wrong config file, or forget that you turned config files off, etc.
3. There’s insanity regarding different developers configuring different connection strings.
4. There’s no good out of the box way to say “deploy package X to environment Y”, etc.
5. There’s no good way to keep your DTSConfig files for different environments in your Source Control system.

Idea:

I want to write a script (or something) to take an SSIS package that I have built and:

1. Have a sane way to copy that SSIS package to the correct spot on a file system for the “environment” that I have selected (Local, Shared Dev, Staging, Prod).
2. Ensure that “Use SSIS Config File” is enabled on the package that is copied to that location regardless of if it is enabled on the package that I am editing in Visual Studio locally.
3. Ensure that an up-to date dtsConfig file is deployed along with the SSIS package (if desired) and also to ensure that the SSIS package is hacked to use a relative path to that dtsConfig file such as .\myconfig.dtsConfig (if desired, absolute paths also supported).
4. Allow updating the SSIS config file appropriately with connection strings appropriate for that environment.

Result:

I’ve now built such a thing.  It’s a VBScript where the top of the script looks like this (skipping Option Explicit):

---

 

CONST DtsPackage = "MyPackageName.dtsx"

CONST DtsConfig = ".\MyPackageName.dtsConfig"

CONST DeployTo = "\\MyProdServer\e$\SomePathGoesHere\Packages"

CONST DeployConfig = True

Const MaxDTSXFileAgeInMinutes = 10

Dim ConnectionStrings

ConnectionStrings = Array( "OutputTextFile","E:\SomePathGoeshere\Output.txt", _

   "DBConnection","Data Source=MyProdServer;Initial Catalog=MyDatabase;Integrated Security=True;")

 

---

That’s the production one.  The Dev one looks like this (differences highlighted):

---

 

CONST DtsPackage = "MyPackageName.dtsx"

CONST DtsConfig = ".\MyPackageName.dtsConfig"

CONST DeployTo = "\\MyDevServer\D$\SomePathGoesHere\DevPackages"

CONST DeployConfig = True

Const MaxDTSXFileAgeInMinutes = 10

Dim ConnectionStrings

ConnectionStrings = Array( "OutputTextFile","D:\SomePathGoeshere\Dev\Output.txt", _

    "DBConnection","Data Source=MyDevServer;Initial Catalog=MyDatabase;Integrated Security=True;")

---

I am able to check both of these VBScripts into source control.  This means any of my developers can use them.  The way it’s written, if DeployConfig is set to false, you don’t need to put the connection strings stuff in the script so the production info can stay secure (you’d just have to pre-stage the config file and not grant the devs access to the destination folder).

In the current VBScript incarnation, to run the deploy script, you just open the SSIS project folder, open a command prompt, type CSCRIPT<space>, drag and drop the script onto the command line and hit ENTER.  After a few seconds, your SSIS package is deployed to the correct environment.  DONE!!!  Once the script for an environment is written, this process takes about 15 seconds.  Writing the script for an environment takes 15 minutes or so just to confirm all the details, give it a quick test, and get the new script into Source Control.

Downsides:

The current version of this uses VBScript. I know, I know – 2003 called and it wants its script engine back.  I wanted to write v1 in PowerShell but I wanted to get it done more than I wanted it to be cool.  I promise that rev 2 will be in PowerShell.  This will allow the deploy scripts to really be just the config data on top and a reference to a shared ps1 file for the actual code.  The current incarnation unfortunately requires copying and pasting the main VBS body between files and that is obviously poor practice.

Next Steps:

I need some help with ideas for this and I need some help testing this out.  If you are interested and you support multiple environments/multiple servers/multiple developers and have your SSIS packages in some sort of source control system, please send me a PM on the SQLServerCentral.com forums (my handle is nycdotnet).

http://www.sqlservercentral.com/Forums/UserInfo680048.aspx

Of course this could completely crash and burn your SSIS environments so please only reach out if you’re really OK with some buggy code or issues with testing.  Thanks!

How to determine the current SSIS runtime environment

If you want to test if you’re currently debugging an SSIS package via BIDS/Visual Studio, or running normally via DTExec, you can use the following utility functions inside a Script task.

    Public Function SSIS_IsBIDS() As Boolean

        Return (SSIS_Environment() = "DtsDebugHost")

    End Function

    Public Function SSIS_IsDTExec() As Boolean

        Return (SSIS_Environment() = "DTExec")

    End Function

    Public Function SSIS_Environment() As String

        Return System.Diagnostics.Process.GetCurrentProcess.ProcessName.ToString

    End Function