More on Package Configurations

Douglas Laudenschlager from the SSIS documentation team has posted an excellent article to his unofficial SSIS blog on how package configurations are applied in SQL Server 2005 and SQL Server 2008 Integration services. Check it out here.

I don’t often post on my blog simply to reference someone else’s original work, but I wanted to do it today because:

• Package configurations are a vitally important feature that are often misunderstood.
• The manner in which configurations are applied has changed between SQL Server 2005 and SQL Server 2008.
• These changes can cause changes in package behavior when executing packages with specific DTEXEC options

So if you are moving from SQL Server 2005 to SQL Server 2008, definitely be sure to read about these changes.

SSIS and the Package ProtectionLevel Property

This is another one of those “I could have sworn I blogged on this years ago” posts.[1] I was looking for something I’d already written that discussed the ProtectionLevel property of the SSIS Package, but search as I might I could not find it. So I guess it’s time to write it, eh?

(As a note for the lazy reader, the moral of the story is that you should use the DontSaveSensitive ProtectionLevel for your packages, and use package configurations to apply the sensitive property values to your packages. Feel free to read the next five pages, but it all boils down to this.)

First of all, let’s start off by reviewing the property itself. Take a look at the properties of any SSIS package and you’ll see it there:

Notice the description there at the bottom of the Properties window? “Specifies the protection mode and the protection method of the package.” Hey – that’s pretty self-explanatory, so maybe we don’t need a blog post after all. Right?

Maybe not.

Before we continue, why not take a look at what SQL Server Books Online (BOL) has to say about this property?

Ok, so you’ve read the docs. Now let’s review:

• Component developers get to specify which properties of their components are considered sensitive.
• For the built-in components, this includes the password property of connection managers, some system variables and the XML nodes in the underlying DTSX file that store this information.
• SSIS users (that means us, the package developers) cannot control what is considered sensitive.

Why does this matter?

Well, SSIS is designed to be secure. One aspect of this is that any “sensitive” information that is part of your package (think about it – many packages include information like passwords in connection strings for connecting to databases and legacy systems where valuable data is stored) needs to be protected. And by protected, we mean never, ever stored in clear text.

That’s right, donkey – never, ever.[3]

But wait, you say – I’ve built many SSIS packages and I’ve never been prompted to encrypt any sensitive anything. How am I supposed to believe you?

Good question. Like most developers[4], the fine members of the SSIS product group wanted to avoid irritating their users.[5] Because of this, they wanted to make protecting sensitive data seamless, even transparent. So they did. Take another look at that screen shot of the Properties window above. Do you notice the EncryptSensitiveWithUserKey option? That’s the default.

This means that, by default, SSIS uses the Data Protection API (DPAPI) to encrypt any sensitive data with the user key of the user who saves the package. Every Windows user has a user key, so this is pretty straightforward. Then, when the user goes to open the package again, the encrypted data is automatically decrypted without the user needing to do anything. Pretty slick, eh?

Yep. Pretty slick. Especially for SSIS developers who live in a world where they never work in teams and never have their packages run in an unattended batch job that uses a service account.

Unfortunately, I don’t live in that world, and neither do many of the SSIS developers I know.

Ok, let’s be fair. That sounded awfully critical, but it wasn’t intended to be. I generally don’t like to criticize unless I can suggest a better alternative, and in this context I don’t see a more reasonable default behavior. It just works, and it lets developers be productive with SSIS without needing to worry about manually configuring security settings.

The criticism that I have is that people just don’t seem to understand this, and that causes problems. If one developer didn’t understand it, then it’s probably his fault. But if dozens or hundreds of people have this same problem (and they do – just take a look at the SSIS forums on MSDN to see how often questions related to the ProtectionLevel property come up) then the communication about the feature hasn’t been what it could be.

So what is the problem, and how does it show up? Well, consider one of these situations:

• The package developer checks his package into source control[6] and another developer checks it out to make changes. The second developer can open the package, but gets an error saying “Failed to decrypt protected XML node "DTS:Password"” and the package will not execute.
• The package developer deploys the package to the production server and the DBA schedules it to execute via a SQL Server Agent job. When the job is run, the job step fails with an error saying “Failed to decrypt protected XML node "DTS:Password"” and the package will not execute.

I won’t go on. You probably see the trend already. With the default settings, initial development is easy, but downstream tasks are more difficult. Only the user who saves the package can edit and execute it without error. This is probably not what we want.

What about the other options? Let’s consider:

• EncryptAllWithUserKey – This option has all of the drawbacks of the default EncryptSensitiveuserKey option, but has the additional drawback of encrypting the whole darned package, so not only can no other user execute the package without re-entering the sensitive data, no other user can even open the package. Probably not what you want.
• EncryptSensitiveWithPassword – This option replies on the package developer entering a password for the package, and then entering that password every time the package is opened or executed. Um… right. I don’t know about you, but my real-world SSIS projects tend to involve many dozens of packages. And I typically have a dozen or more open in Visual Studio at any given time. So picture me staggering to my desk on a blurry Monday morning, desperately clutching my mug of coffee, and launching Visual Studio. Visual Studio loves me, so it has remembered the packages I had open when I shut down for the weekend, and in a display of digital affection it re-opens them all.[7] Now picture the look on my face as I am prompted to enter the &^%$%@(* package $#^%#& password for every &@%$(^@ package as it opens. (And for that matter, picture the look on  the faces of the poor people from accounting down the hall as they run for the fire escape.) And to make matters worse, this option pretty much negates the security it’s designed to provide, because every developer on the team needs to know the package password, and at least one developer[8] will put that password on his white board or on a Post-It Note.
• EncryptAllWithPassword – Just when you thought it was safe to go back into the Properties window, here comes an option which combines the worst of all worlds. Not only do you have to enter the &%#*& password every time you open the package, the whole package contents are also encrypted so if you forget the password[9] you’re pretty much SOL[10].

So what does that leave us? That leaves us with two options. The first is the one that I use and advocate: the DontSaveSensitive option.

This option, like the others we’ve seen so far, is pretty much self-describing. It protects the sensitive data by simply not saving it in the package file. What’s not to like? No password, no user keys, everyone can open the file without error. It’s a little slice of perfection in our imperfect world!

Maybe.

Maybe not.

The difficulty, of course, comes in when you actually want to execute the package. The package can’t run if it doesn’t have a valid connection string to connect to that legacy database server from which it extracts data, now can it? So where does the sensitive data get stored, if not in the package? The answer is that we, the package developer, need to store that sensitive data in package configurations. This could be XML config files, it could be a SQL Server database, or the Windows Registry – there are many options. And each one of them can be secured in its own way, through database permissions, NTFS permissions or Registry ACLs.

The problem that some developers have with this is that it doesn’t let them get down to doing what they want to do the most, which is build their packages. And what do we say to those developers?

We say “tough luck!”

We don’t have much sympathy for those developers, because they’re missing the big picture. Yes, it can slow things down up front to have to add configurations to your packages, but this saves a ton of time later on. Configurations are a required feature of packages that will need to get deployed to multiple environments (and unless your packages will only ever be run by you on your machine, then this means your packages) and by adding them right at the beginning of package development you will have a much greater chance of having a successful deployment than if you wait until you start deploying to start thinking about deployment.

So this is what I recommend that SSIS developers do when building their packages:

• Set the ProtectionLevel property to DontSaveSensitive
• Add a package configuration for each connection string or other sensitive property in the package.
• Test everything and make sure that it works.
• Save the package and use it as a template package moving forward.[11]

Ahhh… life is good. Life is so much simpler when you have prescriptive guidance.

But wait… What about that last option. What about ServerStorage? Should we ever use that one?

ServerStorage can only be used when deploying packages into a SQL Server database[12]. If you deploy to SQL, then you can rely on the database to protect your sensitive data. But as you probably know, this doesn’t apply to package development. Visual Studio can only work with DTSX files on the file system – it cannot open packages from or save packages to SQL Server. This means that while ServerStorage is a viable option for once package development is complete, but not during development. During development you want to use DontSaveSensitive, even if you are planning to deploy to SQL.

So there you have it. I’ve spent five pages[13] saying “use the DontSaveSensitive package ProtectionLevel option when you develop SSIS packages.” But even though it took longer to read, hopefully you’ll have a better idea of why you should choose this option than if I’d just come out and told you…

 

[1] Just as a side note, re-read the subject line. Doesn’t it sound like the name of a children’s story? It was all that I could do to not write this post as an AeSISSop’s Fable or modern cautionary tale[2].

[2] I also started putting it to the tune of the Gilligan’s Island theme before i caught myself. I haven’t been feeling well lately, you see…

[3] Say it in your best Shrek voice and it may make sense. Especially in the fable context.

[4] Read the rest of the sentence and then have a good laugh with me, ok?

[5] Wait, then wipe the tears from your eyes, and then start reading again.

[6] And we all use source control, right?

[7] Awww….

[8] You know who I’m talking about.

[9] And can’t find your way down the hall to Bob’s white board.

[10] That’s “Server Offline” to you.

[11] There will likely be other things that need to be set up in your template package too, but this is a good start.

[12] I personally prefer file system deployments and so do most of the SSIS professionals I know. Check out this post from Jamie Thompson where he discusses this topic, and make sure you also click on the link in the top of his post too.

[13] And if memory serves me, set a new record for the number of footnotes in a single blog post.

Choosing the "Right" SSIS Configuration Location

One of the questions I get asked quite often boils down to this:

"Should I store my SSIS configuration data in SQL Server or in XML configuration files?"

And my every-so-helpful answer usually is:

"Yes!"[1]

That's right - very accurate, but not very useful.

Of course, for this question isn't quite as simple as it might seem at first glance, and there really is no single answer that is both generally accurate (like mine is) and useful (which mine is not) at the same time. The truly useful answer is more complex, because it relies on the context of the question more than it relies on the question itself. There is no "best" or "correct" or "right" choice for configuration storage in general - it all depends on the packages, the project and the project team.

What does this mean? I like to summarize it something like this:

• If you are using a file system deployment, it probably makes more sense to use XML configuration files.
• If you are using a SQL Server deployment, it probably makes more sense to use SQL Server configurations.
• If your ETL solution is managed by the application owner or server administrator, it probably makes more sense to use XML configuration files.
• If your ETL solution is managed by the database administrator, it probably makes more sense to use SQL Server configurations.
• If project team members and/or administrators have past experience and success with a given configuration type, it probably makes sense to use that type unless there is some compelling project-specific reason to do otherwise.

Notice that there's nothing in this list about the capabilities of the different configuration types. I've never found a problem that could be solved with one configuration type that could not also be solved with the other.

Of course, there are differences in the approaches that you use when selecting and implementing a configuration strategy for an SSIS application. And it's not always obvious how to get started, or where to go one you do get started. Fortunately, there are quite a few resources available to help make the right decision for you and to implement your configuration strategy correctly the first time. In particular, there is a small set of MSDN community articles[2] written by SSIS-focused SQL Server MVPs about SSIS configurations. Take a look at these:

• Best Practices for Integration Services Configurations by Jamie Thomson
• Defining a Configuration Approach for Integration Services Packages by John Welch
• Understanding Integration Services Package Configurations by Rafael Salas
• Reusing Connections with Data Sources and Configurations by Matthew Roche

The articles are listed in the order of "most likely valuable" so if you don't have time to read all four, you should read the top ones first.

Check it out, and good luck!

[1] I blame my mother for this. My childhood was filled with conversations that went like this: "Are we having chicken for dinner, or fish?" "Yes!" "Um..." ;-)

[2] There is an interesting story behind these articles. Shortly before SQL Server 2008 shipped, the SSIS team asked a bunch of MVPs if they would like to write SSIS articles for MSDN. There was no guidance or suggestions provided on what topics these articles should cover. But all but one of the MVPs who provided articles independently chose to write about some aspect of configurations. To me this reinforces my belief that configurations are both one of the most important and least understood features in all of SSIS.

New SSIS Article Online on MSDN

I have a new article on the SSIS Developer Center on MSDN, focusing on Data Sources and Configurations as tools for connection reuse across sets of SSIS packages. I wrote it months ago (looking at it now it seems even longer[1]) but it just made it online today.

Check it out here: http://msdn.microsoft.com/en-us/library/cc671619.aspx

This is actually one of a set of articles by SSIS-focused SQL Server MVPs that will be published over the next week of so - I just could not wait to let people know that it was out there. I'll post links to all of the articles once they're all online (and I probably won't be the only one to do so) but for now you can get started with this one. Enjoy!

[1] For example, the "about the author" blurb at the bottom lists me as still working at Configuresoft, even though I have not been working with Configuresoft since the end of July.

SSIS Deployment at TechEd Online

The third and final "Tech*Talk" video interview I recorded during Microsoft's TechEd conference this June in Orlando is now online. I talked with INETA board member Mark Rosenberg about SQL Server Integration Services deployment. You can check it out on the TechEd Online video library site in various formats:

• WMV low quality video
• WMV high quality video
• MP4 high quality video
• MP3 audio only

Enjoy!

BIDS Helper and SSIS Deployment

Back in November I posted about the 1.2 release of the amazing BIDS Helper project on CodePlex and the new SSIS-related features it included. Well, the folks who contribute to the BIDS Helper project have been hard at work getting ready for their 1.3 release, and today they checked into TFS the first set of features designed to provide real-world deployment capabilities from within the Visual Studio (BIDS) development environment.

Now, not only can you create the standard deployment manifest[1] you can also deploy automatically and (here's the exciting part!) have BIDS Helper automatically create a batch file that calls DTUTIL each time you build your project.

How cool is that!

The reason I'm so excited about low-tech batch files is that they are incredibly simple to integrate into any deployment process, and they're completely hands-free. They may not be sexy, but they're close to perfect nonetheless.

Take a look here for more information: http://www.codeplex.com/bidshelper/Wiki/View.aspx?title=Deploy SSIS Packages

The bad news is that version 1.3 is not yet ready for release, so if you want to start taking advantage of this functionality you will need to download the BIDS Helper source code and compile it yourself. Which I am now about to do...

[1] Which seems like a good start, but quickly becomes a dead-end, because it is dependent on a user clicking and typing every time it is used, which makes it very undesirable in a real-world deployment scenario.

SSIS and SQL Server Agent - Choosing the Right Job Step Type

My "SSIS and SQL Server Agent" post is the most frequently accessed post on my blog, or at least it has been for the two months that I've actually been tracking site usage. But some recent posts on the SSIS Forums on MSDN have made me realize that it was probably long overdue for a follow-up focused choosing the correct job step type.

Microsoft has provided two different SQL Server Agent job step  types that you can use to execute SQL Server Integration Services Packages:

1. The "SQL Server Integration Services Package" job step type
2. The "Operating system (CmdExec)" job step type.

Now which one leaps out at you as being ideal for running SQL Server Integration Services packages?

Unfortunately, despite the name and the oh-so-friendly GUI through which you can specify all sorts of options, the SQL Server Integration Services Package job step type is not the one you want to use. There are quite a few reasons I believe this (and it's worth pointing out that while there are people who agree with me, there are some very talented SSIS people out there who do not) including:

• Consistency: You can create a batch file that calls DTEXEC, test that execution outside of SQL Server Agent, and then copy and paste the DTEXEC command line into the SQL Server Agent job. This minimizes the number of environmental variables[1] that change between testing package execution outside of SQL Server Agent and testing package execution within a SQL Server Agent job.
• Discoverability: I realize that this won't apply to everyone, but I personally find it much simpler to look at command line switches and options than to look at nine different tabs in a tabbed window. Looking at the command line I know that I'm seeing everything. Looking through the tabs, I'm pretty sure I'm going to miss something.
• Troubleshooting and Error Output: This is the big one. When you run a package through the SQL Server Integration Services Package job step type, you're limited to the error logging that is already configured within the package itself, and this isn't always sufficient to diagnose and resolve problems with the job. But when you execute a package through DTEXEX and the the CmdExec job step type, you have more options that make tracking down and resolving problems much simpler.

So how do you do it? And do you really want to, if you're not one of those weird people who would rather have a command line than a GUI?

Well, the answer to the second question here is yes. Yes you do. Because even though you're using the CmdExec job step type, you still have a GUI to use. And here's how.

1. Double-click on the package you want to run.[2] This will open up DTEXECUI, the Execute Package Utility.
2. In DTEXECUI, specify all of the options that you need by using its nice friendly GUI.
3. On the Command Line tab in DTEXECUI, highlight and copy the Command line text - these are the switches and options that need to be specified for DTEXEC to duplicate the options you selected in the GUI:
   
   
   
4. In you SQL Server Agent job, add a new job step, and specify the job step type as CmdExec. In the Command text box, type DTEXEC and a space, and then paste the command line text that you copied from DTEXECUI earlier[3]:
   
   
   
5. On the advanced tab of the New Job Step dialog box, enter a file path and name in the Output File text box. (This is the big thing.) With this specified, the output from DTEXEC, which is exactly what you want when troubleshooting problems, will be written to a text file. You can also specify whether the file should be overwritten or appended to with each execution.
   
   

Now, when you execute your SQL Server Agent job, all DTEXEC output will be written to this text file. And if something goes wrong, you now have a single, easy to use location for tracking down and resolving the problems.

One of the most common things that the MVPs and other people who help out on the SSIS forums request is "the complete set of error messages and warnings from the package execution."[4] The reason we ask for this is that it's pretty much impossible to remotely troubleshoot someone else's problems without complete and explicit output. And the greatest thing about using the CmdExec job step type is that all of this information is now in one place. Copy and paste the errors and warnings, and help is (hopefully) on your way.

 

[1] Not Windows environment variables - I'm talking here about "things that are important to your deployment environment."

[2] If your package is stored in SQL Server and not on the file system, you can still use this basic technique, but you will have to browse to and select the package from within DTEXECUI, as opposed to double-clicking on the package on the file system.

[3] You can also specify the fully qualified path to DTEXEC.EXE - this is helpful if DTEXEC is not on the path, or if you're on a 64-bit server and want to explicitly select the 32- or 64-bit version of DTEXEC for this package.

[4] And we're not the only ones who ask for this. Your in-house support folks will probably ask for the same thing if you have a support team who works with SSIS.

SSIS on 64-Bit Windows

I've just returned home from the TechEd 2008 conference in Orlando - what an amazing two weeks this was. Even though the conference ended only yesterday, it already seems vaguely unreal, as if it were too much fun to have been real.[1]

But while I was having fun at TechEd, members of the SSIS team were hard at work[2] writing about one of my favorite topics: SSIS deployment. And since 64-bit deployments was such a large portion of my second breakout session, it seems like synchronicity[4] that both Douglas Laudenschlager and Matt Masson both blogged on the topic as well in the last few days. Check here to see Douglas' excellent post about considerations when using SSIS on 64-bit machines, and here to see Matt's follow-up about how the SQL Server Integration Services job step type in SQL Server Agent (just introduced in SQL Server 2008 RCO!) now provides the option to use the 32-bit SSIS runtime. That's pretty cool. I doubt I'll be using this and giving up DTEXEC any time soon, but it's a good step forward for 64-bit deployments.

Side note: Expect another post or two about SSIS deployment in the next few days. I realized on the flight home that I neglected to mention a few important considerations during that breakout session, so once I've had the time to remind my family who I am, I'll fill in the gaps here. Stay tuned!

 

[1] Although I am pretty sure it really was real. Otherwise I'll need to come up with an alternate theory about where all these t-shirts came from.

[2] Now I know why I didn't see these guys at the show...

[3] BIN-450: SQL Server Integration Services Deployment Best Practices

[4] Or perhaps some other Police song

SSIS, SQL Server Agent and WMI

I need to hang out with Donald Farmer more often. He knows more about SSIS than I will ever forget - or something like that.

Earlier today I co-presented another interactive theater session (largely a repeat of the session we did last week during the developer conference, but with some new content and a different vibe) with Donald and (as before) I learned a few things about using SSIS that I had never seen before. One of them has to do with one of the most popular[1] SSIS topics: SSIS and SQL Server Agent.

This lesson doesn't actually have much to do with SSIS - it has to do with how you trigger the execution of a SQL Server Agent Job. Normally SQL Server Agent Jobs are executed on a schedule, so that the same packages run at the same time every day. To be honest, this is the only way I have ever used SQL Server Agent. But (as I learned today) you can also configure your SQL Server Agent Jobs to be triggered by Windows Management Instrumentation (WMI) events as well.

What does this mean?

It means that any events that are raised by the operating system (or other software that uses WMI) can be used to trigger the execution of your job and your packages. Think about having packages that automatically run when your processor utilization drops below a specific threshold as an example. The possibilities are endless[2] and the setup is documented in SQL Server Books Online here: http://technet.microsoft.com/en-us/library/ms366332.aspx

Enjoy!

 

[1] At least if the web traffic reports for my blog are to be believed.

[2] This is a nice way of saying that I don't know enough about WMI to come up with more examples without making a fool of myself.

Another Reason to Avoid Data Sources in SSIS

I've often railed on the evils of Data Sources - the .ds files that you can add to your SSIS projects in Visual Studio. I tell people to avoid them whenever I'm talking about SSIS deployment, and I even gave them a mention in my SSIS Best Practices blog post last year:

"Don’t use Data Sources: No, I don't mean data source components. I mean the .ds files that you can add to your SSIS projects in Visual Studio in the "Data Sources" node that is there in every SSIS project you create. Remember that Data Sources are not a feature of SSIS - they are a feature of Visual Studio, and this is a significant difference. Instead, use package configurations to store the connection string for the connection managers in your packages. This will be the best road forward for a smooth deployment story, whereas using Data Sources is a dead-end road. To nowhere."

And now there is even one more reason (as if there weren't enough already) to avoid them: they're going to complicate your package upgrade path from SQL Server 2005 to SQL Server 2008. This is due in part to the fact that the SQL Native Client provider name has changed from SQLNCLI to SQLNCLI10 in SQL Server 2008. The SSIS package upgrade wizard will take care of this in your packages and update your connection managers appropriately. But if you're using Data Sources in your project, they will not be changed, so when you next open your properly upgraded packages the new (and correct) connection strings will be overwritten with the old (and incorrect) values and the packages will fail.

And that's not what we want, is it?

So save yourself some pain and go with Package Configurations[1] instead, ok?

 

[1] In the context of full disclosure, I honestly don't know if the upgrade wizard is intelligent enough to find and fix connection strings in package configurations either, but they're still the better choice. ;-)

SSIS and SQL Server Agent

SQL Server Integration Services is great. But SSIS deployment can sometimes be painful. One of the things that makes it painful is that SSIS is built on a relatively complex set of technologies (like SQL Server, Windows Server and Active Directory[1], for starters) and when you go to deploy your ETL solution, you need to have a pretty good understanding of how those technologies work - and how they work together - or else you could well be in for a rough ride.

Nowhere is this more evident than when it comes to deploying SSIS packages to run via a scheduled SQL Server Agent job. I wish I had a dollar for every time someone posted this message on the SSIS forums on MSDN:

"I built my package and it works great, but when I deploy it to SQL Server Agent it fails. Is this a bug in SSIS?"

Yes, I blogged on this back in May. But I've seen this same problem in one form or another at least a dozen times this week on the forums, so I wanted to post on the same topic again. It's important. And although no one part of it is really complex or difficult, there are enough parts that come together during deployment that a lot of people are really struggling with it, despite the volume of documentation and other information that's available online. Hopefully this post will help, at least a little.

Here's the deal [2]:

• When you run a program in Windows, that running process has your permissions. This is why Microsoft Word can save files to your user drive, when only you have permissions to access that folder. Each running process has the access permissions of the user account that launched it.
• When you run a program from a SQL Server Agent job, that running process has the permissions of the execution context configured for the job step. By default this is the SQL Server Agent service account, but it can also be a Proxy account, depending on how the job step was configured.
• By default, the SQL Server Agent service runs in the security context of the Local System account. This means that it has lots of privileges on the local computer, but none whatsoever on the network.

See the problem? Nine SSIS packages out of 10 need to access remote resources. These could be SQL Server databases (remember how we use Windows Authentication whenever we can?) or text files on a file server, or an Access database (MDB file) on a file server, or any number of other things. SSIS packages generally exist to move data from one place to another place, and generally those places aren't all on the same machine, so the packages need to execute in the security context of a user account that can access all of those resources on all of the machines involved.

Please let me repeat/rephrase that, because it's the really important thing in this post.

"SSIS packages need to execute in the security context of a user account that can access all resources on all of the machines involved in the package processing."

What does this mean to you?

It means that when you run the package yourself, the package has your permissions. It can do what you can do and access what you can access, for the simple reason that you ran it. And if you didn't have permission to access all of those resources when the project started, you discovered this problem early on, and got the access you needed because otherwise you couldn't have built the package at all in the first place.

So of course it runs for you!

But what about that SQL Server Agent job?

It should be pretty easy to figure out from here, right?

1. You need to create (or work with the people in charge of your network to get created for you) a Windows user account[3] that has all of the permissions necessary to access the resources your package uses, databases, file system folders, shared folders and so on.
2. Then you create a SQL Server Agent Proxy based on that Windows user account.
3. And then you tell SQL Server Agent to run your job step in the context of this proxy.

One, two, three, Go!

That's really how simple it is. And the craziest part of all? Microsoft has already documented the heck out of this online. Here are a few key links that will go into much more depth than I have here, so if you're still having problems, why not click on them and read what Microsoft has to say.

• From SQL Server Books Online, "How to Run a Package Using a SQL Server Agent Job": http://technet.microsoft.com/en-us/library/ms139805.aspx. (Please note that this page has links to sub-articles and related articles. Click on them and read those pages too.)
• From the Microsoft Support Knowledge Base, "An SSIS package does not run when you call the SSIS package from a SQL Server Agent job step": http://support.microsoft.com/kb/918760. This article goes into many scenarios that could cause an SSIS package to fail when executed in a SQL Server Agent job, not just the security related stuff in this blog post, but from my experience, it's the security that is tripping up most people.

Are you ready to go? I sure hope so. Hopefully the next time someone runs into this problem I can simply point them to this URL instead of having to type up this information each time. And maybe, just maybe, someone will search and find this article before they cry for help on the forums.[4] And if that happens, it will all be worth it, and these blisters on my fingers won't seem to hurt at all anymore... ;-)

[1] No, SSIS isn't really "built on" AD, but read on and you'll see what I mean.
[2] Yes, I know I'm simplifying things quite a bit here, but that's intentional.
[3] If you have Active Directory, this needs to be a domain account. If not, then you have a bunch of reading to do on Windows security, because I'm not going there today.
[4] If you are that person, please post a comment to let me know.

Last Night's Deployment Slides Online

I had a great time last night in Rochester at the Visual Developers of Upstate New York users group meeting. I delivered a session on SSIS Deployment, based on requests from the organizer of the users group, who attended my ACM presentation back in October. Deployment is not one of SSIS's strongest points, so I knew we'd have a lot of fun. We had a great turnout and some killer questions, and everyone had a great time.

I've put the slide deck from last night's presentation online - you can get it here.

There's not a lot of "meat" in the slides (this is probably why I was actually able to finish on time, which is pretty much unheard of when I only have 90 minutes to work with) but quite a few people asked for them, so here they are. If you have any questions, comments or criticisms, please be sure to post them here.