My last two articles on SSIS have been related to bugs. That’s not because I am looking for its faults all the time. I like using SSIS – I have been since SQL Server 2005, more so in 2008 and even better in 2012. I think SSIS has limitations but also great advantages. For most of the projects that I have worked with, it simplifies quite a lot of things for me.
Matt Mason (twitter | blog) has discussed the branching/development strategies and how they fit in the revamped SQL Server 2012 SSIS in a couple of articles. Read them here:
Thoughts on Branching Strategies for SSIS Projects
Can I deploy a single SSIS package from my project to the SSIS Catalog?
So here’s my thought on the new .ispac method of deployment in SQL Server 2012, in response to the latter post by Matt.
After using SSIS in SQL Server 2012 for a moderately sized ETL for data warehouse for over 6 months, I’ve become more and more appreciative of the .ispac method of deploying. Building/deploying it, as if it was a C# project, in SSIS SQL Server 2012 makes more and more sense to me.
There has been a few seconds in the past, which I thought deploying just a single package would be useful. For example, when there is a need to import simple text files that can be done via one SSIS package on an ad hoc basis. However, the more I think about this, I think it’s not a “good” enough reason to do a single package deployment. In fact, if it gets run reasonably frequently or it may be reusable, I would create a project called “Ad hoc Import” which consists of a collection of “common” ad hoc SSIS packages. In my experience, I don’t always know how often an SSIS package to be executed at conception. It could be a one-off / throw-away package; or it could start with being a one-off and then continue to be executed every so often. More often than not, my “first thought” one-off packages have become reusable (re-runnable) that they are worthy of being maintained as a project and deployed to the Integration Services Catalogs.
I think having / forcing project deployment just makes us think twice about how we organise our packages / projects in Integration Services Catalogs; which in my opinion, is not a bad thing at all.
Also – yes – I do use version control for all my SSIS projects. I use Team Foundation Server 2010. Although with the Integration Services Catalogs in SQL Server 2012, the deployed versions information is kept, this is not good enough to replace a version control tool; especially in team development environment.
Most of the issues that I have encountered and submitted at Microsoft Connect in regards to administering and executing deployed SSIS projects have workarounds (albeit risky). So I am all for project deployment. By the way, more info on these issues:
http://connect.microsoft.com/SQLServer/feedback/details/751220/ssisdb-package-parameter-error-in-previous-version-project-lsn-carried-forward-to-the-future-deployed-versions. Also see: SSISDB Bug: Package Parameter Mapping
http://connect.microsoft.com/SQLServer/feedback/details/743735/ssisdb-catalog-get-parameter-values-with-null-able-package-name-input-parameter. Also see: Get Parameter Values from SSISDB Catalog: a bug?
I am also keen to hear what others think about a single package deployment and/or their experience with the new project deployment method in SQL Server 2012.
2 Responses
We are using S/S 2014 and doing a lot of SSIS work. We have deployed our packages into S/S and are using project and package variables. Now we want to deploy to production. I can deploy the packages but haven’t figured out how to deploy configuration variables, values and links to the package. Any thoughts?
Thank you.
Evan
Hi Evan,
Ideally you would want to use SSIS environment to configure variables.
If you want to “script out” to manage the environment variables, you can do that by inserting / modifying the internal tables in SSISDB. However I won’t recommend using this unless you are an advanced user. If you would like some pointers on this, let me know.
Hope this helps.
Julie