Skip to content

SQL Data Partners Podcast – Is SSIS Still Relevant?

SQL Data Partners Podcast

In 2015, I was invited to record a SQL Data Partners podcast about Biml. This year, I got to do it all again, yay! :) I joined Jason Horner and our host Carlos L Chacon to discuss several data integration topics. Is SSIS still relevant? What is the difference between ETL and ELT? How is Azure Data Factory and Azure Databricks different from SSIS? How do patterns and frameworks fit into all of this? And much, much more :)

SQL Data Partners Podcast – Part 1

Moving data with SSIS is a forgone conclusion for most of us — it is part of SQL Server, it has a interface we are somewhat familiar with, and connects to lots of common data sources like csv, excel, and of course SQL Server. Are our needs changing as our environments become more hybrid — as we introduce cloud services? Are you still building packages the same way you were 10 years ago? Just as our data sources have grown, the capabilities needed to move data around have increased. This episode, with Jason Horner and Cathrine Wilhelmsen, challenges some of the thoughts around data movement and gives some things to think about as new tools are available to move data around.

Listen to Episode 144: Is SSIS still relevant? Part 1

SQL Data Partners Podcast – Part 2

In this episode we finish up our conversation with Jason and Cathrine and talk about how they keep up with all the changes in data conversions. The advent of Azure services is great, but can be tricky to navigate. We also get to hear Jason’s answers to the SQL Family questions.

Listen to Episode 145: Is SSIS still relevant? Part 2

Biml for OData Source and Connection Manager in SSIS

Biml for OData Source and Connection Manager

As of July 2018, there is no built-in Biml support for OData. To work with OData in Biml, you have to create a custom source and connection manager. This requires more Biml code than built-in functions like OleDbSource and may look a little overwhelming at first. But don’t worry! You don’t have to start from scratch.

In this blog post, we will first look at the properties of the OData Connection Manager and how to script it in Biml. Then, we will do the same for the OData Source. Finally, we will tie it all together and create a complete SSIS package that you can use a starting point for your own projects.

The Quick and Easy Solution

But before we dig into any code, let’s skip to the quick, easy, and timesaving solution. That’s what we all really want, right? :)

  1. Install or upgrade to BimlExpress 2018
  2. Create an example SSIS package using an OData Source and Connection Manager
  3. Convert the SSIS package to Biml
  4. Done! :)
Convert SSIS Packages to Biml

As promised: quick, easy, and timesaving! The new Convert SSIS Packages to Biml feature was released in BimlExpress 2018, and it really is a lifesaver. After converting to Biml, you can simply copy and paste the code into your projects.

However!

You may run into some bugs when you convert your SSIS packages to Biml. I ran into two issues while writing this blog post. The first was that I had to add UsesDispositions=”true” to the Source component. The second was that the data types in the Source component were prefixed with System. I have fixed both of these issues in my examples below. In addition to these issues, the converted Biml also contained some unnecessary code. Unnecessary code does not break anything, but it can make your code harder to read and maintain. Personally, I prefer my code to be as clean and simple as possible.

Ok, let’s dig into the actual Biml code!

Continue reading →

Precon and Speaking at SQLSaturday Nashville 2018

SQLSatNashWoohoo! I’m very happy to announce that Jason Horner (@jasonhorner) and I will be presenting a precon at SQLSaturday Nashville 2018! The precon will be held on Friday, January 12th, and is focused on SSIS and Biml. We will also be at the SQLSaturday where Jason will be presenting about Dimensional Modeling and I will be presenting about Biml. This will be my first time visiting Nashville, so I’m very much looking forward to it :)

SQLSaturday Nashville 2018 Precon (Jan 12)

Jason Horner and Cathrine Wilhelmsen presents ETL Design Patterns at SQLSaturday Nashville 2018

Jason had been working on a precon idea for a while that would bring together all his experience using SSIS to deliver ETL projects. I wanted to develop a new precon focusing on Biml and SSIS. Instead of working on two separate precons, we decided to team up and combine our knowledge. We will be delivering this precon for the first time in Nashville, and we’re looking forward to helping attendees by providing guidance on how to solve challenges they might face in their projects.

You can read more about why we wanted to present this precon in our interview on the Nashville BI User Group website.

Check out the full abstract and register for our precon on bit.ly/ETL-Precon-Nash. A regular full-day is only $129, and for only $165 you can also attend one of the half-day precons on Thursday. What a bargain!

SQLSaturday Nashville 2018 Sessions (Jan 13)

Jason and I are also presenting a regular session each. Jason will be presenting his Dimensional Modeling Design Patterns: Beyond the Basics session, and I will be presenting my Biml for Beginners: Speed Up Your SSIS Development session.

Check out the full SQLSaturday Nashville schedule and register today to get all the news and updates. You can also follow @SQLSatNash on Twitter and use the hashtag #SQLSatNash to join the conversation. Please help spread the word to all your friends and coworkers, and make sure you sign up before the event is full.

Hope to see you in Nashville in January! :)

Azure Data Factory v2 with Mike Flasko

One of the sessions I was most looking forward to at Microsoft Ignite 2017 was New capabilities for data integration in the cloud with Mike Flasko. In that session, he talks about Azure Data Factory (ADF) v2 and its new first-class SSIS support.

After the session, I convinced Mike Flasko and Sanjay Krishnamurthi to have a chat with me :) We talked about what’s new in Azure Data Factory v2, including the updated pipeline application model with a new visual design canvas, new Software Development Kits (SDKs) for working with Azure Data Factory, the new Integration Runtime, and the ability to run SSIS packages inside Azure Data Factory v2.

Azure Data Factory v2 with Mike Flasko

Follow Mike Flasko on Twitter @mflasko, and keep an eye out for more news about ADF and SSIS! I may or may not have convinced him to do another interview with me in a couple of months :)

Thank you Mike and Sanjay for sitting down with me on a busy day! If you were unable to attend Microsoft Ignite 2017, you can now watch Mike’s session New capabilities for data integration in the cloud on-demand:

Other interviews from Microsoft Ignite 2017

Data Science with Buck Woody
SQL Server 2017 with Bob Ward
High Performance Analytics with Sunil Agarwal

Why can’t I create SSIS Project Parameters from Biml?

Biml (Business Intelligence Markup Language) - Why can't I create SSIS Project Parameters from Biml in BimlExpress or BIDS Helper?BIDS Helper and BimlExpress does not support creating SSIS project parameters from Biml out of the box. There are workarounds (and I have previously blogged about my solution for creating project parameters from Biml), but why is this not a standard feature in BIDS Helper or BimlExpress? Many people have asked about this, so I sat down with Biml creator Scott Currie (@ScottCurrie) to get the full story.

Why doesn’t BIDS Helper or BimlExpress emit SSIS project parameters from Biml?

Well, technically it could, but it shouldn’t. The user experience would have serious issues, leading to confusion, frequent errors, and the potential for data loss. How can that be?

Continue reading →