I gathered recently with some friends who are geeks to discuss geeky stuff. As is often the case, there were a lot of “what if” questions, and we answered most of them by firing up laptops and VMs for testing. I had some trouble with my VM running my instance of the December preview running in Visual Studio 2015 on Windows Server 2016 Technical Preview 4. So I was unable to answer one curious question until later. When I did answer it, I grabbed some screenshots and decided to blog about it. And here we are. 🙂
The question was:
“What Happens if Your First SSIS Project Deployment is a Single-Package Deployment?”
To test, I created a new SSIS project named “DeploymentTest1” and added three simple SSIS packages. I right-clicked Package3.dtsx and clicked Deploy Package. The Integration Services Deployment Wizard started, as expected. Since I was deploying to a pristine Catalog, I created a new Catalog Folder named “Deployment”. There were no projects in my catalog, and I was curious how the Deployment Wizard would handle this.
Once the folder was created I saw a new button: “New project…”. I couldn’t click the OK button until I created a Catalog Project to contain my SSIS package:
So I created a new Catalog Project. Just to see if I could do it, I named the Catalog Project something different (“Deployment_Testing_1”:) from my SSIS project (“DeploymentTest1”):
I have to admit I was a little surprised that it worked:
Once I clicked the OK button, the Integration Services Deployment Wizard displayed a three-part Path, “/SSISDB/<FolderName>/<CatalogProjectName>” instead of the usual two-part Path (“/SSISDB/<FolderName>”):
Once deployed, Package3 appeared content, if alone, in the SSIS Catalog node of SSMS:
I wondered what might happen if I now deployed the SSIS Project from SQL Server Data Tools – Business Intelligence Edition (SSDT-BI), so I tried it. When the Integration Services Deployment Wizard reached the Select Destination step, the Catalog Project I created when deploying Package3 was remembered:
This makes perfect sense. I just didn’t know what would happen. Hence, testing.
Once the SSIS project was deployed from SSDT-BI, Package3 was no longer alone. And that was a good thing.
Still, I was curious how SSIS 2016 tracks the target Catalog Project. So Kent Bradshaw and I conducted a wee bit of testing. Here’s what we learned. There are three new attributes stored inside the SSIS 2016 version of the <SSIS Project Name>.dtproj.user file:
ServerName, PathOnServer, and Run64BitRuntime aren’t found in previous versions of the file (at least the couple we checked). Again this makes perfect sense with single-package deployment. It especially makes sense if you can must create a new Catalog Project to perform a single-package deployment first.
We also tested deploying the SSIS Project as a whole first, and SSIS 2016 appears to work just like SSIS 2012 and 2014.
Conclusion
As we chatted some more, Kent and I realized this decoupling of the SSIS Project name and the Catalog Project name has the potential to confuse developers not familiar with the new functionality. While we really like the idea of being able to deploy a single package to perform a quick fix, we realize this opens the door to heretofore unseen issues. It’s not just coupling between Catalog Projects and SSIS Projects in development, this potentially decouples Catalog Projects from source control. That has Kent and I concerned. How does one know an SSIS Project has been deployed to Production under a different Catalog Project name? We don’t know the answer to that question at this time.
For now, we’re going to suggest SSIS developers adhere to a practice of naming Catalog Projects with the same name as the SSIS Project, even though we’ve proven it’s not technically necessary. We won’t call it a “best practice” – at least not yet. But we will call it an “Andy and Kent practice.” In the event someone doesn’t adhere to our suggestion, it’s possible to update the <SSIS Project Name>.dtproj.user file and correct this for future development of the SSIS project. (We tested!) If you make this change, do yourself a favor and check it into source control right afterwards.
As always, I welcome your thoughts in the Comments (Although comments are moderated and may not appear for a day).
Thoughts?
:{>
Comments