Search results matching tags 'SSIS' and 'BIML'http://sqlblog.com/search/SearchResults.aspx?o=DateDescending&tag=SSIS,BIML&orTags=0Search results matching tags 'SSIS' and 'BIML'en-USCommunityServer 2.1 SP2 (Build: 61129.1)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>A Biml Case Studyhttp://sqlblog.com/blogs/andy_leonard/archive/2014/11/26/a-biml-case-study.aspxThu, 27 Nov 2014 00:37:22 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:56821andyleonard<p><a href="http://varigence.com/Products/Biml/Capabilities" target="_blank">Business Intelligence Markup Language</a> (Biml) is a <a href="http://en.wikipedia.org/wiki/SQL_Server_Integration_Services" target="_blank">SQL Server Integration Services</a> (SSIS) <a href="http://smile.amazon.com/Server-Integration-Services-Patterns-Experts/dp/1430237716/" target="_blank">design patterns</a> engine. </p> <p><strong>A Story of Productivity</strong></p> <p>A while back I was contacted by a client experiencing a familiar issue. The Production SQL Server instance was experiencing performance issues due to conflicts between customers and enterprise reporting needs. They contacted a friend who contacted me. When we spoke they asked, “Can you help?” “Yes,” I replied, “I can help.” </p> <p>We scheduled a three-day visit. On Day 1 I installed a Linchpin People SSIS Framework and gathered information about the Production databases. The Framework facilitates execution and logging and supports my preference of building a single SSIS package per table. I counted over 700 tables in over 30 databases. I determined an incremental load pattern would serve the customer best for their largest and most active tables. For the smaller tables, a truncate-and-load – whack ‘n’ load – pattern would suffice. </p> <p>I fired up <a href="http://www.varigence.com/Mist" target="_blank">Mist</a>, the Biml development environment from <a href="http://www.varigence.com/Mist" target="_blank">Varigence</a>, and started customizing our version of a staging application. </p> <blockquote> <p><em>I need to pause here and let you know that it takes me about an hour to build and test a typical truncate-and-load SSIS package, and two hours to build and test a typical incremental load SSIS package. I was looking at roughly 4.5 months of work with these tables.</em></p> </blockquote> <p>I spent Days 2 and 3 building and deploying SSIS packages using Mist. In the 16 hours that comprised these days, I completed building over 700 SSIS packages, loaded the Framework execution metadata (I used Biml to auto-generate approximately 25,000 lines of T-SQL at approximately 2,000 lines of code per second…), and performed a test load of Production data to the new enterprise reporting instance of SQL Server. When I ran the numbers, I did <em>not</em> do the work of 100 SSIS developers.</p> <p><em>Only 50.</em></p> <p><em>You</em> can do this, too.</p> <p><strong>How?</strong></p> <p>Biml and Mist are the secrets to my productivity. I not only drastically cut development time and effort, I <em>improved</em> code quality. SSIS packages are generated from tried-and-true templates. If one executes my tests successfully, then all the SSIS packages will execute my tests successfully.</p> <p><strong>Learn Biml NOW!</strong></p> <p>Would you like to multiply your SSIS productivity <em>while</em> improving code quality? Tim Mitchell and Reeves Smith are delivering an excellent one-day workshop titled <a href="http://www.eventbrite.com/e/getting-started-with-biml-tickets-13980635457" target="_blank">Getting Started With Biml</a> 5 Dec 2014 in Vienna, Virginia. The cost for this event is only $129. </p> <p>Details:</p> <p>For ETL developers, Biml (Business Intelligence Markup Language) is a game changer. This is a great new tool for Microsoft business intelligence developers who want to make the most of their development efforts. In short, it’s an XML-based language that will allow you to programmatically create SSIS packages. Even better, it can be enhanced with VB.NET or C# scripting to create BimlScript, which allows you to dynamically build and modify a few, dozens, or even hundreds of packages with minimal effort. Best of all, you don’t need budget approval – if you already have Visual Studio, you can use Biml and BimlScript for free! <br />In this full-day presentation, we’ll introduce you to dynamic package generation through Biml. Business intelligence consultants Tim Mitchell and Reeves Smith will walk you through the essentials of Biml – getting started, syntax, and common uses – followed by dozens of design patterns to improve your efficiency as an ETL developer.</p> <p>Among the topics that we’ll cover:</p> <ul> <li>Overview of Biml – What is it? Where do I use it? </li> <li>Introduction to Biml syntax </li> <li>Biml in the Visual Studio environment </li> <li>Introduction to BimlScript syntax </li> <li>Dynamic package generation with BimlScript </li> <li>Metadata management with Biml </li> <li>Create a simple staging package project with dozens of packages in less than an hour </li> <li>Package (re)generation </li> <li>Source control </li> <li>Common design patterns for Biml in the enterprise </li> <li>Taking Biml to the next level with Mist</li> </ul> <p>By the end of this full-day presentation, you should be equipped to get started immediately using Biml. It’s a revolutionary technology, and we hope that you’ll be as excited about Biml as we are.</p> <p><a href="http://www.eventbrite.com/e/getting-started-with-biml-tickets-13980635457" target="_blank">Register today</a>!</p> <p>:{&gt;</p>Join me for 3 Days of SSIS Training in Denmark – Advanced Integration Services 18-20 Nov 2014http://sqlblog.com/blogs/andy_leonard/archive/2014/09/08/join-me-for-3-days-of-ssis-training-in-denmark-advanced-integration-services-18-20-nov-2014.aspxMon, 08 Sep 2014 08:45:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:55043andyleonard<p>I am honored to announce <a href="http://orangeman.dk/kursuskatalog/advanced-integration-services/" target="_blank">Advanced Integration Services</a> will be delivered in cooperation with&#160; in Aarhus, Denmark 18-20 Nov 2014. </p> <p><strong>Target Audience</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 the more advanced stuff, and those who wish upgrade their existing SSIS skills to 2012. Since there are almost no changes between SSIS 2012 and 2014, this course will also be suitable for SSIS 2014 users too.</p> <p><strong>Agenda</strong></p> <ul> <li>Advanced patterns for loading data warehouses</li> <li>ETL Load Performance</li> <li>Data Cleansing</li> <li>Flow, Precedence, Advanced Loop Containers</li> <li>Error handling</li> <li>ETL Instrumentation</li> <li>Scripting in SSIS</li> <li>Designing Custom Tasks</li> <li>Executing, managing, monitoring, and administering SSIS in the enterprise</li> <li>New features in SSIS 2012 and 2014</li> <li>The (new) Project Deployment Model</li> <li>The (new) SSIS Catalog</li> <li>SSIS Frameworks</li> <li>Business Intelligence Markup Language (Biml)</li> </ul> <p>I hope to see you there!</p> <p><a href="http://orangeman.dk/wordpress/?page_id=8&amp;event_id=88&amp;name_of_event=Advanced%20Integration%20Services" target="_blank">Register</a> today! Sign up for the <strong>Advanced Integration Services</strong> <em>at the bottom of <a href="http://orangeman.dk/wordpress/?page_id=8&amp;event_id=88&amp;name_of_event=Advanced%20Integration%20Services" target="_blank">this page</a></em>.</p> <p>:{&gt;</p>Presenting on Biml to the SQL Midlands in Birmingham (UK) 11 Sep 2014!http://sqlblog.com/blogs/andy_leonard/archive/2014/08/27/presenting-on-biml-to-the-sql-midlands-in-birmingham-uk-11-sep-2014.aspxWed, 27 Aug 2014 08:00:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:54981andyleonard<p>I am honored to present “Using Biml as an SSIS Design Patterns Engine” to <a href="http://sqlserverfaq.com/events/638/SQL-Midlands-Evening-SQL-User-Group-Chris-Webb-and-Andy-Leonard-the-BI-Masterclass.aspx" target="_blank">SQL Midlands</a> in Birmingham 11 Sep 2014 starting at 18:15! As at SQL Supper the evening before, the other presenter is my esteemed friend, Chris Webb (<a href="http://twitter.com/TechniTrain">@TechniTrain</a>). Chris’ session is titled, “Everything you wanted to know about Power BI but were too afraid to ask.” I look forward to learning more about Power BI from one of the masters.</p> <p>The abstract for my session:</p> <blockquote> <p>Business Intelligence Markup Language provides a powerful solution for creating and managing SSIS Design Patterns. Andy Leonard, one of the authors of SSIS Design Patterns, demonstrates the benefits of BIML in this session. What are the benefits of Biml? Code re-use, automated code-generation from templates, faster delivery, higher quality.</p> </blockquote> <p>If you will be in the Birmingham area on the evening of 11 Sep, please stop by and say “hello!” to Chris and me. Chris is the younger, better-looking, smarter guy. I’m the old guy with a long beard who talks funny (I promise yall I am working on my English). <a href="http://sqlserverfaq.com/events/638/SQL-Midlands-Evening-SQL-User-Group-Chris-Webb-and-Andy-Leonard-the-BI-Masterclass.aspx" target="_blank">Register today</a>!</p> <p>:{&gt;</p> <p>PS – There are still one or two seats remaining in my <a href="http://technitrain.com/coursedetail.php?c=31&amp;trackingcode=AL1" target="_blank">SSIS 2012 Design Patterns</a> course 8-11 Sep in London. Please <a href="https://www.regonline.co.uk/Register/Checkin.aspx?EventID=1293832&amp;trackingcode=AL1" target="_blank">register here</a> to learn more about SSIS!</p>Presenting Using Biml as an SSIS Design Patterns Engine to Lynchburg SQL Server Users Group 26 Jun!http://sqlblog.com/blogs/andy_leonard/archive/2014/06/20/presenting-using-biml-as-an-ssis-design-patterns-engine-to-lynchburg-sql-server-users-group-26-jun.aspxFri, 20 Jun 2014 11:43:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:54246andyleonard
<p>I am honored to present&nbsp;Using Biml as an SSIS Design Patterns Engine to <a mce_href="http://lynchburg.sqlpass.org/" target="_blank" href="http://lynchburg.sqlpass.org/">Lynchburg SQL Server Users Group</a> Thursday, 26 Jun!</p>
<p><b>Abstract</b>&nbsp;</p>
<p>Business Intelligence Markup Language provides a powerful solution for creating and managing SSIS Design Patterns. Andy Leonard, one of the authors of <a target="_blank" href="http://www.amazon.com/Server-Integration-Services-Patterns-Experts/dp/1430237716/">SSIS Design Patterns</a>, demonstrates the flexibility of Biml in this session.</p><p>If you're going to be in the Lynchburg area Thursday, I hope to see you there!&nbsp;</p><p>:{&gt;&nbsp;</p>Presenting Using Biml as an SSIS Design Patterns Engine at NoVaSQL in Washington DC Monday!http://sqlblog.com/blogs/andy_leonard/archive/2014/03/19/presenting-using-biml-as-an-ssis-design-patterns-engine-at-novasql-in-washington-dc-monday.aspxWed, 19 Mar 2014 15:57:55 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:53287andyleonard<p>I am honored to present <a href="http://www.novasql.com/" target="_blank">Using Biml as an SSIS Design Patterns Engine</a> Monday 24 Mar 2014 at the Northern Virginia SQL Server User Group (<a href="http://www.novasql.com/" target="_blank">NoVaSQL</a>)!</p> <p>I hope to see you there. <a href="http://www.novasql.com/" target="_blank">Details…</a></p> <p>:{&gt;</p>Announcing the 2013 Biml Workshop 15 Oct 2013 in Charlotte NC!http://sqlblog.com/blogs/andy_leonard/archive/2013/05/18/announcing-the-2013-biml-workshop-15-oct-2013-in-charlotte-nc.aspxSat, 18 May 2013 04:00:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:49102andyleonard<p><strong><a href="http://bimlscript.eventbrite.com" target="_blank"><img title="2013 Biml Workshop presented by Varigence and Linchpin People" style="border-top:0px;border-right:0px;background-image:none;border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;display:inline;padding-right:0px;" border="0" alt="2013 Biml Workshop presented by Varigence and Linchpin People" src="http://sqlblog.com/blogs/andy_leonard/BimlWorkshopHeader_5935BC00.jpg" width="447" height="142" /></a><a href="http://bimlscript.eventbrite.com" target="_blank"><img title="2013 Biml Workshop - Learn Biml and more!" style="border-top:0px;border-right:0px;background-image:none;border-bottom:0px;float:right;padding-top:0px;padding-left:0px;border-left:0px;display:inline;padding-right:0px;" border="0" alt="2013 Biml Workshop - Learn Biml and more!" align="right" src="http://sqlblog.com/blogs/andy_leonard/2013BimlWorkshopGraphic1_585D5616.png" width="434" height="615" /></a></strong></p> <p><strong>When</strong>:</p> <p>October 15, 2013 <br />8:45 am - 4:45 pm <br />Charlotte, NC</p> <p><strong>Where</strong>:</p> <p>Wake Forest University Charlotte Center <br />200 North College Street <br />Charlotte, NC 28202</p> <p><strong>What</strong>:</p> <p>Business Intelligence Markup Language (Biml) automates your BI patterns and eliminates the manual repetition that consumes most of your time. Come see why BI professionals around the world think Biml is the future of data integration and BI.</p> <p><a href="http://bimlscript.eventbrite.com" target="_blank">Registration</a> is just $69. <a href="http://bimlscript.eventbrite.com" target="_blank">Register</a> before July 15th and receive early bird discount of just $49. Breakfast, lunch, &amp; refreshments are also included. Seating is limited. <a href="http://bimlscript.eventbrite.com" target="_blank">Register</a> now to guarantee your spot. </p> <p>:{&gt;</p>Presenting Using Biml as a Design Patterns Engine to the Himalayan SQL Server UG 29 Sep!http://sqlblog.com/blogs/andy_leonard/archive/2012/09/20/presenting-using-biml-as-a-design-patterns-engine-to-the-himalayan-sql-server-ug-29-sep.aspxThu, 20 Sep 2012 11:00:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:45271andyleonard<p>I am honored to present “Using Biml as a Design Patterns Engine” to the <a target="_blank" href="http://sqlpassnepal.org/">Himalayan SQL Server User Group</a> 29 Sep 2012 at 3:00 PM (+5:45). <a target="_blank" href="http://sqlpassnepal.org/Meetings/Himalayan-SQL-Server-User-Group-Monthly-Meetin-(2).aspx">Registration</a> is open!</p> <p>:{&gt;</p>Presenting at RichmondSQL 19 Jul 2012!http://sqlblog.com/blogs/andy_leonard/archive/2012/07/13/presenting-at-richmondsql-19-jul-2012.aspxFri, 13 Jul 2012 11:00:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:44276andyleonard<p>I am honored to present Using BIML as an SSIS Design Patterns Engine to the Richmond SQL Server Users Group Thursday, 19 Jul 2012! <a href="http://richmondsqljuly2012.eventbrite.com/">Register here</a>.</p> <p>Will you be in the Richmond area next Thursday? If so, drop by and introduce yourself. I'm the fat guy with a fu.</p> <p>:{&gt;</p>Presenting at Hampton Roads SQL Server and SharePoint User Group 18 Jul 2012!http://sqlblog.com/blogs/andy_leonard/archive/2012/07/12/presenting-at-hampton-roads-sql-server-and-sharepoint-user-group-18-jul-2012.aspxThu, 12 Jul 2012 16:38:04 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:44277andyleonard<p>I am honored to present Using BIML as an SSIS Design Patterns Engine to the Hampton Roads SQL Server and SharePoint User Group Wednesday, 18 Jul 2012! <a href="http://hrssug.org/SitePages/Home.aspx">Register here</a>.</p> <p>Will you be in the Hampton Roads area next Wednesday? If so, drop by and introduce yourself. I'm the fat guy with a fu.</p> <p>:{&gt;</p>