Category Archives: SSIS

Getting Started with SSIS: Get the Software and Tools

People often ask me for tips on getting started with SSIS. My advice is always the same: Get a copy of SQL Server Developer Edition. At the time of this writing, the current version of SQL Server is SQL Server 2014. If you work in an organization that uses SQL Server, you may be able to obtain a copy of SQL Server 2014 Developer Edition from your organization. If not, I highly recommend purchasing your own copy. SQL Server 2014 Developer Edition is available at Amazon.com for about $50 USD. Developer…(read more)

Parallel Foreach loops, one reason to use Powershell Workflow instead of SSIS

Lately I’ve been using Azure Automation which is a service on Azure for running Powershell Workflow scripts, as such as I’ve diving deep into Powershell Workflow. What’s Powershell Workflow? I’m glad you asked:

A workflow is a sequence of programmed, connected steps that perform long-running tasks or require the coordination of multiple steps across multiple devices or managed nodes. Windows PowerShell Workflow lets IT pros and developers author sequences of multi-device management activities, or single tasks within a workflow, as workflows. By design, workflows can be long-running, repeatable, frequent, parallelizable, interruptible, stoppable, and restartable. They can be suspended and resumed; they can also continue after an unexpected interruption, such as a network outage or computer restart.
Getting Started with Windows PowerShell Workflow

So Powershell Workflow does exactly what it says on the tin, its a workflow engine. Those of you that, like me, have been knee deep in SQL Server Integration Services (SSIS) for most of the past decade will know that SSIS too has its own workflow engine – its got a different name, Control Flow, but its still a workflow engine. One frequent ask of SSIS’s Control Flow is a Parallel ForEach Loop but there seems little hope that we’re going to get one (raised on Connect in March 2014, closed as Won’t Fix 5 months later) without going to 3rd party solutions like Cozyroc’s Parallel Loop Task.

As the title of this post has already told you Powershell Workflow has a big advantage over SSIS, it includes a Parallel Foreach Loop out-of-the-box and you can read all about it at about_Foreach-Parallel. Or type “help about_Foreach-Parallel” into your nearest Powershell window. From there:

The Parallel parameter of the ForEach keyword runs the commands in a ForEach script block once for each item in a specified collection.
The items in the collection, such as a disk in a collection of disks, are processed in parallel. The commands in the script block run sequentially on each item in the collection.

That’s fairly self-explanatory. If you’re interested to know more I’ve put together a cool little Powershell Workflow demo that makes calls to a URI, first serially (using a conventional Foreach Loop) then parallelly (which isn’t, I’m pretty sure, a real word but I’m using it anyway) and hosted it as a githib gist: Parallel-vs-Serial.ps1. You can simply copy-paste the script into Powershell ISE, hit F5 and you’re away. On my machine the serial calls completed in 19seconds, the parallel calls in 13seconds. No brainer!

Take a look if you have 5 minutes. This is good stuff.

@Jamiet 

image

It’s That Time of Year to Think About Your Career

If you work with SQL Server (or want to work with SQL Server), this is a good time of the year to think about your career. Why? It’s the holiday season and you should have some time off… unless you have a Crappy Job . You can use some of this time to advance your knowledge about SQL Server, especially the new version: SQL Server 2014. How? You can download a free 180-day version here . That version will carry you through the holidays and then some. Install it, poke around some, search for online…(read more)

A Biml Case Study

Business Intelligence Markup Language (Biml) is a SQL Server Integration Services (SSIS) design patterns engine. A Story of Productivity 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.” We scheduled a three-day visit. On Day…(read more)

[Re-] Updated: Creating a Custom SSIS 2012 Task

Apologies. My first edition of this post copied outdated links to this series. I have corrected the links but left the comments pointing out my earlier error. Thank you to those who let me know! :{> A while back I asked myself: “Self, do you think it is possible to create a custom SSIS 2012 task using Visual Studio 2012 Express?” That question launched a quest to learn the answer. In this series of posts I describe what I learned. I recently updated the material. You can find the updated series…(read more)

Join me for 3 Days of SSIS Training in Denmark – Advanced Integration Services 18-20 Nov 2014

I am honored to announce Advanced Integration Services will be delivered in cooperation with  in Aarhus, Denmark 18-20 Nov 2014. Target Audience 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…(read more)

SSIS Dashboard v 0.6.1

Yesterday I’ve released the latest SSIS Dashboard update. There quite a lot of new features included that I found to very useful when you have a server full of packages and logs. Here the complete list: Highlighted the feature I think worth the most:

  • Updated Morris.js to v 0.5.1
  • Updated MetisMenu to v 1.1.1
  • Added information on "Child" Packages
  • Added more detail to the "Package Execution History" page. Also added an estimated end time / elapsed time for running packages, using a moving average of 7 steps.
  • Added navigation sidebar in the main page that shows available folders and projects
  • Added support for folders and project filtering
  • Changed configuration file in order to comply with Python/Flask standards
  • Cleaned Up code in order to follow Python best practices (still a lot to do :))

Have you had a chance to give it a try? What features you’d like to see added?

My plans for the next releases is to

  • Add a configuration page so that you can choose the maximum number of rows return (now set to 15) and the time interval you want to analyze (not set to 72 hours in the config file)
  • Use a EWMA instead of the simple Moving Average
  • Do a video to show how to install and use the Dashboard
  • Package everything in only one executable file / directory / VM (I want to be able to offer a xcopy deplyment “all-included”…not only the .py files)
  • Include additional information taken from [event_message_context], [executable_statistics], [execution_parameters_values]
  • Fix the layout for small / medium screens (smartphones / tablet)
  • Add historical / average elapsed time also for Child Packages and Executables
  • Include DataFlow informations

Once all those things will be do, version 1.0 will be ready

If you want to help, fork the code from Github:

https://github.com/yorek/ssis-dashboard

if you want to try it go here

http://ssis-dashboard.azurewebsites.net/

Presenting on Biml to the SQL Midlands in Birmingham (UK) 11 Sep 2014!

I am honored to present “Using Biml as an SSIS Design Patterns Engine” to SQL Midlands 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 ( @TechniTrain ). 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. The abstract for my session: Business Intelligence Markup Language provides a powerful…(read more)

SSIS Dashboard 0.5.2 and Live Demo Website

In the last days I’ve worked again on the SQL Server Integration Service Dashboard and I did some updates:

Beta Added support for "*" wildcard in project names. Now you can filter a specific project name using an url like:

http://<yourserver>/project/MyPro*

Added initial support for Package Execution History. Just click on a package name and you’ll see its latest 15 executions

and I’ve also created a live demo website for all those who want to give it a try before downloading and using it:

http://ssis-dashboard.azurewebsites.net/

SSIS Dashboard v0.4

Following the post on SSISDB script on Gist, I’ve been working on a HTML5 SSIS Dashboard, in order to have a nice looking, user friendly and, most of all, useful, SSIS Dashboard.

Since this is a “spare-time” project, I’ve decided to develop it using Python since it’s THE data language (R aside), it’s a beautiful & powerful, well established and well documented and with a rich ecosystem around.

Plus it has full support in Visual Studio, through the amazing Python Tools For Visual Studio plugin,

I decided also to use Flask, a very good micro-framework to create websites, and use the SB Admin 2.0 Bootstrap admin template, since I’m all but a Web Designer.

The result is here:

https://github.com/yorek/ssis-dashboard

and I can say I’m pretty satisfied with the work done so far (I’ve worked on it for probably less than 24 hours). Though there’s some features I’d like to add in t future (historical execution time, some charts, connection with AzureML to do prediction on expected execution times) it’s already usable. Of course I’ve tested it only on my development machine, so check twice before putting it in production but, give the fact that, virtually, there is not installation needed (you only need to install Python), and that all queries are based on standard SSISDB objects, I expect no big problems.

ssis-dashboard-v0.4

If you want to test, contribute and/or give feedback please fell free to do it…I would really love to see this little project become a community project!

Enjoy!