News

Tweets













Top 10+ SSIS 2012 Improvements

If you have not heard the latest technical news yet, the SQL Server 2012 Virtual Launch event is today March 8, 2012!

Register to not to miss this opportunity to interact with technology experts and win cool prizes!

I am thrilled and excited!

Why? Because like in the automotive industry, every new iteration of a vehicle is more fuel efficient, safer, more polished, looking more attractive and fun to drive.

Sure, the the new version of the SQL Server is ready to take me further in what I can deliver to my clients, empower me even more and make me a better professional!

Do not miss the chance knowing first hand what exactly is going to rock many corporate and smaller IT departments.

If the aforesaid is not enough to convince you to spend some time glancing at the future and ask fellow professionals on the new capabilities, then why not to even just enjoy some swags?

Yes, some free stuff you used to get when attending public events! How about a totally free ebook you can take anywhere? And namely: PowerShell Administrator’s Pocket Consultant by William Stanek! By the way, watch for even more books at the event for a great discount.

OK, now to the topic, my goal here is cover the top 10 (and more) improvements from my point of view in SQL Server Integration Services 2012 for a SSIS Developer.

1. ODBC Support

The ODBC support is becoming first class now I guess because of the future full integration with Hadoop and an increased demand to integrate more easily with various open source platforms. So I guess the days when you will be able to easily connect to a Linux machine from a SQL Server are coming. Attunity connectors also get more readily available and covering more vendors.

2. Change Data Capture for SSIS

The Change Data Capture (CDC) is not new to SQL Server, but it is a new kind of an animal to SSIS:

image

Now with CDC one can easily capture the changes in data sources and provide them for reporting, data analysis or feed into the Data Warehouse.

3. Support for Variable Number of Columns in a Flat File

This is a productivity enhancement that potentially pays for a good portion of the upgrade fee (IMHO). I just happen to see how many developers stumble upon such a reality unable to overcome this barrier resorting to various online forums or blogs. No longer!

If you see a file as depicted below:

image

No fear, it will be understood by the SSIS engine and handled without incidents:

image

Hooray! No more time wasted and scratching your head!

4. Revamped Configurations

This is another big improvement.

Did you ever wonder why you deployed a package and it took the design time parameters? Did you struggle to deploy your config files or a database along with the package?

No longer! You now can have several configurations, for Dev and Prod, no problem. If you envied your fellow C# or VB .Net developer being able to store parameters right in the Visual Studio, no more, now you can, too. As an aside, there is no more BIDS, there is the new Data Tools, but to me it is a Visual Studio 2010, I just develop special projects in it, and it is a 1st class tool! And how about this: you can even add parameters after the package has been deployed? Do you feel thrilled as me? Not yet, then how about the possibility of sharing parameters across many packages within a project?

5. Script Component – you can debug it, finally!

If your heart is not beating faster by now, then let’s recall how much you struggled to find out why a Script Component does not work as expected? A value, or worse yet, three are not right?

Remember? No? I do, I remember how I needed to build a console app till 10 PM to just solve the mystery why the values were wrong sitting along in the office biting nails because at midnight a package just had to load the latest flight data. I wish I could just debug the mysterious component with 400 lines of code. Sigh and smile, now I will:

image

Better yet, all my runtime values are captured. Did I say it is a Visual Studio?

6. SSIS Package Format Changed and the Specs are Open Source!

Bye-bye the lineage IDs, cryptic, long XML! Hello comparable, mergable packages!

imagevs. image

Easily compare packages with Diff tools now! Full specs are at: http://msdn.microsoft.com/en-us/library/gg587140.aspx

7. Built-in Reporting

Yes, there will be three canned reports provided for You, dear developer to benchmark, troubleshoot and just better support a live implementation:

image

image

image

8. Data Taps

This is totally new: have you ever been asked to fix a package with no rights to access the data source? I had such an “opportunity”, their DBA just shrugged off my requests to provide with a read only account. But now you are more in control, you can now turn on and off small data dumps to a CSV file for an ad-hock analysis. Those, most often, are instrumental in finding metadata differences and thus allowing a real quick fix to many issues. More on this topic is here: http://goo.gl/AUBP5

9. Deploying Projects from Visual Studio

Yes, like I said, Visual Studio is the centerpiece to developing and deploying a SSIS solution. Now you need to think more project oriented as a result, so there is a bit of paradigm shift, or I would say you need to think of a project as unit more than of a package now in SSIS 2012 (for those not ready for the change the old deployment model still works, so not to worry).

So what is different, actually all and more simple, you just deploy with a right-click on the project, no more fiddling around with the Deployment manifest or manual copy and paste, import, etc.

The configurations are taken care of automatically!

(picture is taken from Rafael Salas blog http://www.rafael-salas.com/2012/01/ssis-2012-project-deployment-model-and.html).

10. Manage with PowerShell

Did I mention about the PowerShell book at the beginning of the post? I did this on purpose Smile. SSIS 2012 provides with 1st class support to managing the SSIS indices as the SSIS catalog, package deployment and maintenance. You can craft and automate most tasks using an editor, just reference the needed module:

SNAGHTML75e1d8

There are also the APIs to validate a package, configure and deploy a package:

image

Oh, I have just already covered 10 improvements, wait but there are more:

  • Un-do and Re-do are now possible (I can hear the wow!);
  • New designer surface (AKA canvas) with adorners

image

  • Shared (across a project) Connection Managers (no more click and copy, pastes)!
  • Shared (across packages in project) Cache Managers
  • Do you remember the dreaded errors all over the package after some metadata changed? Now you can resolve them all up the stream with a single click!
  • Group items to reduce clutter without resorting to sequence containers:

image

  • The ability to rollback to an older (and working) version of a package:

image

I can hear the applause…

OK for now!

I hope I whet wet your appetite enough to go and explore the features yourself. And to stay always tuned do not forget too bookmark the aggregated SSIS Resources page: http://goo.gl/2WZxp!


PS: So all signs are, the SQL Server Integration Services 2012 is a huge incremental leap forward bringing better productivity, and thus shortened what I call “time-to-solution” cycle, improved connectivity and support.

Dare to explore!

Regards,

Arthur

Tuesday, March 6, 2012 12:26 AM

Feedback

# re: Top 10+ SSIS 2012 Improvements

Hey Arthur,
Great compilation, you summarized most of the features very well. It was real fun reading your blog :) 5/21/2012 6:12 PM | Sudeep Raj

# re: Top 10+ SSIS 2012 Improvements

Good effort! nice visuals. 7/10/2012 2:08 AM | Nagarjuna

# re: Top 10+ SSIS 2012 Improvements

Thanks Arthur for sharing the details, it helped to know the new features with 2012.Good effort. 10/1/2012 7:01 AM | JAYA CHANDRA MUNI

# re: Top 10+ SSIS 2012 Improvements

Great Article . Appreciate the time and effort you have taken to compile this great article. Great Job. 10/22/2012 10:59 AM | VIVEK C

# re: Top 10+ SSIS 2012 Improvements

very nice... gr8 blog. Thanks 1/11/2013 4:48 PM | Mahadev

# re: Top 10+ SSIS 2012 Improvements

very good article.
fun reading too.
great thanks
10/3/2013 10:37 AM | Rafi

# re: Top 10+ SSIS 2012 Improvements

good article..keep posting. 10/19/2013 1:15 AM | nagendra

# re: Top 10+ SSIS 2012 Improvements

That's cool.. Thanks for posting :) 11/21/2013 4:43 PM | Pramod

# re: Top 10+ SSIS 2012 Improvements

Thats's really helpful to understand SSIS 2012 .. 12/19/2013 2:52 AM | Ashutosh

# re: Top 10+ SSIS 2012 Improvements

Good Article... Keep posting. 2/24/2014 3:58 AM | Beena

# re: Top 10+ SSIS 2012 Improvements

Nicely done! 6/23/2014 11:51 AM | Arjun

# re: Top 10+ SSIS 2012 Improvements

*Whet not wet.

Loved the article! 9/30/2014 3:29 PM | Grammar Police

# re: Top 10+ SSIS 2012 Improvements

*Whet not wet.

Excellent Article 9/30/2014 4:44 PM | Grammar Police

# re: Top 10+ SSIS 2012 Improvements

Good catch Grammar Police!

Correction has been made. 10/1/2014 11:38 PM | Arthur

Post A Comment
Title:
Name:
Email:
Comment:
Verification: