My husband Sean (you may’ve heard of him, or of his hair) gives an ongoing Ground Zero SSIS class each month at the North Texas SQL Server User Group meeting in Dallas. I really enjoy these sessions; not only is he innovative in bringing a free and continuous course to the community, but I’ve always really admired his take on teaching. He doesn’t present textbook “how to”s in his sessions, but instead mentors you on everything you should know about the subject. Instead of “How to Create a Package in Three Steps”, he walks through the process and points out good practices, behind-the-scenes information and history, all while giving out swag and making us laugh.
What can I say? I like the guy’s style.
Here are the past episodes of Ground Zero SSIS:
- Ground Zero Class 1
- Ground Zero Class 2
- Ground Zero Class 3
- Ground Zero Class 4 (to be posted next week)
- Ground Zero Class 5 (to be posted by July 12)
Last night was episode five, and I thought I’d share my notes. These are a little rough, but there are some good points, and it’ll all make better sense once the video is published:
SSIS Ground Zero, Episode Five
We have an SSIS package with a stored procedure (SP) call; it passes a variable (set = 50) as a parameter. This is one of those scenarios where you want to genericize the package by passing it different values, for example, like running the package for one region, then another, using that variable. You see that a lot, like if you’re using trace data, you can store the server name in a variable (to be a parameter to the SP). Same package on different boxes, parameterized for simplicity.
MAJOR POINT: SSIS only looks at the first return query in the SP to get the data binding metadata. in other words,
IF x > 100
SELECT id, col1 FROM TABLE
ELSE
SELECT id, col1, col2 FROM TABLE
…will cause a problem when the ELSE is in effect, because the package was only expecting two columns (based on the first query).
You can get around this in a few ways, one is: split the SPs into two SPs, one for over 100 and one for under; call the right SP from your package dependent on your value in the package. That’s not always an option, so best to make sure the resultset is the same (column-wise) in each of the return queries.
Note to self (but blogged on purpose) : Did I get that right? SSIS doesn’t like an initial select into a temp table in your SP? If so, the way to get around that is a dummy query. Or potentially set nocount on…that needs testing.
Deployment
We talk about encrypting sensitive data in the package. In Package Properties, ProtectionLevel: If you’re running your DB connections with a SQL user name and the password is in your connection manager, that’s what’ll be encrypted in the file. Data protection API that comes from Windows .NET, SSIS doesn’t do anything special. If you’re connecting with a windows account, it’s all irrelevant because there is no sensitive data.
- dont save sensitive– doesn’t save any of your passwords/anything sensitive; pw must be in config file. In order to run the package (not open it) you’ll . That’s only IF you have sensitive data. You’ll use this the most.
- encrypt sensitive with user key – calls DP API like before, but uses a formula based on your user profile and the box…in order to run this you have to be the same user on the same box, or it won’t run. Some exceptions, but it’s not solid. your profile on that box
- (encrypt sensitive data in 2005 was encrypting by machine key, not dependent on user.)
- Others…
In the config file, you can mark your own data as sensitive; like if you want to store a certain code as sensitive, you can mark it sensitive so it’ll be sensitive. It’s nice that you can customize what you consider “sensitive”, instead of MS just saying “only passwords are considered sensitive and encryptable”.
Aside: Why would you not want to run your packages with Windows authentication? If you’re crossing domains, connecting to Oracle, to a legacy system, etc.
If you’re running your DB connections with a SQL user name and the password is in your connection manager, that’s what’ll be encrypted in the file. Data protection API that comes from Windows .NET, SSIS doesn’t do anything special. If you’re connecting with a windows account, it’s all irrelevant because there is no sensitive data.
Store the config loacation in an environment variable: solves the problem of moving a package from one server to another. With “Specify configuration settings directly”, you’re locked in to a specific file name. With environment variable, you can change that in the call.
“Select Properties to Export”
What should you put in a config file? At a bare minimum, the ConnectionString (Connection Managers -> [package name] -> ConnectionString).
Under Project properties, deployment utility , CreateDeploymentUtility = True. Creates a manifest file on build; dbl click on it to get the deployment wizard. Project Folder\bin\Deployment\xyz.SSISDeploymentManifest (This is just an XML file, btw).
On deployment: “Installation Folder” is where you want the config file to be dropped.
Like I said, a little cryptic. But this might spur discussion for those who weren’t there, and jog memories for those who were. Watch this space for the video! It’ll be out before next month’s user group meeting.
Happy days,
Jen McCown
Pingback: Tweets that mention SQL Awesomesauce » Blog Archive » Ground Zero SSIS at NTSSUG -- Topsy.com
Pingback: SQL Awesomesauce » Blog Archive » SSIS Ground Zero at NTSSUG – #6