Search results matching tag 'SSIS'http://sqlblog.com/search/SearchResults.aspx?o=DateDescending&tag=SSIS&orTags=0Search results matching tag 'SSIS'en-USCommunityServer 2.1 SP2 (Build: 61129.1)Software Economics and Testinghttp://sqlblog.com/blogs/andy_leonard/archive/2015/04/01/software-economics-and-testing.aspxWed, 01 Apr 2015 09:00:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:58220andyleonard<p align="left">“<em>There are two types of developers: those who test their software and those who will.</em>” – Andy, circa 2015</p> <p align="left">It’s April Fool’s Day in the US, but I’m going to act like it’s Halloween. Software testing is no joke, and not testing should scare you.</p> <p align="left">In 1996 (yes kids, years used to begin with a “1”), in the sixth issue of <a href="http://www.fastcompany.com/" target="_blank">Fast Company</a> magazine, <a href="http://www.fastcompany.com/user/charles-fishman" target="_blank">Charles Fishman</a> wrote <a href="http://www.fastcompany.com/28121/they-write-right-stuff" target="_blank">They Write the Right Stuff</a> – an article about Lockheed Martin’s On-Board Shuttle Group and the work they did writing and maintaining software for NASA’s Space Shuttle program. Some interesting “nummers” from the article:</p> <ul> <li> <div align="left">420,000 lines of code.</div> </li> <li> <div align="left">1 error in each of the previous (at the time of writing) versions.</div> </li> <li> <div align="left">The previous 11 versions of the software contained a total of 17 errors.</div> </li> <li> <div align="left">$35,000,000 / year budget.</div> </li> </ul> <p align="left">I did the math: maintaining each line of code was $83.33/year. Plus 1/3rd of a penny. In 1996 money. According to the <a href="http://www.usinflationcalculator.com/" target="_blank">US Inflation Calculator</a>, that’s equivalent to $124.12 per line-of-code per year today.</p> <p align="left">I mention the economics because I’m a consultant. I know how much I charge for my time. I have a pretty good idea what others charge for their time. I co-own <a href="http://linchpinpeople.com" target="_blank">Linchpin People</a>, and we subcontract software developers for ourselves and other companies. I know what we pay software developers and how much we charge others for their services. At the risk of disillusioning those who aspire to co-own their own technical consulting firm, we have precisely 0 clients paying us $35,000,000 per year – even in 2015 money ($35 million in 1996 would be north of $52 million in 2015).</p> <p align="left">Why not? Because that’s a lot of money to pay for software. When lives, safety, and the pride of a nation are on the line, you’re paying for more than “just software.” The value of maintaining the Space Shuttle software was worth those costs. The cost/benefit analysis was sound. </p> <p align="left">What’s the value of your software?</p> <p align="left"><strong>Some Important Questions</strong></p> <p align="left">As you ponder the “nummers,” you are likely thinking about the software in your enterprise. Or maybe just the programming-for-fun project you’re working on in your spare time. It may be prudent for you to spend some time thinking about the costs and benefits of your software. Here are some questions to get you started:</p> <ul> <li> <div align="left">How important is your software? </div> </li> <li> <div align="left">What’s at stake if your software fails?</div> </li> <li> <div align="left">What are the risks?</div> </li> <li> <div align="left">Can your enterprise tolerate your software being offline for some period of time?</div> </li> <ul> <li> <div align="left">If so, for how long?</div> </li> <li> <div align="left">Are some times worse – riskier, higher stakes – than other times?</div> </li> </ul> <li> <div align="left">What’s your backup plan if your software dies and cannot be brought back?</div> </li> <ul> <li> <div align="left">What happens if you lose Production data?</div> </li> <ul> <li> <div align="left">Even all of it?</div> </li> </ul> </ul> </ul> <p align="left">You may read that list and think, “Andy, you’re just trying to scare me.” </p> <p align="left">Guilty. </p> <p align="left">You durned right I’m trying to scare you. If fear is what it takes to wake you up to the realities of your situation, I’m not above scaring you. (In my mind, I just wrote, “I am an engineer.” But I digress…)</p> <p align="left"><strong>Process</strong></p> <p align="left">If we were an agricultural society, it would be prudent to educate you about pests, soil care, and crop rotation. If we were manufacturers (and please understand, software is <em>not</em> manufactured…), it would be good to advise you to maintain a safe and healthy work environment for those who keep the machines running and keep your machinery in working order. But we make software. </p> <p align="left">What are the corollaries? One good way to mitigate risk is to possess and practice a process.</p> <p align="left">Before I go too far in this discussion about process, I want to reiterate my belief that people build processes to help people, processes are living and subject to maturity and change, therefore, people should <em>always</em> trump process. If you find a process harming a person’s life, liberty, or pursuit of happiness; you should re-examine the process. Processes should serve people, not the other way around. That’s ground rule #1 and it’s non-negotiable.</p> <p align="left">The Lockheed Martin team practiced a process. It is enumerated in the article (did you read the <a href="http://www.fastcompany.com/28121/they-write-right-stuff" target="_blank">article</a> yet? You should. Right now, if you haven’t already. It’ll only take a few minutes…): </p> <ol> <li> <div align="left">The product is only as good as the plan for the product.</div> </li> <li> <div align="left">The best teamwork is a healthy rivalry.</div> </li> <li> <div align="left">The database is the software base.</div> </li> <li> <div align="left">Don't just fix the mistakes — fix whatever permitted the mistake in the first place.</div> </li> </ol> <p align="left">This list is rich. But you’ve read a lot already so I’m going to bring this post home by unpacking #4. </p> <p align="left"><strong>Mistakes Are Normal</strong></p> <p align="left">Even at $124 per line of code per year, mistakes happen. If you believe you can pay enough people enough money to eliminate errors in software, you are mistaken (see what I did there?). And if one of the risks you identified in the Some Important Questions section above is, “People could get hurt,” you need to do all the engineering you can to see that people do not get hurt. But even if you do everything humanly possible to prevent and mitigate mistakes, you will never eliminate mistakes. So the very first step is to realize – and <em>act</em> like you realize – mistakes are going to happen. </p> <p align="left">Want to manufacture stress? Create a culture that does not tolerate mistakes. How? Rant, rave, yell, reprimand, and fire people when they fail. </p> <p align="left">Want to deliver great software? Create a culture that tolerates mistakes. Even better, create a culture where failing fast is celebrated. Why? People learn from mistakes. People who make more mistakes learn more. People who fail faster learn faster. People who learn faster make great software.</p> <p align="left"><strong>Mitigate the Negative Effects of Mistakes</strong></p> <p align="left">Failing safely is the best way to mitigate the negative effects of mistakes, that’s why martial arts students first learn how to fall. Why? Because <strong>they’re going to fall</strong>! So are you. Identifying software errors quickly (failing fast) is a profitable practice. Testing software is the best way to identify software errors. There are some rules to software testing and the rules are important:</p> <ol> <li> <div align="left">Test in a safe environment.</div> </li> <li> <div align="left">Test realistically.</div> </li> <li> <div align="left">Do not allow developers to test their own code.</div> </li> </ol> <p align="left"><em>Test in a Safe Environment</em></p> <p align="left">Set up a virtual machine. Something. Anything – except the Production server. There are conditions that need to be tested on the Production server. But, please, do not let “we to test this in Production” be your first thought. Exhaust every other option first. Please.</p> <p align="left"><em>Test Realistically</em></p> <p align="left">The reason you sometimes need to test software in Production is that there simply isn’t another server or environment in the enterprise that looks and acts like Production. There can be sound business and economic and timing reasons for such a condition. If you cannot test realistically in a safe environment, test as realistically as you can in a safe environment before testing in Production.</p> <p align="left"><em>Do Not Allow Developers to Test Their Own Code</em></p> <p align="left">Do you want to read some stories of heartache? Some rants? Some helpful advice? <a href="https://www.google.com/webhp?sourceid=chrome-instant&amp;ion=1&amp;espv=2&amp;ie=UTF-8#q=developers%20test%20their%20own%20code" target="_blank">Search for “Developers test their own code.”</a> It will bum you out. And it may also help. Let me state up front that I develop software (SSIS is software even though contains “SQL Server” in its name). I miss things when I test my own code. Some of the links from the search above will contain stories about how and why this happens. Trust me. It happens. </p> <p align="left">Get someone other than the developer to test the code. Prepare a test plan – even if the plan is “run this script on this test server.” That’s a test plan. It’s good enough for someone who understands what the code is supposed to do, or for anyone capable of interpreting a green/red indicator. </p> <p align="left">Developers should conduct unit tests and functional tests. They should execute the code in a development environment to make sure it runs, does what they think it should do, and returns the expected results. Then someone else should run their code somewhere else to make sure all those things happen over there – in some location other than the environment where the software was built.</p> <p align="left"><strong>Conclusion</strong></p> <p align="left">Remember: All software is tested; some, intentionally. What does that mean? That means that untested code is going to be tested in Production. Sometimes, the people testing your software in Production are your soon-to-be former largest customer. Think about it. Please. If this scares you, then good: I’ve accomplished what I set out to do.</p> <p align="left">:{&gt;</p>Tabular Data Packages and a BIML challengehttp://sqlblog.com/blogs/jamie_thomson/archive/2015/03/27/tabular-data-packages-and-a-biml-challenge.aspxFri, 27 Mar 2015 20:58:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:58297jamiet<p><i>Update. The challenge set below has been&nbsp;responded to by Scott Currie who also happens to be the inventor of BIML. He has&nbsp;made his work&nbsp;&nbsp;available at </i><a href="https://github.com/bimlscript/BETDPI"><i>https://github.com/bimlscript/BETDPI</i></a></p>
<p>In February 2004 I joined a company called Conchango and had the pleasure of working with some very very smart people (Mick Horne, Dan Perrin, Pete Spencer, Paul Mcmillan, Steve Wright amongst others) who schooled me in the ways and means of building data integration solutions. One piece of IP that Conchango had revolved around a means of transporting a collection of data files in a well-known format. We called it the "Generic Feed Interface" and it broke down like this:</p>
<ul>
<li>Data was kept in CSV files. There was no limit to how many files were in a collection. </li>
<li>Each collection had two extra files, an inventory file and a metadata file. Both of these were also CSV files. </li>
<li>The inventory file listed all of the data files </li>
<li>The metadata file defined the data type of each column in each data file </li>
</ul>
<p>Here’s a very simple example of what this looked like, a collection consisting of one datafile:</p>
<p><a mce_href="http://sqlblog.com/blogs/jamie_thomson/image_26D52026.png" href="http://sqlblog.com/blogs/jamie_thomson/image_26D52026.png"><img width="244" height="97" title="image" style="border-width:0px;display:inline;" alt="image" border="0" src="http://sqlblog.com/blogs/jamie_thomson/image_thumb_182D04F2.png"></a> </p>
<p><a mce_href="http://sqlblog.com/blogs/jamie_thomson/image_754EA9C3.png" href="http://sqlblog.com/blogs/jamie_thomson/image_754EA9C3.png"><img width="244" height="78" title="image" style="border-width:0px;display:inline;" alt="image" border="0" src="http://sqlblog.com/blogs/jamie_thomson/image_thumb_5D4D5A0C.png"></a> </p>
<p><a mce_href="http://sqlblog.com/blogs/jamie_thomson/image_1A711592.png" href="http://sqlblog.com/blogs/jamie_thomson/image_1A711592.png"><img width="328" height="135" title="image" style="border-width:0px;display:inline;" alt="image" border="0" src="http://sqlblog.com/blogs/jamie_thomson/image_thumb_5794D117.png"></a> </p>
<p>As well as this format of defining data our tangible IP consisted of two <a target="_blank" mce_href="http://en.wikipedia.org/wiki/Data_Transformation_Services" href="http://en.wikipedia.org/wiki/Data_Transformation_Services">DTS</a> packages that could:</p>
<ul>
<li>be pointed at a database and produce such a collection </li>
<li>be given a collection and import it into a specified database, creating target tables if required </li>
</ul>
<p>This was used to extract data from systems of record which could then later be imported elsewhere for additional processing (e.g. as a source for a data warehouse). We liked this approach because we were <i>loosely-coupling</i> our data warehouse from the systems-of-record, this brought benefits such as the ability to extract data from source at a different time from which it was loaded into the target. It was a great piece of IP and was used at a time which heralded a very successful period for us in the Microsoft BI space. Doubtless many of you reading this have invented and/or used similar mechanisms for moving data.</p>
<hr>
<p>It was with interest then that earlier this week I was alerted (<a target="_blank" mce_href="https://twitter.com/matt40k/status/581005865269862400" href="https://twitter.com/matt40k/status/581005865269862400">by Matt Smith</a>) to an effort to achieve the same called <a target="_blank" mce_href="http://dataprotocols.org/tabular-data-package/" href="http://dataprotocols.org/tabular-data-package/">Tabular Data Package</a>.</p>
<p><a mce_href="http://sqlblog.com/blogs/jamie_thomson/image_49C51BCD.png" href="http://sqlblog.com/blogs/jamie_thomson/image_49C51BCD.png"><img width="504" height="304" title="image" style="border:0px currentColor;border-image:none;display:inline;" alt="image" border="0" src="http://sqlblog.com/blogs/jamie_thomson/image_thumb_3FFFB455.png"></a> </p>
<p>Tabular Data Packages include a JSON file for the same as which we used the Inventory and Metadata file but nevertheless its the same basic concept, it is a well-known format for transporting data in human-readable files. Tabular Data Packages are advocated by the <a href="http://opendatainstitute.org/">Open Data Institute</a>, see Jeni Tennison's blog post on the subject <a href="http://theodi.org/blog/2014-the-year-of-csv">2014: The Year of CSV</a>.</p>
<p>It occurs to me that it would be really useful to have a couple of SSIS packages that work with a Tabular Data Package in the same manner that our DTS packages worked with our data files collection all those years ago. Then again, using the SSIS object model to dynamically generate packages based on some known metadata (which is what would be required here) is notoriously difficult, better would be if there existed a code-generation tool for SSIS packages. Luckily such a thing exists, its called <a target="_blank" mce_href="http://en.wikipedia.org/wiki/Business_Intelligence_Markup_Language" href="http://en.wikipedia.org/wiki/Business_Intelligence_Markup_Language">BIML</a>, it is free and is becoming a very very popular means for developing SSIS solutions.</p>
<p>Therefore I’m issuing a friendly challenge. Can anyone out there build a BIML script that can, given a database connection string, generate a Tabular Data Package containing all the data in that database? Furthermore, can someone also build a BIML script that can <i>consume</i> a Tabular Data Package and push all the data therein into a SQL Server database?</p>
<p>The challenge is set. Is anyone game?</p>
<p><a target="_blank" mce_href="http://twitter.com/jamiet" href="http://twitter.com/jamiet">@Jamiet</a></p>Announcing SSIS Design Patterns Training in London 7-10 Sep 2015!http://sqlblog.com/blogs/andy_leonard/archive/2015/03/11/announcing-ssis-design-patterns-training-in-london-7-10-sep-2015.aspxWed, 11 Mar 2015 08:00:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:58162andyleonard<p>I am honored to work alongside <a href="http://www.technitrain.com/" target="_blank">TechniTrain</a> to deliver <a href="http://www.technitrain.com/coursedetail.php?c=47&amp;trackingcode=AL1" target="_blank">SQL Server Integration Services Design Patterns</a> in London 7-10 Sep 2015!</p> <p><a href="http://www.technitrain.com/coursedetail.php?c=47&amp;trackingcode=AL1" target="_blank">Register</a> before 31 Mar to save £100.</p> <p><strong>Who is it for?</strong></p> <p>The target audience for this course is intermediate SQL Server Integration Services developers (or quick learners) who wish to learn best practices and design patterns, and those who wish upgrade their existing SSIS skills to 2012 or 2014.</p> <p><strong>Course Highlights</strong></p> <p>SSIS 2012 Catalog Execution</p> <ul> <li>A look “under the hood” of the SSIS 2012 Catalog and SSISDB database. Learn where the SSIS Catalog metadata resides in SSISDB, see examples of custom reporting, and examine ways to extend functionality with custom objects. </li> </ul> <p>Scripting in SSIS</p> <ul> <li>Leveraging the Script Task to perform operations inside SSIS. </li> <li>Using the Script Task and .Net to interface with external operations. </li> </ul> <p>Designing Custom Tasks</p> <ul> <li>Take SSIS scripting to the next level by building custom SSIS tasks in .Net. </li> </ul> <p>Advanced Parameter and Variable Management</p> <ul> <li>Using the new Project and Package Parameters. </li> <li>Plumb the depths of the SSIS Expression Language. </li> </ul> <p>SSIS Connections and Configurations Management</p> <ul> <li>Using Environments in the SSIS 2012 Catalog. </li> <li>Custom Connections Management. </li> </ul> <p>Advanced Loop Containers</p> <ul> <li>Typical (and atypical) uses of For Loop and Foreach Loop Containers. </li> </ul> <p>Enterprise Data Integration Lifecycle Management</p> <ul> <li>SSIS Project and Package Deployment Models. </li> <li>Security, Deployment, Execution, Monitoring, and Maintenance. </li> </ul> <p>Advanced SSIS Messaging </p> <ul> <li>Impact of Deployment Model on project and package messaging. </li> <li>Events and Event Handlers. </li> <li>Logging, Reports, Configurations, and Environments. </li> </ul> <p>ETL Design Patterns</p> <ul> <li>Execution Patterns. </li> <li>Custom Logging Patterns. </li> </ul> <p>ETL Instrumentation Patterns.</p> <ul> <li>Data Warehouse ETL Patterns </li> <li>Dimension Loads. </li> <li>Fact Loads. </li> <li>Incremental Loads. </li> <li>Change Detection methods. </li> <li>Lookup Patterns. </li> </ul> <p>Data Integration Automation</p> <ul> <li>Business Intelligence Markup Language (Biml). </li> </ul> <p><strong>Key benefits</strong></p> <p>At the conclusion of the training, attendees will have been exposed to:</p> <ul> <li>New features in SSIS 2012/2014 </li> <li>Advanced patterns for loading data warehouses </li> <li>Error handling </li> <li>The Project Deployment Model </li> <li>Scripting in SSIS </li> <li>Designing Custom Tasks </li> <li>Managing, monitoring, and administering SSIS in the enterprise </li> </ul> <p>I hope to see you in London!</p> <p>:{&gt;</p> <p>Learn more: <br /><a href="http://www.linchpinpeople.com/tag/ssis/" target="_blank">Linchpin People Blog: SSIS</a> <br /><a href="http://www.sqlservercentral.com/stairway/72494/" target="_blank">Stairway to Integration Services</a></p> <p><a href="http://smile.amazon.com/Server-Integration-Services-Design-Patterns/dp/1484200837" target="_blank"><img title="SSIS2014DesignPatterns200" border="0" alt="SSIS2014DesignPatterns200" src="http://sqlblog.com/blogs/andy_leonard/SSIS2014DesignPatterns200_2A08FE52.jpg" width="163" height="204" /></a></p>Secure Connections Management in SSIS, Part 2http://sqlblog.com/blogs/andy_leonard/archive/2015/03/02/secure-connections-management-in-ssis-part-2.aspxMon, 02 Mar 2015 10:00:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:57976andyleonard<p align="left">SQL Server Integration Services (SSIS) is built to move data. SSIS ships with features to support the secure transmission of data. In this series, my goal is to promote awareness of these features and advocate for their use.</p> <p align="left"><b>SSIS Connection Managers</b></p> <p align="left">In <a href="http://sqlblog.com/blogs/andy_leonard/archive/2015/02/25/secure-connections-management-in-ssis-part-1.aspx" target="_blank">Secure Connections Management in SSIS, Part 1</a>, I demonstrated configuring an SSIS Connection Manager to us Windows Authentication, as shown in Figure 1:</p> <p align="left"><a href="http://sqlblog.com/blogs/andy_leonard/SSISCM_2_1_2AB3EB8D.jpg"><img title="SSISCM_2_1" style="border-left-width:0px;border-right-width:0px;background-image:none;border-bottom-width:0px;padding-top:0px;padding-left:0px;display:inline;padding-right:0px;border-top-width:0px;" border="0" alt="SSISCM_2_1" src="http://sqlblog.com/blogs/andy_leonard/SSISCM_2_1_thumb_425AFD26.jpg" height="244" /></a> <br /><em>Figure 1</em></p> <p align="left">Using Windows Authentication in SSIS Connection Managers is a best practice. But what if you need to use a SQL Login to connect to the database? The OLE DB Connection Manager Editor provides an option labeled, “Use SQL Server Authentication” in the “Log on to the server” groupbox. After selecting the “Use SQL Server Authentication” option, enter a user name and password in the appropriate textboxes, as shown in Figure 2:</p> <p align="left"><a href="http://sqlblog.com/blogs/andy_leonard/SSISCM_2_2_4F54DD37.jpg"><img title="SSISCM_2_2" style="border-left-width:0px;border-right-width:0px;background-image:none;border-bottom-width:0px;padding-top:0px;padding-left:0px;display:inline;padding-right:0px;border-top-width:0px;" border="0" alt="SSISCM_2_2" src="http://sqlblog.com/blogs/andy_leonard/SSISCM_2_2_thumb_6E2B5115.jpg" width="244" height="113" /></a> <br /><em>Figure 2</em></p> <p align="left"><strong>Save my Password</strong></p> <p align="left">There is a “Save my password” checkbox beneath the Password textbox, but checking this checkbox will not automatically save your password. Conversely, not checking the checkbox will not remove the password configuration from the OLE DB Connection Manager. </p> <p align="left">I can hear you thinking, “What determines whether the password will be saved as part of the Connection Manager configuration?” That is an excellent question. Let’s examine some properties and use cases that determine whether the password will be stored as part of the Connection Manager configuration.</p> <p align="left"><strong>Do Nothing</strong></p> <p align="left">If you created an SSIS package with design-time defaults and you do not check the “Save my password” checkbox and click the OK button on the OLE DB Connection Manager Editor, the password will be stored as part of the Connection Manager configuration. </p> <p align="left">How can you check? After closing the OLE DB Connection Manager Editor, right-click the OLE DB Connection Manager in the Connection Managers tab at the bottom of the SSIS package Control Flow and click “Test Connectivity” as shown in Figure 3:</p> <p align="left"><a href="http://sqlblog.com/blogs/andy_leonard/SSISCM_2_3_2669C82E.jpg"><img title="SSISCM_2_3" style="border-left-width:0px;border-right-width:0px;background-image:none;border-bottom-width:0px;padding-top:0px;padding-left:0px;display:inline;padding-right:0px;border-top-width:0px;" border="0" alt="SSISCM_2_3" src="http://sqlblog.com/blogs/andy_leonard/SSISCM_2_3_thumb_321F0F60.jpg" width="204" height="244" /></a> <br /><em>Figure 3</em></p> <p align="left">Connectivity is automatically tested when click the OK button on the OLE DB Connection Manager Editor. But you can manually test connectivity at any time via the Connection Manager’s context (right-click) menu. If SSIS is unable to acquire a connection with the current configuration, the OLE DB Connection manager will appear as shown in Figure 4:</p> <p align="left"><a href="http://sqlblog.com/blogs/andy_leonard/SSISCM_2_5_5C1F0D88.jpg"><img title="SSISCM_2_5" style="border-left-width:0px;border-right-width:0px;background-image:none;border-bottom-width:0px;padding-top:0px;padding-left:0px;display:inline;padding-right:0px;border-top-width:0px;" border="0" alt="SSISCM_2_5" src="http://sqlblog.com/blogs/andy_leonard/SSISCM_2_5_thumb_0933FA57.jpg" width="135" height="42" /></a> <br /><em>Figure 4</em></p> <p align="left">In this case, SSIS <em>is</em> able to acquire a connection with the current configuration, so the OLE DB Connection manager will appear as shown in Figure 5:</p> <p align="left"><a href="http://sqlblog.com/blogs/andy_leonard/SSISCM_2_4_215764B2.jpg"><img title="SSISCM_2_4" style="border-left-width:0px;border-right-width:0px;background-image:none;border-bottom-width:0px;padding-top:0px;padding-left:0px;display:inline;padding-right:0px;border-top-width:0px;" border="0" alt="SSISCM_2_4" src="http://sqlblog.com/blogs/andy_leonard/SSISCM_2_4_thumb_279E3B40.jpg" width="133" height="44" /></a> <br /><em>Figure 5</em></p> <p align="left">Note what happens when you re-open the OLE DB Connection Manager Editor, though: the Password textbox is empty as shown in Figure 6:</p> <p align="left"></p> <p align="left"></p> <p align="left"></p> <p align="left"><a href="http://sqlblog.com/blogs/andy_leonard/SSISCM_2_6_78D8829D.jpg"><img title="SSISCM_2_6" style="border-left-width:0px;border-right-width:0px;background-image:none;border-bottom-width:0px;padding-top:0px;padding-left:0px;display:inline;padding-right:0px;border-top-width:0px;" border="0" alt="SSISCM_2_6" src="http://sqlblog.com/blogs/andy_leonard/SSISCM_2_6_thumb_02049812.jpg" width="239" height="244" /></a> <br /><em>Figure 6</em></p> <p align="left">If you click the “Test Connection” button the test will fail and you will a dialog similar to that displayed in Figure 7:</p> <p align="left"><a href="http://sqlblog.com/blogs/andy_leonard/SSISCM_2_7_206ED8FB.jpg"><img title="SSISCM_2_7" style="border-left-width:0px;border-right-width:0px;background-image:none;border-bottom-width:0px;padding-top:0px;padding-left:0px;display:inline;padding-right:0px;border-top-width:0px;" border="0" alt="SSISCM_2_7" src="http://sqlblog.com/blogs/andy_leonard/SSISCM_2_7_thumb_179ED0AF.jpg" width="244" height="54" /></a> <br /><em>Figure 7</em></p> <p align="left">If you dismiss the dialog and click the OK button to close the OLE DB Connection Manager Editor, the OLE DB Connection Manager will indicate the connection is not connected as shown in Figure 8:</p> <p align="left"><a href="http://sqlblog.com/blogs/andy_leonard/SSISCM_2_8_4FDD47C7.jpg"><img title="SSISCM_2_8" style="border-left-width:0px;border-right-width:0px;background-image:none;border-bottom-width:0px;padding-top:0px;padding-left:0px;display:inline;padding-right:0px;border-top-width:0px;" border="0" alt="SSISCM_2_8" src="http://sqlblog.com/blogs/andy_leonard/SSISCM_2_8_thumb_763F2B12.jpg" width="141" height="45" /></a> <br /><em>Figure 8</em></p> <p align="left">In addition, an error will display in the Error List window (View—&gt; Error List) as shown in Figure 9:</p> <p align="left"><a href="http://sqlblog.com/blogs/andy_leonard/SSISCM_2_9_7C8601A0.jpg"><img title="SSISCM_2_9" style="border-left-width:0px;border-right-width:0px;background-image:none;border-bottom-width:0px;padding-top:0px;padding-left:0px;display:inline;padding-right:0px;border-top-width:0px;" border="0" alt="SSISCM_2_9" src="http://sqlblog.com/blogs/andy_leonard/SSISCM_2_9_thumb_525653B8.jpg" width="244" height="49" /></a> <br /><em>Figure 9</em></p> <p align="left"></p> <p align="left">The error will be similar to:</p> <p align="left"><font face="Courier New">An error has occurred while connecting vmSQL14.TestDB: SSIS Error Code DTS_E_OLEDBERROR.&#160; An OLE DB error has occurred. Error code: 0x80040E4D.&#160; An OLE DB record is available.&#160; Source: &quot;Microsoft SQL Server Native Client 11.0&quot;&#160; Hresult: 0x80040E4D&#160; Description: &quot;Login failed for user 'testUser'.&quot;.</font></p> <p align="left">Why did this happen? Was the OLE DB Connection Manager ever really connected? </p> <p align="left">Let’s answer that second question first. Yep, the OLE DB Connection Manager was really connected.</p> <p align="left"><strong>Why Does The OLE DB Connection Manager Behave Like This?</strong></p> <p align="left">To answer why the OLE DB Connection Manager behaved in the way it did, we have to examine some other properties in the SSIS package and project. Those properties are:</p> <ul> <li> <div align="left">SSIS package ProtectionLevel</div> </li> <li> <div align="left">SSIS project Deployment Model</div> </li> <li> <div align="left">SSIS project ProtectionLevel</div> </li> </ul> <p align="left"><strong>The SSIS Package ProtectionLevel Property</strong></p> <p align="left">The first property to examine is the SSIS package ProtectionLevel property shown in Figure 10:</p> <p align="left"><a href="http://sqlblog.com/blogs/andy_leonard/SSISCM_2_10_0A94CAD1.jpg"><img title="SSISCM_2_10" style="border-left-width:0px;border-right-width:0px;background-image:none;border-bottom-width:0px;padding-top:0px;padding-left:0px;display:inline;padding-right:0px;border-top-width:0px;" border="0" alt="SSISCM_2_10" src="http://sqlblog.com/blogs/andy_leonard/SSISCM_2_10_thumb_49864B6C.jpg" width="244" height="97" /></a> <br /><em>Figure 10</em></p> <p align="left">The default setting for the SSIS package ProtectionLevel property is EncryptSensitiveWithUserKey (shown in Figure 10). You can learn more about the <a href="https://msdn.microsoft.com/en-us/library/ms141747.aspx" target="_blank">SSIS package ProtectionLevel property at MSDN</a>. The EncryptSensitiveWithUserKey package ProtectionLevel setting configures the SSIS package to encrypt the OLE DB Connection Manager password using “a key that is based on the current user profile.” </p> <p align="left"><strong>The SSIS Project Deployment Model</strong></p> <p align="left">In SSIS 2012 and later, the default deployment model for SSIS packages and projects is “Project Deployment Model.” If you created an SSIS solution in SQL Server Data Tools – Business Intelligence (SSDT-BI) and haven’t made any changes to the default Deployment Model, you are developing in Project Deployment Model. There is currently only one other Deployment Model available in SSIS: <em>Package Deployment Model</em>. Package Deployment Model is included in SSIS 2012 and SSIS 2014 to provide backwards compatibility with SSIS 2005, SSIS 2008, and SSIS 2008 R2. In pre-2012 versions of SSIS, <em>all</em> SSIS packages used the same deployment model, and that model was the Package Deployment Model.</p> <p align="left"><em>How can you tell if your SSIS project is configured to use the Project Deployment Model or the Package Deployment Model?</em></p> <p align="left">If the SSIS project is configured to use the Package Deployment Model, Solution Explorer will indicate this with the text, “(package deployment model)” beside the name of the project, as shown in Figure 11:</p> <p align="left"><a href="http://sqlblog.com/blogs/andy_leonard/SSISCM_2_11_762F0545.jpg"><img title="SSISCM_2_11" style="border-left-width:0px;border-right-width:0px;background-image:none;border-bottom-width:0px;padding-top:0px;padding-left:0px;display:inline;padding-right:0px;border-top-width:0px;" border="0" alt="SSISCM_2_11" src="http://sqlblog.com/blogs/andy_leonard/SSISCM_2_11_thumb_26E20CF1.jpg" width="244" height="71" /></a> <br /><em>Figure 11</em></p> <p align="left">If the SSIS project is configured to use the Project Deployment Model, there will be no text following the project name in Solution Explorer, as shown in Figure 12:</p> <p align="left"><a href="http://sqlblog.com/blogs/andy_leonard/SSISCM_2_12_05EE9A4A.jpg"><img title="SSISCM_2_12" style="border-left-width:0px;border-right-width:0px;background-image:none;border-bottom-width:0px;padding-top:0px;padding-left:0px;display:inline;padding-right:0px;border-top-width:0px;" border="0" alt="SSISCM_2_12" src="http://sqlblog.com/blogs/andy_leonard/SSISCM_2_12_thumb_0B5D0AEE.jpg" width="244" height="88" /></a> <br /><em>Figure 12</em></p> <p align="left"><strong>The SSIS Project ProtectionLevel Property</strong></p> <p align="left">In Project Deployment Model, you must make sure the SSIS <em>project</em> ProtectionLevel property setting matches the ProtectionLevel setting for <em>each </em>SSIS package contained in the project. To set the SSIS project ProtectionLevel property, right-click the project in Solution Explorer and click “Properties” as shown in Figure 13:</p> <p align="left"><a href="http://sqlblog.com/blogs/andy_leonard/SSISCM_2_13_23144254.jpg"><img title="SSISCM_2_13" style="border-left-width:0px;border-right-width:0px;background-image:none;border-bottom-width:0px;padding-top:0px;padding-left:0px;display:inline;padding-right:0px;border-top-width:0px;" border="0" alt="SSISCM_2_13" src="http://sqlblog.com/blogs/andy_leonard/SSISCM_2_13_thumb_393A37D9.jpg" width="181" height="244" /></a> <br /><em>Figure 13</em></p> <p align="left">Clicking Properties opens the SSIS project’s Property Pages. the SSIS Project ProtectionLevel property is located on the Common Properties\Project page, as shown in Figure 14:</p> <p align="left"><a href="http://sqlblog.com/blogs/andy_leonard/SSISCM_2_14_4A3E65BC.jpg"><img title="SSISCM_2_14" style="border-left-width:0px;border-right-width:0px;background-image:none;border-bottom-width:0px;padding-top:0px;padding-left:0px;display:inline;padding-right:0px;border-top-width:0px;" border="0" alt="SSISCM_2_14" src="http://sqlblog.com/blogs/andy_leonard/SSISCM_2_14_thumb_0C815833.jpg" width="244" height="155" /></a> <br /><em>Figure 14</em></p> <p align="left">Again, the SSIS Project ProtectionLevel property <em><strong>must match the SSIS Package ProtectionLevel property setting for every SSIS package included in the SSIS project</strong></em>. </p> <p align="left"></p> <p>By default, the SSIS Project ProtectionLevel property is set to EncryptSensitiveWithUserKey and the SSIS package ProtectionLevel property is set to EncryptSensitiveWithUserKey. So – by default – these settings match. </p> <p><strong>The Behavior, Explained</strong></p> <p>When we enter a password into the OLE DB Connection Manager Editor and click the OK button, the value of the password is encrypted “a key that is based on the current user profile” (a quote from the page regarding <a href="https://msdn.microsoft.com/en-us/library/ms141747.aspx" target="_blank">SSIS package ProtectionLevel property at MSDN</a>). Where is it encrypted? In the SSIS package XML. You can view the SSIS package XML by right-clicking the package name in Solution Explorer and clicking “View Code” as shown in Figure 15:</p> <p><a href="http://sqlblog.com/blogs/andy_leonard/SSISCM_2_15_5D4F6C9B.jpg"><img title="SSISCM_2_15" style="border-left-width:0px;border-right-width:0px;background-image:none;border-bottom-width:0px;padding-top:0px;padding-left:0px;display:inline;padding-right:0px;border-top-width:0px;" border="0" alt="SSISCM_2_15" src="http://sqlblog.com/blogs/andy_leonard/SSISCM_2_15_thumb_1AFC5458.jpg" width="244" height="197" /></a> <br /><em>Figure 15</em></p> <p>The encrypted password is stored in the definition of the OLE DB Connection Manager. You can see it in the code displayed when you click “View Code” as shown in Figure 16:</p> <p>&#160;</p> <p><a href="http://sqlblog.com/blogs/andy_leonard/SSISCM_2_16_1FFE9207.jpg"><img title="SSISCM_2_16" style="border-left-width:0px;border-right-width:0px;background-image:none;border-bottom-width:0px;padding-top:0px;padding-left:0px;display:inline;padding-right:0px;border-top-width:0px;" border="0" alt="SSISCM_2_16" src="http://sqlblog.com/blogs/andy_leonard/SSISCM_2_16_thumb_6C2675E8.jpg" width="244" height="197" /></a> <br /><em>Figure 16</em></p> <p>Closing the Code window and return to the SSIS designer window, we can reopen the OLE DB Connection Manager Editor as shown in Figure 17:</p> <p><a href="http://sqlblog.com/blogs/andy_leonard/SSISCM_2_17_7F672C87.jpg"><img title="SSISCM_2_17" style="border-left-width:0px;border-right-width:0px;background-image:none;border-bottom-width:0px;padding-top:0px;padding-left:0px;display:inline;padding-right:0px;border-top-width:0px;" border="0" alt="SSISCM_2_17" src="http://sqlblog.com/blogs/andy_leonard/SSISCM_2_17_thumb_156D981A.jpg" width="244" height="125" /></a> <br /><em>Figure 17</em></p> <p>Note the Password textbox is empty. If one clicks the OK button now and closes the OLE DB Connection Manager Editor, the Connection Manager is validated (via SSIS design-time validation) and – since the Password textbox was empty when the developer clicked the OK button, validation fails as shown in Figure 18:</p> <p><a href="http://sqlblog.com/blogs/andy_leonard/SSISCM_2_18_5B7E552D.jpg"><img title="SSISCM_2_18" style="border-left-width:0px;border-right-width:0px;background-image:none;border-bottom-width:0px;padding-top:0px;padding-left:0px;display:inline;padding-right:0px;border-top-width:0px;" border="0" alt="SSISCM_2_18" src="http://sqlblog.com/blogs/andy_leonard/SSISCM_2_18_thumb_4CD3A948.jpg" width="141" height="46" /></a> <br /><em>Figure 18</em></p> <p>Returning to the Code view, we see that – compared to the previous XML – the &lt;DTS:Password&gt; tag is missing from the later version (on the right) as shown in Figure 19:</p> <p><em><a href="http://sqlblog.com/blogs/andy_leonard/SSISCM_2_19_32FF7319.jpg"><img title="SSISCM_2_19" style="border-left-width:0px;border-right-width:0px;background-image:none;border-bottom-width:0px;padding-top:0px;padding-left:0px;display:inline;padding-right:0px;border-top-width:0px;" border="0" alt="SSISCM_2_19" src="http://sqlblog.com/blogs/andy_leonard/SSISCM_2_19_thumb_73FDCCB0.jpg" width="244" height="68" /></a> <br /><em>Figure 19</em></em></p> <p>The other difference between the two versions of the XML is the &lt;DTS:ConnectionManager&gt; tag is closed with a “/ &gt;” construct in the later version (on the right) instead of a closing tag “&lt;/DTS:ConnectionManager&gt;” in the earlier version (on the left).</p> <p><strong>Conclusion</strong></p> <p>Is this complex? Yep. As a data integration developer with SSIS, do you <em>really</em> need to know all of this? No, not all of it; but you do need to understand the moving parts of SSIS that are related to security – as well as how they interact – and these are some of the moving parts.</p> <p align="left">Learn more: <br /><a href="http://www.eventbrite.com/e/advanced-ssis-training-3-day-course-march-9-2015-tickets-13953897483" mce_href="http://www.eventbrite.com/e/advanced-ssis-training-3-day-course-march-9-2015-tickets-13953897483">Advanced SSIS Training with Tim Mitchell and Andy Leonard in Dallas, TX 9-11 Mar 2015</a> <br /><a href="http://www.linchpinpeople.com/secure-connections-management-in-ssis-part-2-the-video/" mce_href="http://www.linchpinpeople.com/secure-connections-management-in-ssis-part-2-the-video/">Watch the Video</a> <br /><a href="http://quizegg.com/q/94390" mce_href="http://quizegg.com/q/94390">Test your knowledge</a> --> <br /><a href="http://www.linchpinpeople.com/tag/ssis/" mce_href="http://www.linchpinpeople.com/tag/ssis/">Linchpin People Blog: SSIS</a> <br /><a href="http://www.sqlservercentral.com/stairway/72494/" mce_href="http://www.sqlservercentral.com/stairway/72494/">Stairway to Integration Services</a></p> <p align="left"><a href="http://smile.amazon.com/Server-Integration-Services-Design-Patterns/dp/1484200837" mce_href="http://smile.amazon.com/Server-Integration-Services-Design-Patterns/dp/1484200837"><img title="SSIS2014DesignPatterns200" border="0" alt="SSIS2014DesignPatterns200" src="http://sqlblog.com/blogs/andy_leonard/SSIS2014DesignPatterns200_2A08FE52.jpg" width="163" height="204" /></a></p>A Cool SSIS Catalog Visualizationhttp://sqlblog.com/blogs/andy_leonard/archive/2015/02/27/a-cool-ssis-catalog-visualization.aspxFri, 27 Feb 2015 10:00:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:58039andyleonard<p align="left">My friend George Squillace (<a href="https://www.linkedin.com/profile/view?id=4644572" target="_blank">LinkedIn</a>) shares <a href="http://nhlearningsolutions.com/Blog/TabId/145/ArtMID/16483/ArticleID/1199/Architecture-of-the-SSISDB.aspx" target="_blank">this great post</a> and cool <a href="https://dl.dropboxusercontent.com/u/4219804/OLL%20Files/OLL%20Files%20to%20Xfer/Articles/SSIS%20%26%20warehousing%20References/SQL2012_SSISDB_Features.pdf" target="_blank">SSIS Catalog Visualizations</a>. </p> <p align="left">Enjoy!</p> <p align="left">:{&gt;</p> <p align="left">Learn more: <br /><a href="http://www.eventbrite.com/e/advanced-ssis-training-3-day-course-march-9-2015-tickets-13953897483" target="_blank">Advanced SSIS Training with Tim Mitchell and Andy Leonard in Dallas, TX 9-11 Mar 2015</a> <br /><a href="http://www.linchpinpeople.com/tag/ssis/" target="_blank">Linchpin People Blog: SSIS</a> <br /><a href="http://www.sqlservercentral.com/stairway/72494/" target="_blank">Stairway to Integration Services</a></p> <p align="left"><a href="http://smile.amazon.com/Server-Integration-Services-Design-Patterns/dp/1484200837" target="_blank"><img title="SSIS2014DesignPatterns200" border="0" alt="SSIS2014DesignPatterns200" src="http://sqlblog.com/blogs/andy_leonard/SSIS2014DesignPatterns200_2A08FE52.jpg" width="163" height="204" /></a></p>Secure Connections Management in SSIS, Part 1http://sqlblog.com/blogs/andy_leonard/archive/2015/02/25/secure-connections-management-in-ssis-part-1.aspxWed, 25 Feb 2015 10:00:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:57902andyleonard
<p align="left">SQL Server Integration Services (SSIS) is built to move data. SSIS ships with features to support the secure transmission of data. In this series, my goal is to promote awareness of these features and advocate for their use.</p>
<p align="left"><b>SSIS Connection Managers</b></p>
<p align="left">Let’s begin by examining ways to connect to a database – any database – using SSIS. SSIS connectivity is provider-driven. To connect to a database (or almost* <i>any</i> resource), SSIS developers use Connection Managers. </p>
<p align="left"><i>* Some exceptions: SSIS developers can use the Script Task and Script Component to connect to resources via the .Net Framework. Custom tasks and components can “reach” from within SSIS to outside resources. The Raw File Source and Raw File Destination access the file system from within the Data Flow Task <u>without</u> using a Connection Manager.</i></p>
<p align="left"><b>Configure an SSIS Connection Manager</b></p>
<p align="left">There’s no better way to learn than by doing, so please follow along if you have SSIS installed and available. Create an SSIS solution. Note that I am using SSIS 2014 for my screenshots, but I will endeavor to point out differences in versions of SSIS (I have experience with all released versions of SSIS). Once the solution is created, right-click in the Connection Managers tab and click “New OLE DB Connection…” as shown in Figure 1:</p>
<p align="left"><a mce_href="http://sqlblog.com/blogs/andy_leonard/SSISCM_1_1_27937760.jpg" href="http://sqlblog.com/blogs/andy_leonard/SSISCM_1_1_27937760.jpg"><img title="SSISCM_1_1" style="border-left-width:0px;border-right-width:0px;background-image:none;border-bottom-width:0px;padding-top:0px;padding-left:0px;display:inline;padding-right:0px;border-top-width:0px;" border="0" alt="SSISCM_1_1" width="244" height="150" src="http://sqlblog.com/blogs/andy_leonard/SSISCM_1_1_thumb_7F14954B.jpg"></a> <br><i>Figure 1</i></p>
<p align="left">Clicking “New OLE DB Connection…” opens the Configure OLE DB Connection Manager window, shown in Figure 2:</p>
<p align="left"><a mce_href="http://sqlblog.com/blogs/andy_leonard/SSISCM_1_2_499BD326.jpg" href="http://sqlblog.com/blogs/andy_leonard/SSISCM_1_2_499BD326.jpg"><img title="SSISCM_1_2" style="border-left-width:0px;border-right-width:0px;background-image:none;border-bottom-width:0px;padding-top:0px;padding-left:0px;display:inline;padding-right:0px;border-top-width:0px;" border="0" alt="SSISCM_1_2" width="244" height="207" src="http://sqlblog.com/blogs/andy_leonard/SSISCM_1_2_thumb_2EEF370D.jpg"></a> <br><i>Figure 2</i></p>
<p align="left">If you have not configured OLE DB connections, your Data Connections list will be empty. I have configured a handful of OLE DB connections on my demo virtual machine. They are listed in the Data Connections listbox in Figure 2. </p>
<p align="left">Click the “New…” button to configure a new OLE DB connection. Clicking the “New…” button opens the Connection Manager editor, shown in Figure 3:</p>
<p align="left"><a mce_href="http://sqlblog.com/blogs/andy_leonard/SSISCM_1_3_6E4CEA9D.jpg" href="http://sqlblog.com/blogs/andy_leonard/SSISCM_1_3_6E4CEA9D.jpg"><img title="SSISCM_1_3" style="border-left-width:0px;border-right-width:0px;background-image:none;border-bottom-width:0px;padding-top:0px;padding-left:0px;display:inline;padding-right:0px;border-top-width:0px;" border="0" alt="SSISCM_1_3" width="239" height="244" src="http://sqlblog.com/blogs/andy_leonard/SSISCM_1_3_thumb_657CE251.jpg"></a> <br><i>Figure 3</i></p>
<p align="left">If you click the Provider dropdown, you can see there are several OLE DB providers available to SSIS, as shown in Figure 4:</p>
<p align="left"><a mce_href="http://sqlblog.com/blogs/andy_leonard/SSISCM_1_4_52C7E89A.jpg" href="http://sqlblog.com/blogs/andy_leonard/SSISCM_1_4_52C7E89A.jpg"><img title="SSISCM_1_4" style="border-left-width:0px;border-right-width:0px;background-image:none;border-bottom-width:0px;padding-top:0px;padding-left:0px;display:inline;padding-right:0px;border-top-width:0px;" border="0" alt="SSISCM_1_4" width="244" height="143" src="http://sqlblog.com/blogs/andy_leonard/SSISCM_1_4_thumb_1F1F7C3C.jpg"></a> <br><i>Figure 4</i></p>
<p align="left">If your workstation has other OLE DB providers installed, such as providers for Oracle or DB2, those providers will also appear in this dropdown list. For the purposes of this exercise, let’s stick with the SQL Server Native Client. </p>
<p align="left">Enter the name of a SQL Server instance in the “Server name” combobox, or click the dropdown and select a name from the available SQL Server instances, as shown in Figure 5:</p>
<p align="left"><a mce_href="http://sqlblog.com/blogs/andy_leonard/SSISCM_1_5_7E2C0994.jpg" href="http://sqlblog.com/blogs/andy_leonard/SSISCM_1_5_7E2C0994.jpg"><img title="SSISCM_1_5" style="border-left-width:0px;border-right-width:0px;background-image:none;border-bottom-width:0px;padding-top:0px;padding-left:0px;display:inline;padding-right:0px;border-top-width:0px;" border="0" alt="SSISCM_1_5" width="239" height="244" src="http://sqlblog.com/blogs/andy_leonard/SSISCM_1_5_thumb_150ADB11.jpg"></a> <br><i>Figure 5</i></p>
<p align="left">The “Log on to the server” groupbox is located beneath the “Server name” combobox. This is a common first encounter with data security in SSIS. There are two options:</p>
<ul>
<li> <div align="left">Use Windows Authentication </div> </li>
<li> <div align="left">Use SQL Server Authentication </div> </li>
</ul>
<p align="left">There are several reasons why Windows Authentication is considered better and more secure than SQL Server Authentication. Windows Authentication uses Security ID’s (SIDs) instead of username / password combinations. Active Directory manages domain access when a user logs on. SQL Server permissions are granted based upon SIDs. There are several caveats to using Windows Authentication making Windows Authentication a good, but not perfect, solution for all use cases. </p>
<blockquote>
<p align="left"><i>Note: I am not a security expert. I know some security experts and their advice to me has always been, “Use Windows Authentication whenever and wherever possible.” I share their advice with you.</i></p>
</blockquote>
<p align="left">In the combobox labeled “Select or enter a database name,” do exactly that (select or enter a database name) as shown in Figure 6:</p>
<p align="left"><a mce_href="http://sqlblog.com/blogs/andy_leonard/SSISCM_1_6_6D645EE6.jpg" href="http://sqlblog.com/blogs/andy_leonard/SSISCM_1_6_6D645EE6.jpg"><img title="SSISCM_1_6" style="border-left-width:0px;border-right-width:0px;background-image:none;border-bottom-width:0px;padding-top:0px;padding-left:0px;display:inline;padding-right:0px;border-top-width:0px;" border="0" alt="SSISCM_1_6" width="239" height="244" src="http://sqlblog.com/blogs/andy_leonard/SSISCM_1_6_thumb_6494569A.jpg"></a> <br><i>Figure 6</i></p>
<p align="left">Click the OK button to close the Connection Manager editor and return to the Configure OLE DB Connection Manager window, as shown in Figure 7:</p>
<p align="left"><a mce_href="http://sqlblog.com/blogs/andy_leonard/SSISCM_1_7_6ADB2D28.jpg" href="http://sqlblog.com/blogs/andy_leonard/SSISCM_1_7_6ADB2D28.jpg"><img title="SSISCM_1_7" style="border-left-width:0px;border-right-width:0px;background-image:none;border-bottom-width:0px;padding-top:0px;padding-left:0px;display:inline;padding-right:0px;border-top-width:0px;" border="0" alt="SSISCM_1_7" width="244" height="207" src="http://sqlblog.com/blogs/andy_leonard/SSISCM_1_7_thumb_0FF87795.jpg"></a> <br><i>Figure 7</i></p>
<p align="left">Click the OK button to close the Configure OLE DB Connection Manager window and return to your SSIS package. Note a shiny, new Connection Manager, as shown in Figure 8:</p>
<p align="left"><a mce_href="http://sqlblog.com/blogs/andy_leonard/SSISCM_1_8_2168D86D.jpg" href="http://sqlblog.com/blogs/andy_leonard/SSISCM_1_8_2168D86D.jpg"><img title="SSISCM_1_8" style="border-left-width:0px;border-right-width:0px;background-image:none;border-bottom-width:0px;padding-top:0px;padding-left:0px;display:inline;padding-right:0px;border-top-width:0px;" border="0" alt="SSISCM_1_8" width="152" height="56" src="http://sqlblog.com/blogs/andy_leonard/SSISCM_1_8_thumb_4EE9F830.jpg"></a> <br><i>Figure 8</i></p>
<p align="left">Using Windows Authentication for SSIS connection managers is your first best practice for developing secure SSIS packages.</p>
<p align="left">Learn more: <br><a mce_href="http://www.eventbrite.com/e/advanced-ssis-training-3-day-course-march-9-2015-tickets-13953897483" href="http://www.eventbrite.com/e/advanced-ssis-training-3-day-course-march-9-2015-tickets-13953897483">Advanced SSIS Training with Tim Mitchell and Andy Leonard in Dallas, TX 9-11 Mar 2015</a> <br><a mce_href="http://www.linchpinpeople.com/secure-connections-management-in-ssis-part-1-the-video/" href="http://www.linchpinpeople.com/secure-connections-management-in-ssis-part-1-the-video/">Watch the Video</a> <br><a mce_href="http://quizegg.com/q/94390" href="http://quizegg.com/q/94390">Test your knowledge</a> <br><a mce_href="http://www.linchpinpeople.com/tag/ssis/" href="http://www.linchpinpeople.com/tag/ssis/">Linchpin People Blog: SSIS</a> <br><a mce_href="http://www.sqlservercentral.com/stairway/72494/" href="http://www.sqlservercentral.com/stairway/72494/">Stairway to Integration Services</a></p>
<p align="left"><a mce_href="http://smile.amazon.com/Server-Integration-Services-Design-Patterns/dp/1484200837" href="http://smile.amazon.com/Server-Integration-Services-Design-Patterns/dp/1484200837"><img title="SSIS2014DesignPatterns200" border="0" alt="SSIS2014DesignPatterns200" width="163" height="204" src="http://sqlblog.com/blogs/andy_leonard/SSIS2014DesignPatterns200_2A08FE52.jpg"></a></p>SSIS Catalog Environments– Step 20 of the Stairway to Integration Services!http://sqlblog.com/blogs/andy_leonard/archive/2015/02/18/ssis-catalog-environments-step-20-of-the-stairway-to-integration-services.aspxWed, 18 Feb 2015 10:00:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:57654andyleonard<p><a mce_href="http://www.sqlservercentral.com/stairway/72494/" target="_blank" href="http://www.sqlservercentral.com/stairway/72494/">SSIS Catalog Environments– Step 20 of the Stairway to Integration Services</a> is live at the <a mce_href="http://www.sqlservercentral.com/stairway/72494/" target="_blank" href="http://www.sqlservercentral.com/stairway/72494/">Stairway to Integration Services</a> site at <a mce_href="http://www.sqlservercentral.com" target="_blank" href="http://www.sqlservercentral.com">SQL Server Central</a>!</p> <p>Enjoy!</p> <p>:{&gt;</p>Executing SSIS: Run SSIS Packages in the SSIS Catalog via Stored Procedurehttp://sqlblog.com/blogs/andy_leonard/archive/2015/01/26/executing-ssis-run-ssis-packages-in-the-ssis-catalog-via-stored-procedure.aspxMon, 26 Jan 2015 12:00:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:57668andyleonard
<p align="left">In my article titled <a target="_blank" href="http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/120925/">A Custom Execution Method – Level 19 of the Stairway to Integration Services</a> posted at <a target="_blank" href="http://www.sqlservercentral.com/">SQL Server Central</a>, I created a stored procedure to execute SSIS packages stored in the SSIS Catalog. Really, all I did was build a rudimentary wrapper for the stored procedures used by the SSIS Catalog to execute packages. To learn more about what I did, and why and how I did it, <a target="_blank" href="http://www.sqlservercentral.com/stairway/72494/">please read the article</a>. It’s good. I promise. Would I lie?</p>
<p align="left">One benefit: the SSIS Catalog does not (easily) facilitate code re-use. If you want to execute the same package in multiple SSIS Catalog projects, the Execute Package Task is going to force you to import those packages into your existing project. What happens if you update this package later? You have to update every copy of the package… and then redeploy – and test – each project. Or, you can use an Execute SQL Task to call this stored procedure, and execute any SSIS package. In any project. In any folder.</p>
<p align="left">The Transact-SQL script that follows adds a stored procedure to the “custom” schema (please create the <i>custom </i>schema first) to the SSISDB database (the database used by the SSIS Catalog). It creates an intent-to-execute, configures the intent-to-execute, and then executes the SSIS package. </p>
<p align="left"><font size="2" face="Courier New"><font color="#0000ff">Use</font>&nbsp;<font color="#800000">SSISDB</font> <br><font color="#800000">go</font> <br> <br><font color="#0000ff">print</font>&nbsp;<font color="#ff0000">'Custom.execute_catalog_package stored procedure'</font> <br> <br><font color="#0000ff">If</font>&nbsp;<font color="#0000ff">Exists</font><font color="#800000">(</font><font color="#0000ff">Select</font>&nbsp;<font color="#800000">s</font><font color="#c0c0c0">.</font><font color="#0000ff">name</font>&nbsp;<font color="#c0c0c0">+</font>&nbsp;<font color="#ff0000">'.'</font>&nbsp;<font color="#c0c0c0">+</font>&nbsp;<font color="#800000">p</font><font color="#c0c0c0">.</font><font color="#0000ff">name</font> <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <font color="#0000ff">From</font>&nbsp;&nbsp; <font color="#800000">sys</font><font color="#c0c0c0">.</font><font color="#800000">procedures</font>&nbsp;<font color="#800000">p</font> <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <font color="#0000ff">Join</font>&nbsp;<font color="#800000">sys</font><font color="#c0c0c0">.</font><font color="#800000">schemas</font>&nbsp;<font color="#800000">s</font> <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <font color="#0000ff">On</font>&nbsp;<font color="#800000">s</font><font color="#c0c0c0">.</font><font color="#800000">schema_id</font>&nbsp;<font color="#c0c0c0">=</font>&nbsp;<font color="#800000">p</font><font color="#c0c0c0">.</font><font color="#800000">schema_id</font> <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <font color="#0000ff">Where</font>&nbsp; <font color="#800000">s</font><font color="#c0c0c0">.</font><font color="#0000ff">name</font>&nbsp;<font color="#c0c0c0">=</font>&nbsp;<font color="#ff0000">'custom'</font> <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <font color="#0000ff">And</font>&nbsp;<font color="#800000">p</font><font color="#c0c0c0">.</font><font color="#0000ff">name</font>&nbsp;<font color="#c0c0c0">=</font>&nbsp;<font color="#ff0000">'execute_catalog_package'</font><font color="#800000">)</font> <br>&nbsp; <font color="#0000ff">begin</font> <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <font color="#0000ff">print</font>&nbsp;<font color="#ff0000">' - Dropping custom.execute_catalog_package'</font>&nbsp; <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <font color="#0000ff">Drop</font>&nbsp;<font color="#0000ff">Procedure</font>&nbsp;<font color="#800000">custom</font><font color="#c0c0c0">.</font><font color="#800000">execute_catalog_package</font>&nbsp; <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <font color="#0000ff">print</font>&nbsp;<font color="#ff0000">' - Custom.execute_catalog_package dropped'</font> <br>&nbsp; <font color="#0000ff">end</font> <br> <br><font color="#0000ff">print</font>&nbsp;<font color="#ff0000">' - Creating custom.execute_catalog_package'</font> <br><font color="#800000">go</font> <br> <br><font color="#0000ff">Create</font>&nbsp;<font color="#0000ff">Procedure</font>&nbsp;<font color="#800000">custom</font><font color="#c0c0c0">.</font><font color="#ff0080"><b>execute_catalog_package <br>&nbsp;</b></font></font><font size="2" face="Courier New">&nbsp;<font color="#8000ff">@package_name</font>&nbsp;&nbsp;&nbsp; <font color="#000000"><i>nvarchar</i></font><font color="#800000">(</font><font color="#000000">260</font><font color="#800000">)</font><font color="#c0c0c0">,</font> <br><font color="#8000ff">&nbsp; @folder_name</font>&nbsp;&nbsp;&nbsp;&nbsp; <font color="#000000"><i>nvarchar</i></font><font color="#800000">(</font><font color="#000000">128</font><font color="#800000">)</font><font color="#c0c0c0">,</font> <br>&nbsp; <font color="#8000ff">@project_name</font>&nbsp;&nbsp;&nbsp; <font color="#000000"><i>nvarchar</i></font><font color="#800000">(</font><font color="#000000">128</font><font color="#800000">)</font><font color="#c0c0c0">,</font> <br>&nbsp; <font color="#8000ff">@use32bitruntime</font>&nbsp;<font color="#000000"><i>bit</i></font>&nbsp;<font color="#c0c0c0">=</font>&nbsp;<font color="#800000">false</font><font color="#c0c0c0">,</font> <br>&nbsp; <font color="#8000ff">@reference_id</font>&nbsp;&nbsp;&nbsp; <font color="#000000"><i>bigint</i></font>&nbsp;<font color="#c0c0c0">=</font>&nbsp;<font color="#0000ff">NULL</font><font color="#c0c0c0">,</font> <br>&nbsp; <font color="#8000ff">@logging_level</font>&nbsp;&nbsp; <font color="#000000"><i>varchar</i></font><font color="#800000">(</font><font color="#000000">11</font><font color="#800000">)</font>&nbsp;<font color="#c0c0c0">=</font> <font color="#ff0000">'Basic'</font> <br><font color="#0000ff">As</font> <br>&nbsp; <font color="#0000ff">begin</font> <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <font color="#008000"><i>-- create an Intent-to-Execute</i></font> <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <font color="#0000ff">declare</font>&nbsp;<font color="#8000ff">@execution_id</font>&nbsp;<font color="#000000"><i>bigint</i></font> <br> <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <font color="#0000ff">exec</font>&nbsp;<font color="#800000">[SSISDB]</font><font color="#c0c0c0">.</font><font color="#800000">[catalog]</font><font color="#c0c0c0">.</font><font color="#ff0080"><b>[create_execution]</b></font> <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <font color="#8000ff">@package_name</font><font color="#c0c0c0">=</font><font color="#8000ff">@package_name</font><font color="#c0c0c0">,</font> <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <font color="#8000ff">@execution_id</font><font color="#c0c0c0">=</font><font color="#8000ff">@execution_id</font>&nbsp;<font color="#800000">output</font><font color="#c0c0c0">,</font> <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <font color="#8000ff">@folder_name</font><font color="#c0c0c0">=</font><font color="#8000ff">@folder_name</font><font color="#c0c0c0">,</font> <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <font color="#8000ff">@project_name</font><font color="#c0c0c0">=</font><font color="#8000ff">@project_name</font><font color="#c0c0c0">,</font> <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <font color="#8000ff">@use32bitruntime</font><font color="#c0c0c0">=</font><font color="#8000ff">@use32bitruntime</font><font color="#c0c0c0">,</font> <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <font color="#8000ff">@reference_id</font><font color="#c0c0c0">=</font><font color="#8000ff">@reference_id</font> <br> <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <font color="#008000"><i>-- Decode and configure the Logging Level</i></font> <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <font color="#0000ff">declare</font>&nbsp;<font color="#8000ff">@var0</font>&nbsp;<font color="#000000"><i>smallint</i></font>&nbsp;<font color="#c0c0c0">=</font>&nbsp;<font color="#0000ff">Case</font> <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <font color="#0000ff">When</font>&nbsp;<font color="#ff00ff"><i>Upper</i></font><font color="#800000">(</font><font color="#8000ff">@logging_level</font><font color="#800000">)</font>&nbsp;<font color="#c0c0c0">=</font>&nbsp;<font color="#ff0000">'NONE'</font>&nbsp;<font color="#0000ff">Then</font>&nbsp;<font color="#000000">0</font> <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <font color="#0000ff">When</font>&nbsp;<font color="#ff00ff"><i>Upper</i></font><font color="#800000">(</font><font color="#8000ff">@logging_level</font><font color="#800000">)</font>&nbsp;<font color="#c0c0c0">=</font>&nbsp;<font color="#ff0000">'PERFORMANCE'</font>&nbsp;<font color="#0000ff">Then</font>&nbsp;<font color="#000000">2</font> <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <font color="#0000ff">When</font>&nbsp;<font color="#ff00ff"><i>Upper</i></font><font color="#800000">(</font><font color="#8000ff">@logging_level</font><font color="#800000">)</font>&nbsp;<font color="#c0c0c0">=</font>&nbsp;<font color="#ff0000">'VERBOSE'</font>&nbsp;<font color="#0000ff">Then</font>&nbsp;<font color="#000000">3</font> <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <font color="#0000ff">Else</font>&nbsp;<font color="#000000">1</font>&nbsp;<font color="#008000"><i>-- Basic</i></font> <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <font color="#0000ff">End</font> <br> <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <font color="#0000ff">exec</font>&nbsp;<font color="#800000">[SSISDB]</font><font color="#c0c0c0">.</font><font color="#800000">[catalog]</font><font color="#c0c0c0">.</font><font color="#ff0080"><b>[set_execution_parameter_value]</b></font> <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <font color="#8000ff">@execution_id</font><font color="#c0c0c0">,</font> <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <font color="#8000ff">@object_type</font><font color="#c0c0c0">=</font><font color="#000000">50</font><font color="#c0c0c0">,</font> <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <font color="#8000ff">@parameter_name</font><font color="#c0c0c0">=</font><font color="#ff0000">N'LOGGING_LEVEL'</font><font color="#c0c0c0">,</font> <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <font color="#8000ff">@parameter_value</font><font color="#c0c0c0">=</font><font color="#8000ff">@var0</font> <br> <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <font color="#008000"><i>-- Start the execution</i></font> <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <font color="#0000ff">exec</font>&nbsp;<font color="#800000">[SSISDB]</font><font color="#c0c0c0">.</font><font color="#800000">[catalog]</font><font color="#c0c0c0">.</font><font color="#ff0080"><b>[start_execution]</b></font> <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <font color="#8000ff">@execution_id</font> <br> <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <font color="#008000"><i>-- Return the execution_id</i></font> <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <font color="#0000ff">Select</font>&nbsp;<font color="#8000ff">@execution_id</font>&nbsp;<font color="#0000ff">As</font>&nbsp;<font color="#800000">execution_id</font> <br>&nbsp; <font color="#0000ff">end</font> <br> <br><font color="#800000">go</font> <br> <br><font color="#0000ff">print</font>&nbsp;<font color="#ff0000">' - Custom.execute_catalog_package created.'</font> <br><font color="#800000">go</font>&nbsp; </font></p>
<p align="left">Use this handy stored procedure to execute SSIS packages via T-SQL, regardless of which SSIS Catalog folder and project contain the packages.</p>
<p>Learn more: <br><a target="_blank" href="http://www.eventbrite.com/e/advanced-ssis-training-3-day-course-march-9-2015-tickets-13953897483">Advanced SSIS Training with Tim Mitchell and Andy Leonard in Dallas, TX 9-11 Mar 2015</a> <br><a target="_new" href="http://www.linchpinpeople.com/executing-ssis-run-ssis-packages-ssis-catalog-via-stored-procedure-video/">Watch the Video</a> <br><a target="_new" href="http://www.linchpinpeople.com/executing-ssis-run-ssis-packages-ssis-catalog-via-stored-procedure-video/">Get the Script</a> <br><a target="_new" mce_href="http://www.linchpinpeople.com/tag/ssis/" href="http://www.linchpinpeople.com/tag/ssis/">Linchpin People Blog: SSIS</a> <br><a target="_new" mce_href="http://www.sqlservercentral.com/stairway/72494/" href="http://www.sqlservercentral.com/stairway/72494/">Stairway to Integration Services</a></p>
<p><a target="_blank" mce_href="http://smile.amazon.com/Server-Integration-Services-Design-Patterns/dp/1484200837" href="http://smile.amazon.com/Server-Integration-Services-Design-Patterns/dp/1484200837"><img title="SSIS2014DesignPatterns200" style="border-left-width:0px;border-right-width:0px;background-image:none;border-bottom-width:0px;padding-top:0px;padding-left:0px;display:inline;padding-right:0px;border-top-width:0px;" border="0" alt="SSIS2014DesignPatterns200" width="163" height="204" src="http://sqlblog.com/blogs/andy_leonard/SSIS2014DesignPatterns200_2A08FE52.jpg"></a></p>
<p>:{&gt;</p>A Custom Execution Method – Level 19 of the Stairway to Integration Services!http://sqlblog.com/blogs/andy_leonard/archive/2015/01/21/a-custom-execution-method-level-19-of-the-stairway-to-integration-services.aspxWed, 21 Jan 2015 10:00:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:57653andyleonard
<p><a mce_href="http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/120925/" target="_blank" href="http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/120925/">A Custom Execution Method – Level 19 of the Stairway to Integration Services</a> is live at the <a mce_href="http://www.sqlservercentral.com/stairway/72494/" target="_blank" href="http://www.sqlservercentral.com/stairway/72494/">Stairway to Integration Services</a> site at <a mce_href="http://www.sqlservercentral.com" target="_blank" href="http://www.sqlservercentral.com">SQL Server Central</a>!</p>
<p>Enjoy!</p>
<p>:{&gt;</p>SSISDB Monitoring Queries on GitHubhttp://sqlblog.com/blogs/davide_mauri/archive/2015/01/19/ssisdb-monitoring-queries-on-github.aspxMon, 19 Jan 2015 17:13:28 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:57652manowar<p>I’ve moved my SSISDB scripts from Gist to GitHub where I can maintain them more comfortably. So far, I’ve published 6 scripts:</p> <ul> <li><strong>ssis-execution-status</strong>: Latest executed packages </li> <li><strong>ssis-execution-breakdown</strong>: Execution breakdown for a specific execution </li> <li><strong>ssis-execution-dataflow-info</strong>: Data Flow information for a specific execution </li> <li><strong>ssis-execution-log</strong>: Information/Warning/Error messages found in the log for a specific execution </li> <li><strong>ssis-execution-lookup-cache-usage</strong>: Lookup usage for a specific package/execution </li> <li><strong>ssis-execution-package-history</strong>: Execution historical data </li> </ul> <p>I used them almost every day when I need to have a quick glance to what’s going on on Integration Services and when I need to do some deep analysis of errors and problems.</p> <p>You can find them here:</p> <p><a title="https://github.com/yorek/ssis-queries" href="https://github.com/yorek/ssis-queries">https://github.com/yorek/ssis-queries</a></p> <p>If you’re also wondering what happened to the SSIS Dashboard project</p> <p><a title="https://github.com/yorek/ssis-dashboard" href="https://github.com/yorek/ssis-dashboard">https://github.com/yorek/ssis-dashboard</a></p> <p>…don’t fear, it’s not dead. I’m still working on it, but since I’m working on it only in my free time, updates are taking much more time than expected.</p> <p>PS</p> <p>Funny enough, Andy Leonard published a script to analyze lookups just couple of hours before me. You may also want to take a look at his post: <a title="http://sqlblog.com/blogs/andy_leonard/archive/2015/01/16/advanced-ssis-parsing-ssis-catalog-messages-for-lookups.aspx" href="http://sqlblog.com/blogs/andy_leonard/archive/2015/01/16/advanced-ssis-parsing-ssis-catalog-messages-for-lookups.aspx">http://sqlblog.com/blogs/andy_leonard/archive/2015/01/16/advanced-ssis-parsing-ssis-catalog-messages-for-lookups.aspx</a></p>