Decoding Errors with Error Column Names and in SQL Server 2016 Integration Service

Problem: When SSIS packages failed at the step of loading files, sometimes it doesn't tell which column is the one that caused failure. How to find out the troublemaker?

Solution: 

If you have SQL Server 2016 Integration Service or later version, you can retrieve error column names by using the new GetIdentificationStringByID method in SSIS 2016.

Below is an example showing how to use IDTSComponentMetaData130.GetIdentificationStringByID method to decode error column names and error descriptions by using Script Component.

Step 1: Choose script component and connect it with error output from upstream.
Step 2: Select Transformation as script component type.

Step 3: After selecting C# as script language to use, check ErrorCode and ErrorColumn as Input Columns as shown below.

Step 4: Add output columns ErrorColumnName and ErrorDesc to represent error column names and descriptions respectively, then specify the column type and length.

Step 5: Edit script and added this following to Input0_ProcessInputRow function:

public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
        
        IDTSComponentMetaData130 componentMetaData = this.ComponentMetaData as IDTSComponentMetaData130;
        Row.ErrorColumnName = componentMetaData.GetIdentificationStringByID(Row.ErrorColumn);
        Row.ErrorDesc = ComponentMetaData.GetErrorDescription(Row.ErrorCode);
    };

Then you can add a flat file output to write the errors with detailed information about error column names and descriptions.

From the following output, you can tell that the loading failure is because input column "OLE DB Destination.Inputs[OLE DB Destination Input].Columns[OrderDate]". The error description is "The data value cannot be converted for reasons other than sign mismatch or data overflow."

How to use Azure Storage Emulator for Testing

Problem: During development of SSIS packages for data transferring between Azure Storage, you want it to be fully tested on your local first before any data transfers to your Azure account. You also want to avoid unnecessary charges on your Azure account for testing. How to achieve this?

Solution: Using Azure storage emulator

We know that Azure storage is charged based on volume of data stored every month, types of operations performed, and amount of data transfers involved base on Azure Storage pricing.

In order to avoid unnecessary charges during development and testing, we can leverage Azure storage emulator to test and debug Azure cloud services locally before real data transfer occurs between on-premise and Azure account.

First, you can download and install Azure storage emulator here. During installation, you need to take note of installation paths of Azure emulators. Here mine is installed by default at: 

C:\Program Files (x86)\Microsoft SDKs\Azure\Storage Emulator

After installation, start the storage emulator by searching "Azure Emulator" after clicking windows Start button. 

If it is executed for the first time, it will do initialization first by creating a database in LocalDB and granting database access for current user.

You can also check the database created by Storage Emulator as shown below:

How to set connection to Azure storage emulator?

Here we use upload files to Azure Blob storage by SSIS as an example. (As for how to set up Azure environments for SSIS, please refer to the steps in "How to Upload Data Files to Azure Blob Storage". )

At connection manager pane in SSIS, choose "New Connection", then choose "AzureStorage" as shown below.

Next, choose "Use local developer account" instead of "Use Azure account". Then click "Test Connection" button to ensure connection with storage emulator is successful. Ensure that you have storage emulator running. You can check the status of emulator by running the following command on command console:

C:\Program Files (x86)\Microsoft SDKs\Azure\Storage Emulator>AzureStorageEmulator.exe status

You can rename the connection to the name you want. Here I rename it to "azure-emulator" for future use.

To use it with Azure related task, the main change is the connection to be the Azure emulator one you just created. Below shows the configuration for Azure Blob Upload Task. Now you are good to go to debug and test your package on your local!

Help Command in Storage Emulator

You can use help command to quickly locate commands for operations on storage emulator.

Note that storage emulator is an emulated environment running on a local SQL instance, there are Differences between the storage emulator and Azure Storage, in aspects of Blob Storage and table storage.

How to Looping through Result Sets in SSIS For Foreach Loop Container

Problem: When you would like to loop through result sets stored in an object variable, you probably will use Foreach Loop Container in SSIS. If you need to read multiple fields from object variables to use in the down stream later, how to implement it?

Solution: 

To specify the mapping by indexes in "Variable Mapping" of Foreach Loop (FEL) container.

Here we use a dynamic parent-child loading pattern as an example. For details about dynamic parent-child loading pattern, please refer to the book "SQL Server 2012 Integration Services Design Patterns".  Basically, this design pattern uses a control table to control which tables to load and which child packages to call at this execution. Later in the Foreach Loop, we can use Execute Package task to call related child packages. This dynamic parent-child design pattern is especially helpful when you have lots of tables to refresh while refreshing frequencies are different. So the parent package will be designed as shown below. It consists of two main tasks: one Execute SQL task to extract child packages to call for current execution; the other is Foreach Loop container to loop through result sets from above Execute SQL task, also use Execute Package Task to call child package at each loop.

We extract two columns from a control table by SQL scripts and loaded the results into an Object variable called User::PkgList. 

SELECT TableName, [ChildPackageName]
FROM [dbo].[PackageListWithOrder]
where IsLoad = 1
order by LoadOrder 

As shown below, User::PkgList contains table names and related package names.

Step 1: Define the ADO enumerator as usual in Foreach Loop Container.

Step 2: Go to "Variable Mappings", then specify the variables to be mapped with,following the order in your Object variables with index starts from 0. Note that you can't find the "Add" button to add additional mapping. It doesn't matter. You just click the space after first row, then you can specify the new mapping.

Then you can use these two variables in Execute Package Task to call and pass through related child packages. Run the package to see the variables' values got updated when looping through Foreach loop! Let me know if you have any questions.

How to Upload Data Files to Azure Blob Storage?

Problem: You have text files in csv or AVRO formats and you would like to upload them to Blob storage as intermediate steps of big data processing in Azure. How to upload them to Azure Blob storage?

Solution: 

Use "Azure Blob Upload Task" or  "Azure Blob Destination" in Data Flow Task in Integration Service in SQL Server (SSIS).

Before we open new Integration Service project in Visual Studio 2015, you need to do preparations to get our environments setup:

1. Sign up Azure account.
2. Install Azure feature pack for SQL Server and ensure that Azure tools appear in Visual Studio. If Azure tools are grey out, please refer to the article to resolve issues.
3. Install Microsoft Azure Storage Explorer and create a storage account.

     After you create a storage account, you should see four types of storage appearing under your storage account at Azure Storage Explorer:

        Then you can create a Blob container to contain the file to be uploaded. You can easily do it by using Microsoft Azure Storage Explore shown below. Here the Blob Container called "ssisdemoblob" is created and under this a Blob Directory "data" is also created. The summary of naming rules for Azure can be referred as this article.

Now you can go ahead to open visual studio and create a new SSIS project. 

Step 1: Create Azure Storage Connection.

            - Choose "AzureStorage" as Connection manager type. 

            - For Account Key in "Azure Storage Connection" editor, copy "Access Key" in your Azure account. 

            - Click "Test Connection" to make sure it connects successfully to your Azure account.

Below shows how to get access key for your storage account from Azure Portal.

Note that you can create Azure Storage Connection under project level connections as shown below so every package under this project can share the same Azure Storage connection.

Step 2: Configure the Azure Blob Upload Task in SSIS as shown below:

To make this upload more dynamic, here we use variable @[User::srcFolder] for local directory. You can use expression for other properties such as BlobContainer, BlobDirectory, Disable.

Moreover, you can use wildcard for FileName as in other SSIS solutions.

After executing this task, you can see the related file appears under the Blob Directory specified. Now you have your first file uploaded to Azure Blob Storage!

Note that you can also use "Azure Blob Destination" in Data Flow Task to achieve this.
Azure Blob Upload Task can be easily used to upload one more files to azure Blob storage by using wildcards for FileName. While for "Azure Blob Destination" in Data Flow Task, you have to use Foreach Loop Container together with Azure Blob Destination to load multiple files to Blob Storage.

Why "Azure" related tools are grey out in SSIS after installation of Azure feature pack?

Problem: After installation of Azure feature pack for SSIS, Azure related toolboxes are still grey out?

Solution: 

If Azure related toolboxes are grey out in SSIS, that means that the version of SQL server is different from the version of Azure feature pack. You need to change the TargetServerVersion in visual studio to the same version of Azure feature pack you installed.

For example, if you download and install Microsoft SQL Server 2016 Integration Services Feature Pack for Azure for SQL Server 2016, you need to set TargetServerVersion in visual studio to be SQL Server 2016 as shown below:

Now your project version will show: SQL Server 2016 and Azure related tools will show up in your SSIS toolbox!

SSIS's BULK insert error msg

Problem

When you are trying Bulk insert from csv file, here is the error message you may run into:

“Could not bulk insert because file 'C:csv_filename.txt' could not be opened. Operating system error code 3(The system cannot find the path specified.).”

Solution

First of all, confirm that you are specifying the UNC (Universal Naming Convention) path and not just drive letters. If you are trying to create the file to a remote location then the path should follow UNC, i.e:

             \\Server_Name or IP_ADDRESS\Shared_Folder_PATH(Name)\FILE_NAME

Note that the path may be OK if you are trying to create the file on the SAME server running SQL Server.

Secondly, make sure that the SQL Server service account has permissions to SQL Server instance.
For doing this you can use a Domain user or, create a new local user and start the services with that account.
Go to the lower left corner of the desktop, click START, input “services.msc”, then choose “SQL Server” as shown below:

Check Log On AS, a new window will pop up.

The default setting is Local System account. Try changing it with a domain user account and restart SQL Server Service after new changes.

Last but not least, make sure that this account has Read and Write permissions on the folder where you are creating the file. To do so, right click on the folder --> sharing and security --> permissions.

That’s it! Now the error message should have been swept away.

Conversion between SSIS Integers and SQL Server Strings

When you carry out database migration, one of main headaches is the conversion of different data types between sources and destinations. If you are using SSIS, be careful about the conversion between strings and int. Sometimes SSIS will fail because the integer type defined is not big enough to hold the original data. It is also known as overflow problem. So here is a summary that indicates which kind of integer data types you should use for SSIS for strings in the source table. Hope it helps!

SSIS	SQL Server	Range	Maximum String Converted
DT_I1	tinyint	0 to 255	varchar(2)
DT_I2	smallint	-2^15 (-32,768) to 2^15-1 (32,767)	varchar(4)
DT_I4	int	-2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647)	varchar(9)
DT_I8	bigint	-2^63 (-9,223,372,036,854,775,808) to 2^63-1 (9,223,372,036,854,775,807)	varchar(18)

Note that for an empty string, you can use derived column task in SSIS with expression below to convert an empty string into a NULL integer in SSIS.

LEN(strCol) == 0 ? NULL(DT_I4) : (DT_I4)(RTRIM(strCol))

You can also refer to BOL: int, bigint, smallint, and tinyint (Transact-SQL)

Hidden Gem for Data Analysis: Data Profiling Task

Data analysis and cleansing are the most time consuming parts when you want to load source data, or prepare sampling data for data mining. You can write TSQL to find out distinct values or lengths of a column that are existing in the testing data. However, when you have millions of rows or more than thirty columns for analysis, TSQL scripts might take a whole day to get the answer. Since SQL Server 2008,  SSIS has Data Profiling task that can conveniently help analyze the content and structure of data, and uncover patterns, inconsistencies, anomalies and even redundancies. Today we will give a brief demonstration about this hidden gem for data analysis.

How to view the result by Data Profiler Viewer?

Data Profiler Viewer is used to view the generated profiler by data profiling task in SSIS. You create a new package and drag “Data Profiling Task” to the control flow pane as shown.

Double click the task, a new Data Profiling Task Editor window pops up and then click “Open Profile Viewer” button.

Click the open button at the upper left corner and locate the output profile file in xml format.

Types of profiles and statistics for analysis

Then you can explore the profile. Below only show four kinds of profiler but you can have eight kinds of profiles to request:

1. Column Length Distribution
2. Column Null Ratio
3. Column Pattern
4. Column Statistics including min, max, mean, standard deviation for each column.
5. Column Value Distribution including values, counts, percentages.
6. Functional Dependency: fully or partially dependent of other column to check redundancy.
7. Candidate Key: check which column might be a good candidate for a primary key or business key.
8. Value Inclusion: check whether all values in a column exists in another table. It can be dimensions or lookup table.
   

Demo

Let us take a look at the Column Value Distribution Profile first. Click each column on the top pane and then the lower pane will display Value, Count, Percentage for that column. You can sort by value, counts, or percentage by clicking the dropdown arrow at the right side of item. For this column, you can easily tell the top three kinds of type codes are M, P, R.

Note that it won’t be an exhausted list for all the values. Here is an example for drug code. It only listed three values in the lower pane but number of distinct values is 48720. So if the variation for column values is a lot but for some values, there are only a tiny percentage records, the profile won’t display those small percentage.

In summary, data profiles generated by Data Profiling Task of SSIS is very convenient to help you understand data, find patterns, derive data rules, detect the outliners of columns for data cleansing. You can even perform data profiling to test the foreign key relationship. If you would like to find out how to set up each kinds of profile requests, Jamie Thomson’s series of SSIS: Data Profiling Task are an awesome reference.

Reference: TechNet’s Data Profile Viewer

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!

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

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.