Latest Articles: SSIS

Excellent post... SSIS 2005 is getting old, it has some inherent limitations... no SFTP, no good tasks to consume XML, no native task to zip files !

In my case, we perform an extract and dump it into a csv file... that csv file extract it's huge, what about zipping it before transfer it over the network? (the ratio is hundred of Mb vs less than 10 Mb).

Checking in SSIS for that, we found that we can use Winzip or similar at command line level (try to convince the IT Admin to install such tool on their machines), use some commercial SSIS custom task (not expensive, about 500 $), or code it your self, using some of the open source libraries available (cowboy developer, let's reinvent the wheel :) ).

Hopefully, the folks from the Joy of Code have posted this excellent task for SSIS... zipping files task for free, excellent work !!!

Determine which version or which service pack is installed on a given SQL Server instance is not something straight forward.

In this doc you will find how to check which version of SQL Server is installed just be execution a single SELECT statement.

Second part of this post, interesting details (some functionallity to get changes in a DB, some other to perform merges...

Interesting, new stuff added to SSIS on 2008, the one that I loved is being able to use C# on custom tasks :-)

Inserting bulk data into a table it's easy, but... having two tables that need to be synchronized is that an straight forward task

This article explains how to copy all the rows from an origin table to a destination one, taking into consideration that somes rows doesn't exists (insert / create) and some other yes (update)

you will find two SQL script solutions and two based on SSIS

When exporting data to csv files, it is not possible to format columns when opening the csv file in excel without any macros or user actions. E.g. when you export the value 012 and open the file in Excel, the value will be displayed as 12. Even if you export the value as '012, "012" or "'012" the result after opening in Excel is not satisfying as the value is now shown as '012.

SSIS is a bit limited when generating Excel, you can just generate plain data.

This post explains you a trick to add formatting to your tabular reports, the tricks consists in format an Excel file and use it as template (Excel destination but not over a new file, write over an existing one). This solution won't work for all scenarios, and won't give you all the formatting that you expect, but at least it's something to try before going for a custom script task (XMLSS, Open XML, ...)

Many ex-DTS users are miffed that the SSIS expression language does not have an equivalent of the VB Script IsNumeric() function that is used so extensively in DTS and to be honest, its a fair shout.

As I said above, the SSIS expression language does not have an IsNumeric() function so the Derived Column component cannot be used to do the same. Script Component to the rescue!!! The following code inside a script component will achieve the same.

Sometimes you have to import a column that you suposse that is numeric but comes as string (e.g. a CSV file, or a staging table where all the fields are text), SSIS does not have an already made box for this task... but you can always use .net in SSIS !!

This articles explains you how to check if a given column / row value is numeric using SSIS and a .net script task.

How to launch an SSIS job from a remote machine (e.g. an ASP .net application hosted on an IIS).

You will find two approaches on using ADO .net (calling a system stored proc) the other one via Web Service.

Very interesting slide deck about lessons learned migrating from 2000 to 2005 (SSIS, SSAS, and SSRS).

One of the most interesting points are cases where performance was lower after the migration... why? Bad migration practices, try maintain a lot of legacy code, massive use of wizards...

Another interesting area covered are the limitations of the current product.

Cache-warming is one of the most neglected performance-tuning techniques for Analysis Services: perhaps it seems too much like cheating? Yet almost everyone knows how much difference there can be executing a query on a cold cache and a warm cache so there should be no excuse not to be doing it, especially if you know what queries your users are likely to be running in advance.

you can find a lot of resources about proactive caching and SSAS (meanwhile a cube is being updated hold a copy of the cube to let the users run meanwhile the cube is processed)

That's nice, but... What about MDX caching?

If your users are browsing "canned" reports, you can take advantage of SSRS caching features (quite easy to use and quite useful).

But... if your clients are using ad-hoc reporting tools (e.g. Proclarity Professional or Excel BI capabilities), you can see how your cube/servers can throw a low performance.

Normally cube data, has a latency (e.g. the cube is updated every day, or every week), so for sure a lot of similar MDX queries are executed over and over consuming CPU cycles and memory. Why not take advantage of SSAS, MDX native cache?

This article explains you how to create an SSIS cache warm up process.

To create the source of MDX queries to feed that SSIS process, you can just use profiler, or log that MDX queries, do not forget to clean up the cache once the cube has been updated

Very basic intro to Fuzzy Lookups on SSIS.

Use fuzzy lookups when you want to find possibly repeated entries (e.g. a client with same name, a typo on last name, but same phone number is a candidate).