Lesson Two. Deploying Projects in SSIS 2012 (Today’s class is longer than I expected, we’ll take a break and finish tomorrow.)
Before we get started, a big thank you to Jorge Segarra (blog|Twitter) for inviting me to write for SQL University .
PreRequisite: Lesson One
By the end of this part of the lesson you should understand the following processes/terms.
- Building SSIS Projects
- Ispac files
- The SSIS Catalog
- Deploying ispac files
By the end of the second half of the lesson, the following will be covered:
- SSIS Catalog Environments
- SSIS Catalog Environment Variables.
- SSIS Executions
- Building Agent jobs with SSIS Catalog projects.
So you’ve got your Project with a Parent package. (by the way, you should designate Parent Packages as Entry Point packages inside a project by right clicking them and selecting “Entry Point Package”). You’ve got some child packages. You’ve got some variables. You’ve got project level parameters for values which should be available to all packages inside a project (like say, a directory for importing/exporting files, a client to process, a business unit to process, etc. ) and you’ve got package parameters for values which need to be passed from parent packages to child packages. You’re ready to Build, Deploy and Execute in SSIS 2012 utilizing all of its new toys features.
Oooo shiny new SSIS Project—Ready for Deployment
Continue reading SQL University – SSIS 2012 Deployments, Lesson Two — Part A
Lesson One, where I introduce tons of prerequisite new concepts in preparation for Deploying and Executing in Lesson Two. The main concepts and terms covered in this lesson are:
- Project Deployment Model
- Legacy Deployment Model
- Project Reference reference type –Execute Package Task
- Ispac files
- Parameters, subdivided into the two following subtypes:
- Project Parameters
- Package Parameters
It will help if you are familiar with the following concepts inside SSIS (pre 2012 versions) before attempting this course 😉
- Execute SQL Task
- Execute Package Task
- Connection Managers
- Parent/Child packages
SSIS in 2012 brings a great number of significant changes. It is the first version since its initial release with SQL Server 2005 to look significantly different. There is a more modern look and feel to be sure. However, the really big change to SSIS in 2012 is in the way in which it is deployed. There is a new method of deployment called “Project Deployment”, which has as much to do with how and where a package is deployed as it does with how a package is bundled during development. Let me go back in time a bit and explain. Continue reading SQL University– SSIS 2012: Deploying Projects –Lesson One
I was fortunate to be able to speak at the Microsoft Integration Architects monthly meeting tonight. Thanks to all who came out tonight to see the presentation! This is a great group which tackles Integration from a multi -product standpoint. I am going to be attending these meetings!
Here is a link to download the slides as pdf and a zip with the code:
I am honored to be part of the team which is bringing SQL Saturday 89 — Atlanta, GA to the community on September 17th. As an extra special treat, we are also bringing two Pre-Cons on the Friday before the Pre-Con for only $99.
Troubleshooting and Performance Tuning with Kevin Kline
Today I want to tell you why you should attend John Welch’s Pre-Con.
At $99 –This is a bargain. Some phrases become trite with overuse. Phrases like: “A Fraction of the Cost”, “Gubernatorial”, “Buy now and get this personal pizza oven”. But Google “SSIS training” and look at the prices. Also look at the fact that this is only one day off of work, and if you are local, no travel. Also, you get John Welch live and in person. You can ask him questions!
John Welch (Blog | Twitter) is completely awesome. He’s a skilled practitioner and a great speaker. He’s a Microsoft SQL Server MVP and was a contributing author to MVP Deep Dives. I learn something from John every time I see him–see this blog post for how John helped me with Error Handling in SSIS. He’s been doing this ETL stuff for a long time and he’s got more tricks for doing it quickly and properly than Jason Bacani (another team member– Blog , Twitter) has shoes (next time you see Jason, ask him how many shoes he owns and report back, it’s astounding!). There have been many times when I have been completely stumped with a problem and found the answer on John’s blog (he’s really good at working with XML in SSIS!).
Do you use BIDS Helper? John Welch worked on that project. That’s right. John Welch helped write the code that turns your connection managers blue and pink and resets your GUIDs when they are trying to ruin your life ! Wouldn’t you like to learn from the man who helped tame SSIS with BIDS Helper and other similar projects ? Shouldn’t you thank him?!!!
Survival. If the subject matter in this session is not part of your job today, it will be soon. You will always need to know how to move data from Source A to Destination B. No matter the size your organization or your relationship with said organization’s data, there is almost always a moment when you are faced with importing or exporting data. It comes with being a DBA. While there are as many ways to accomplish this as there are DBA’s out there to do it; SSIS is actually built exclusively for this purpose. It may have bulky bells and whistles, but SSIS at its core is THE tool given to us by Microsoft to move data. Shouldn’t you learn how to bend it to your will? Could there be a better opportunity? So come on people: click the button!
The brand new PASS SQL Server User Group in Daytona Beach let me present at their very first meeting July 13th. Thanks to Brook Ranne and Jim McAleer for inviting me. I debuted a presentation on the changes forthcoming in SSIS with Denali. The slides are available here. If you are in the Daytona Beach area please check this group out!
I also just wanted to share some exciting news for me. I submitted three sessions for this year’s PASS Summit. When the schedule came out, none of them made the final selection. However, this week I found out there still might be hope. PASS has put together a Community Choice Ballot of 20 sessions for you the community to vote on, and one of mine (SQL Smackdown with Aaron Nelson) made the ballot! There are five available slots we are competing for. As I always say, I would love your vote, but regardless of whom you vote for, you should let your choice be known, as there are amazing sessions on the ballot! Voting ends July 20th, so hurry! And thank you readers for your support this year, without you I’m sure I wouldn’t be on the ballot at all.
Check out the Community Choice Ballot here.
So like all bloggers, Audrey and I made a resolution to blog regularly and frequently back in November of 2010. We did well for a stretch, but we’ve had many good things happen and the blog has taken a hit. I went to the inaugural SQL Rally two weeks ago. I met for the first time a lot of great community people– Karla Landrum, Michael Blizzard, Mike and Karen Rhodes, Bill Graziano, Rick Heiges, Kendra Little, Jeremiah Peschka, Karen Lopez, Timothy Mitchell, Bradley Balls, Tom LaRock, Allen Kinsel, Mark Broadbent— I know I’m forgetting lots of names. It was wonderful to meet folks from all over the country and the world who are excited to learn about SQL Server. I also got to hang with lots of my buddies whom I only get to see at events like these.
Then it was on to Microsoft TechEd which was hosted in our home town of Atlanta, GA. Audrey and I spent some time at the PASS booth, encouraging folks to attend their local user group meetings.
Then there’s the book. See Jorge’s post here on the great news about the SQL Bible for 2011. Audrey and I will probably not be sleeping much in the next few months as we work on the Wiley SQL 2011 Bible. It’s crazy exciting!
So if we become ever so slightly less vocal here on the ol’ Datachix blog; remember, it’s not you , it’s us! And now on to our regularly scheduled blog post:
Debugging SSIS Variables Part 3 — The Watch Window
Continue reading Where have we been? and, Debugging SSIS Variables Part 3 — The Watch Window
There was this one time when I was working on a project and we were trying to load a datawarehouse. I came in one morning to find a team member staring at a failed SSIS package with this error message at a Data Flow Task, specifically coming from the query in the ole db source:
[SRC_OLEDB ] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: “Microsoft SQL Server Native Client 10.0” Hresult: 0x80004005 Description: “Internal Query Processor Error: The query processor encountered an unexpected error during execution.”.
Continue reading The Vaguest Error Message I Ever Saw in Integration Services