Decipher SSIS Error Codes: -1071607685

When using SSIS as tools to loading files, you usually can get a very clear error message that indicates what is going wrong. You can tell which column is wrong from ErrorColumn and for which reason the column brought failure from “ErrorCode – Description”.

However, when loading a source file that is not formatted as expected, if you have got an error output with "No Status is available" as ErrorCode and “0” as ErrorColumn as shown below, what do you feel?

Do you feel like lost in darkness? Somewhat …

Here is my recent experience in helping out troubleshooting file loading problem. Since there is no clue, all I can do is to check all constraints on all columns for potential trouble-makers.

Finally, it turned out that the trouble-makers is one of the obsolete columns that used to be NOT NULL, but no more input at the current loading. The solution is easy. You need to allow that column to have NULL values.

During the research for the clue, I uncovered two helpful resources to decipher SSIS error codes: 

• Integration Services Error and Message Reference
• header file dtsmsg.h

The header file dtsmsg.h is under the folder

C:\Program Files (x86)\Microsoft SQL Server\110\SDK\Include (for SQL Server 2012)

The two resources covered the five kinds of messages as shown. The online one is in table form and easier to follow, while the header file is more precise and detailed in technical terms.

So next time, hope you will feel more confident when you get SSIS error messages!

Do you know NCQA, NQF, or PQRS?

Do you know how quality measures are set to evaluate physicians’ performance? Below is the list of abbreviation for nationally recognized entities who provide guidelines and requirements for provider transparency methodologies and programs.

ADA: American Diabetes Association

AQA: Ambulatory Care Quality Alliance

AACAP: American Academy of Child and Adolescent Psychiatry

AACE: American Association of Clinical Endocrinologists

APA: American Psychological Association

AHRQ: Agency for Healthcare Research and Quality   

BTE: Bridges to Excellence   

CHIPRA:  Children's Health Insurance Program Reauthorization Act

PQRS: CMS Physician Quality Reporting System

NCQA HEDIS: Health plan Employer Data and Information Set

NCQA DRP: Diabetes Recognition Program

NCQA HSRP:  Heart/Stroke Recognition Program

NQF: National Quality Forum

PCPI: Physician Consortium for Performance Improvement

PQA: Pharmacy Quality Alliance

Tableau, Qlikview, or Power BI?

Nowadays, there are lots of excellent products for turning fast-paced digital information into easily understood images and insights. So the common questions are: Which BI tool should I choose? Which BI tool has the most promising future? While waiting for this year’s Magic Quadrant for Business Intelligence and Analytics Platform, I’d like to share with you with Jen Stirrup’s opinion from a user or customer respect.

In her article Business Intelligence Barista: Mixing your choice of BI Coffee with Tableau, Power BI or Qlikview?, Jen compared Tableau, Qlikview and Microsoft Partner PivotStream from business criteria, data visualization criteria, and technical criteria. The comparisons are very clearly delivered in three tables for each category. She gave her scores in Excellent, Good, Yes/No, Limited for these three BI tools. If you would like to find why she gave the related scores, don’t forget to read her comments at the far right column.

Jen included the following as business criteria: time to implement, scalability, enterprise ready, long-term viability, free online help, big data support, size of partner network. As for visualization criteria, she compared data interactivity, visual drilldown, offline viewer, dashboard support, web/mobile clients etc. (It seems that Tableau has a “wow” factor in terms of eye-catching interactive visualization.) Last but not least, she discussed technical criteria in aspect of data integration, OLAP cubes, data modeling, data mining/science, multi-dimensional, xVelocity support, PowerPivot suppot, API, etc.

At last, Jen concluded that “There is no wrong choice, if it is the right choice for your organization.” The key to make decision on BI tools is to find out user requirement or buyers’ needs. I can't agree more on that. We need to know what we really need and what are the priorities before we go shopping for solutions.

Although Jen mentioned Power BI solution was not included in the comparison since it is still in preview, she mentioned Power View and Power Map as comparing to the other products. I wish that she could include product functionality and learning curve for developers for these three products as well.

Further reading for more qualitative analysis of BI market: 2013 Gartner Magic Quadrant for BI.

Debugging a Script Component in SSIS

As we have discussed how to debug a script task in SSIS, some readers asked “how to debug a script component in SSIS”. Before I wrap up my own examples, I highly suggest reading these two excellent articles:

1. Script Component Debugging in SSIS 2012

2. Breakpoint does not work within SSIS Script Component

You will learn three main methods to monitor a script component:

1. Display a modal message by using the MessageBox.Show.
2. Raise events for informational messages, warnings, and errors.
3. Log events or user-defined messages.

Also you will get an idea about the limitation of situations that you can debug a script component for current versions of SSIS. 

Debugging SSIS Script Tasks

SSIS Script tasks provide codes to implement customized functions that a built-in SSIS task cannot achieve directly. It is often necessary to debug through a Script task to ensure it works properly. You can set breakpoints for events such as OnPreExecute or OnPostExecute from ten break conditions as we discussed in Set Breakpoints for SSIS Debugging. Moreover, you can define stopping points in a script task through Microsoft Visual Studio Tools for Applications (VSTA).

Set a breakpoint in VSTA for a Script Task?

After you click “Edit Script” button in Script Task, a VSTA window will open.

Click the line you would like to set a breakpoint. Then right-click it and select Breakpoint –> Insert Breakpoint.

Below shows setting breakpoints in SQL Server 2008 R2.

Now you have the breakpoint set right before Messagebox.Show(). This means that the execution should break immediately before the message box is popped up.

SQL Server 2012

The way to set breakpoints in Script Tasks for SQL Server 2012 is similar as shown.

Also, after clicking Insert Breakpoint, a popup window will let you to do more fine tunings for your breakpoint through locations of Line and Character.

Note that SQL Server 2012 has one additional option: Insert Tracepoint besides “Insert Breakpoint”.

 

How breakpoints work?

After you set up the breakpoint, you can close Script Task Editor window. Right Click the Script Task and then select Execute Task. You would expect a yellow arrow displayed at the breakpoint.

After you press F10 button, a message box pops up with the message “Hello World”. After you close the popup window, the VSTA window should appear like this.

Tweaks for SQL Server 2008

If you use SQL Server 2012, you are lucky to get the breakpoint hit as described above.

If you use SQL Server 2008, you need some tweaks to make breakpoints working as expected:

Step 1: Set SSIS runtime mode as 32 bit at PROJECT Level.

Step 2: Re-open Script Task Editor window and save it again. In this way, your script task code is set to be compiled as 32 bit.

 

Shortcut Keys for Debugging

You can use debugger shortcut keys to speed up debugging. SSIS Script tasks share the same set of shortcut keys for Visual Studio. Here is a list of some common shortcut keys.

Keys	Functions
F5	Run the application.
F10	Step Over (Execute the next line of codes but not follow execution through any function calls).
F11	Step Into.
SHIFT+F11	Step Out.
CTRL+BREAK	Stop execution (Break).
F9	Toggle breakpoints.

Now I hope that you have a solid understanding of how to debug script tasks in SSIS. Take time to play with it and you will find it save the day for you in troubleshooting. If you have any questions, please feel free to leave a comment.

Reference:

1. Debug a Script by Setting Breakpoints in a Script Task and Script Component
2. Debugger Shortcut Keys for Visual Studio

Best Practices for ETL Design #10: When to Truncate Tables

Truncating tables is a common practice when you need the table to be totally refreshed. In Microsoft SQL Server Integration Service, it is often implemented through “Execution SQL Task” at the very beginning of ETL packages. You may never think about it would sometimes give trouble. Here is some worst possible scenario:

Let us imagine the target table to be refreshed is a report that users may run at any minute. If we truncate the target table at the beginning. However, for some reasons, there is some problem to get the rest of ETL package to complete immediately. Or it takes more than five minutes ( or the time that the end users could bear with) to finish the ETL package and to load new data to the target table.

Then what will happen to users on the reporting side? They will come back to you and complain the report is down!

Solution

Therefore, in order to avoid the above worst scenario, in your ETL packages, please truncate the final key target table at the LAST minute when its new data is ready to load. Satisfy end users with your reports always on!

Set Breakpoints for SSIS Debugging

Like debugging codes for other languages or tools, it is often necessary to set breakpoints to pause execution so you can examine variable values where you think the problem can be. SSIS provides a very straightforward GUI to help you set breakpoints in SSIS packages.

Where to set breakpoints?

You can set breakpoints on a task or a container. A task can be Execute SQL Task, data flow tasks, script tasks, etc. A container can be a For Loop container, a Foreach Loop container, or a Sequence container.

Moreover, there are eleven break conditions that you can choose from as shown below

These break conditions are defined as:

1. OnPreExecute: Called by a task or a container immediately before it runs.
2. OnPostExecute: Called by a task or a container immediately after it runs.
3. OnError: Called by a task or container when an error occurs.
4. OnWarning: Called when the task is in a state that does not justify an error, but does warrant a warning.
5. OnInformation: Called when the task is required to provide information.
6. OnTaskFailed: Called by the task host when it fails.
7. OnProgress: Called when there is measurable progress about task execution.
8. OnQueryCancel: Called at any time in task processing when a cancel execution is fired.
9. OnVariableValueChanged: Called when the value of a variable changes. The RaiseChangeEvent of the variable must be set to true to raise this event.
10. OnCustomEvent: Called by a custom task-defined events.
11. Loop iterations: Called when the iteration condition in a loop is satisfied. This only appears for a For Loop container and a Foreach Loop container.

Moreover, there are four Hit Count types you can define:

• Always
• Hit count equals
• Hit count greater than or equal to
• Hit count multiple

After you define a Hit Count Type, you can specify a Hit Count at which the breakpoint executes. This is especially useful when you want to skip some iterations and break at some specific iterations.

How to set breakpoints?

In SSIS designer, navigate to the control flow panel.  Right-click the object where you want to set the breakpoint and then click the Edit Breakpoints option. You will see a Set Breakpoints window popup just like the picture shown above.

Next, select break conditions you like to have. Here you can combine multiple break conditions. For example, you can let it break at both OnPreExecute and OnPostExecute events so that you can examine the changes on variables. The default Hit Count Type is grayed out when the related break condition is unchecked. Once a break condition is selected, you can go further to define Hit Count Type and Hit Count. Below is a breakpoint set at a loop when its loop iteration is equal or larger than 2.

After you close the Set Breakpoints window, you will notice a red dot appears on the object with breakpoints.

Furthermore, setting breakpoints in SSIS Script tasks is a little different from the way shown above. If you would like to know more, please stayed tuned.

How to modify breakpoints?

You can modify the breakpoint in the same way you set breakpoints. Right-click the object and then click the Edit Breakpoints option.

Now you have a powerful tool to help your troubleshooting effectively. With breakpoints, you can step through your ETL package to keep track of your variables and status of packages.