Create SSIS Custom Control Flow Task Part 1 - Create the Project

Start from here I am going to spend a few posts to introduce my approach for creating a SSIS control flow task. 

Custom component in SSIS gives you lot of flexibilities, and can extend SSIS capacities, can improve code reusability, can improve ETL manageability, blah blah blah…  But because the custom components are created on top of SSIS, there certainly are some troubles. So what I did is create some standard procedures to simplify my job.

Now let me demonstrate my approach through a custom control flow task. The approach starts from solution setup, then goes down to individual project setting, does some coding, and finally a deployment.

The component is created by Visual Studio Express 2012 for Windows Desktop, and essentially, as long as we reference the correct DLLs, the solution can be used for 2008R2, 2012, and 2014.

Custom Control Flow Task requires two assemblies installed in GAC: one is for the Task, which will be called when the task is executed. The 2nd assembly actually contains a winform object for the IDE (BIDS, SSDT, or VS), as well as an UI object for task initialization. So our first task is to have a solution with two class library projects:

Open VS Express and create a class library project. In my demonstration, I named my project to “MyControlFlowTask”.

By default the name of the class in the project is “class1.cs”. It doesn’t make sense in most cases, so I changed the name to “MyControlFlowTask.cs”

Now as I mentioned, we need a 2nd class library project. To make the name consistent, I named the class to “MyControlFlowTask_UI.cs”

In the UI project, I then added a windows form and named it “MyControlFlowTask_Form.cs”.

The solution is ready, now we need to dig into to project level. Let’s begin with project reference

 

The reference we need is Microsoft.SqlServer.ManagedDTS, which can be found at extensions category. Note here version 11 is for 2012, 10.50 is for 2008R2, and 12 is for 2014.

Notice I had System.Windows.Forms referenced as well, by default it is not in the reference list. It is because when the task is executed in the package, I just want to show a messagebox :P (coding is not the most important thing in this series)

Add Microsoft.SqlServer.ManagedDTS into the UI project as well. But this time we need one more reference for Microsoft.SqlServer.Dts.Design.

Now we have done project references. What we need to do is to sign the projects, because after compile these assemblies needs to be added into system GAC.  I will continue the topic in my next post.

SSRS - Report Performance Tuning

Today I just want to blog experiences I had when doing SSRS report performance tuning.

The story is like this, we have a horrible report, This report contains 2 sub-reports: one is for the overall chart which is fine, the 2nd sub-report contains 4 matrices, each matrix has date, week, and quarter column groups, and has business perspectives at row groups.

The report is a campaign to date report, which shows current quarter in weekly breakdowns, and suppress previous quarters into Quartly Total. Therefore at the beginning of each quarter the subscription is fine, but when it is close to end of the quarter, 9 out of 10 the execution failed.

Long in short, what I did are

• Identify the the problem: in this case, it is a matrix to show states, retailers, offers, and rates by date.
• Isolate the problem: I create a dummy report for this matrix so I can compare the performance to my solution
• Work out the solution: I then create a new report by re-write the query and re-design the groups of the matrix
• Compare the performance: one picture worth thousand words

The tricky thing is when I wrote the backend query, I actually made it more complicated. It is because in this particular case, query is fast enough to deliver the data to report server, but processing data is too slow due to complicated grouping conditions in the matrix. So I made some groups at the query level, which slows down the data retrieval time, but improve processing time a lot.

Now some my personal ideas:

Firstly, real world is much tough then the book. We know rules such as "query the data only when you need to", or "retrieve the data you need only". But in real world you have to work it out within business scope.

Secondly, having a big picture is impotant. In this case the report is scheduled as a subscription to run at 6:00AM. So blocking is not a problem when made the query complicated.

Finally, "Best result" is enviornment oriented. For this particular report, sacrificing few seconds in query time gives you 10 times benefits at processing level.

SSRS - Create a QR report

QR code is becoming popular. So what if we put QR code into the report, such as a contact report with QR codes? Sounds interesting, isn't it? Following steps below you can create a simple report with QR code integrated, no code required!


Firstly we need a report to show some basic contact information. In this example, I just make it very sample, as shown below

Then I drag and drop a tablix control on to the report. After set first few columns, I drop an image into the last column

Now here is the interesting part: the QR code actually is coming from http://qrcode.kaywa.com/. This site can encode your input into QR code (static) and export the result as an image. And for personal use, you can create a free account. So what we need to do is to set the image property to the output of this site

As you can see, I change the image source to external, and then I set the image expression to:

 = "http://qrcode.kaywa.com/img.php?s=8&d=" + Fields!name.Value + "%0A" + Fields!site.Value + "%0A" + Fields!mob.Value  

Now run the report:

When you run the report, you may see the warning message:

"Images with external URL references will not display if the report is published to a report server without an UnattendedExecutionAccount or the target image(s) are not enabled for anonymous access."

It is because of reporting service execution account setting. To configue a default reporting service execution account, you can open Reporting Service Configuration Manager, and navigate to Execution Account page, fill in default account information, as shown below.