I’m mentally preparing myself for a whole slew of upcoming conferences / speaking engagements over the next month and a half. I love talking about SQL Server technologies, and I’m honored that I’ll have so many chances to do it in the next few weeks!
Please come see me at one (or more) of the following events:
Yes, yes you can. Scripted or automated deployment can be done in a number of ways in SQL Server 2012.
Integration Services Deployment Wizard
Yes, this is the primary UI tool for SSIS project deployment, but it can also be run silently on the command line. When you run through the wizard, the Review page will actually list all of the parameters you need to do the same deployment from the command line.
Run ISDeploymentWizard.exe /? from a command prompt, and you’ll see the full list of arguments it supports.
<img width="619" height="234" title="image" style="margin: 5px; border: 0px currentcolor; display: inline; background-image: none;" alt="image" src="https://www.mattmasson.com/wp-content/uploads/2013/05/image2.png" border="0" />
SSIS Catalog T-SQL API
The SSISDB [catalog] schema has a number of public stored procedures, including one that can be used for deployment. We even provide samples on how to use it in Books Online.
DECLARE @ProjectBinary as varbinary(max)
DECLARE @operation_id as bigint
Set @ProjectBinary = (SELECT * FROM OPENROWSET(BULK 'MyProj.ispac', SINGLE_BLOB) as BinaryData)
Exec catalog.deploy_project @folder_name = 'MyFolder', @project_name = 'MyProj', @Project_Stream = @ProjectBinary, @operation_id = @operation_id out
All SSIS Catalog operations can be automated through PowerShell. I previously blogged about a deployment script I use to setup my demos.
The SSIS Catalog management object model (MOM) exposes a set of SMO classes you can use to code your own catalog utilities. You’ll want to use the CatalogFolder.DeployProject method to do the actual deployment. If SMO or .NET isn’t your thing, you can also code a custom utility which interacts directly with the T-SQL API.
I put this together for an SSIS performance presentation I did a while ago, and thought it warranted its own post.
In addition to looking for appropriate Design Patterns, I have a checklist of things to look for when trying to optimize an SSIS Data Flow. This list is by no means extensive, but it contains some of the more common causes of performance issues in the packages I’ve worked with.
OLE DB command operates on a row by row basis. Stage the data and perform the operation in a batch mode using the Execute SQL Task instead.
Learn MERGE, and love it.
Sometimes you do. Most of the time you don’t. If you do need them (i.e. you are loading from a flat file, and need to do a merge), considering maxing out the DefaultBufferMaxRows setting so you can sort more data in memory (sort will keep at most 5 active buffers in memory before it starts swapping to disk).
- Lookup Transforms
- Can the lookup be avoided?
- Are you using the right cache mode?
- Should you use a Cache Connection Manager?
Lots of related lookup posts can be found here.
Have fun optimizing!
Andy Leonard will be joining me to present a Developing Extensions for SSIS webcast on May 22nd. It’s been a long time since I’ve had the chance to present a code heavy session like this, and I’m really looking forward to it. I’ll be using this session as a dry run for a talk I’ll be giving at the DevTeach conference in Toronto the following week, so feedback will be much appreciated.
Andy has a great blog series about creating custom SSIS Tasks, and his company (Linchpin People) recently released an SSIS Event Task. I plan on expanding on the content in Andy’s blog posts a little bit, and talk about some of the key things you need to know when building an SSIS Data Flow component.
The abstract for the talk is available below, and registration page can be found here.
Join Matt Masson and Andy Leonard for a discussion and demonstrations on extending SSIS with custom tasks and data flow components. This code heavy session walks you through the creation of a custom SQL Server Integration Services (SSIS) Task, and a custom Data Flow component. If you’ve ever thought of customizing SSIS functionality beyond what you can do with scripting, this talk is for you! All examples will be in coded in C#, and made available following the session.
Similar to localizing a task, you can localize the display name and description of a custom connection manager by setting the LocalizationType property of the DtsConnection attribute. SSIS will look for two properties on the class:
These must be public, static string properties on your LocalizationType class.
The code looks like this:
[DtsConnection(ConnectionType = "MYCONNECTION", LocalizationType = typeof(Strings))]
public class MyConnectionManager : ConnectionManagerBase