Tag Archives: SSIS

Articles and examples on How To use Microsoft SQL Server Integration Services (SSIS).

#0412 – SQL Server – SSIS – Error – The value type (__ComObject) can only be converted to variables of type Object. Variables may not change type during execution.


Recently, we were manipulating a string in an “Execute SQL” task inside a SSIS package, when we ran into the following sequence of errors.

[Execute SQL Task] Error: The value type (__ComObject) can only be converted to variables of type Object.
[Execute SQL Task] Error: An error occurred while assigning a value to variable "MyStringVariable": "The type of the value (DBNull) being assigned to variable "User::MyStringVariable" differs from the current variable type (String). Variables may not change type during execution. Variable types are strict, except for variables of type Object.".
Error: The type of the value (DBNull) being assigned to variable "User::MyStringVariable" differs from the current variable type (String). Variables may not change type during execution. Variable types are strict, except for variables of type Object.

The Execute SQL was similar to something that we had done hundreds of times before, and therefore we were stumped by the error. I found the root cause interesting and hence wanted to write about it right away.

The Test Setup

Before we go ahead, allow me to walk through the sample SSIS package which we used to reproduce the issue. As I mentioned, it is a simple SSIS package with a single “Execute SQL Task”.

0412_01_SSISExecuteSQLTask

The Execute SQL task in the sample SSIS package

The “Execute SQL” task simply executes a T-SQL statement that returns a single-row result set and sets a package variable of type “string“.

DECLARE @myVariable VARCHAR(MAX);

SET @myVariable = 'SQLTwins';

SELECT @myVariable AS myVariable;
0412_02_SSISVariable

User Variable of type “string” in the test package

0412_03_SSISExecuteSQLDetails

Execute SQL task details showing sample T-SQL script

0412_04_SSISResultSetVariableMapping

Variable Mapping in the Execute SQL Task

When we execute this SSIS package, it fails with the error referenced above.

0412_05_ExecuteSQLFailure

Failed Execute SQL Task

0412_06_ExecuteSQLFailureDetails

Execute SQL Task Failure Details

The Solution

The solution was right there in our faces, but we failed to notice it for a while. If we read the error message carefully, we can isolate the following points:

  • The data-type of the variable from the Result Set output of the Execute SQL task is different from the data-type of the target user variable
  • SSIS detects this as an attempt to change the data-type, which is not allowed because variables types are strict unless defined as an “object”

Based on this, we set about looking at differences between the single-row result set and the SSIS user variable of type “string”. We soon realized that the result set was returning a VARCHAR(MAX).

It appears that the (MAX) was causing problems in the SSIS engine. As soon as we changed it to a fixed-length variable the package worked as expected.

DECLARE @myVariable VARCHAR(8000);

SET @myVariable = 'SQLTwins';

SELECT @myVariable AS myVariable;
0412_07_ExecuteSQLSuccess

Successful execution of Execute SQL after changing to a fixed-length data-type

Hope this little tip helps in your development efforts someday.

Until we meet next time,

Be courteous. Drive responsibly.

 

Advertisements
SSDT 15.5.2 for Visual Studio 2017 Installation Error: 0x80072f76

#0411 – SQL Server – SSDT 15.5.2 for Visual Studio 2017 – Installation failed with error 0x80072f76: Failed to acquire payload


I was recently building up an all-in-one development environment for a project and ran into an unexpected error. I had already installed Microsoft Visual Studio 2017 and attempted to install SQL Server Data Tools (SSDT).

The SSDT 15.5.2 for Visual Studio 2017 failed to install with the following error.

SSDT 15.5.2 for Visual Studio 2017 Installation Error: 0x80072f76

SSDT 15.5.2 for Visual Studio 2017 Installation Error: 0x80072f76

Upon studying the error log file, I found the following sequence of unexpected entries:

Acquiring package: Microsoft.DataTools.AnalysisServices, payload: pay98911873C1CF2F7FF48824555D2B0337, download from: https://go.microsoft.com/fwlink/?linkid=866936
Error 0x80072f08: Failed to send request to URL: https://go.microsoft.com/fwlink/?linkid=866936, trying to process HTTP status code anyway.
Error 0x80072f76: Failed attempt to download URL: 'https://go.microsoft.com/fwlink/?linkid=866936' to: 'C:\Users\sqltwins\AppData\Local\Temp\2\{5C5CD709-A276-454C-88E3-0E939CB80B0E}\pay98911873C1CF2F7FF48824555D2B0337'
Error 0x80072f76: Failed to acquire payload from: 'https://go.microsoft.com/fwlink/?linkid=866936' to working path: 'C:\Users\sqltwins\AppData\Local\Temp\2\{5C5CD709-A276-454C-88E3-0E939CB80B0E}\pay98911873C1CF2F7FF48824555D2B0337'
Failed to acquire payload: pay98911873C1CF2F7FF48824555D2B0337 to working path: C:\Users\sqltwins\AppData\Local\Temp\2\{5C5CD709-A276-454C-88E3-0E939CB80B0E}\pay98911873C1CF2F7FF48824555D2B0337, error: 0x80072f76.
MainViewModel.OnPackageAction: Install CompletedDownload for package SQL Server Analysis Services (id: Microsoft.DataTools.AnalysisServices)
Error 0x80072f76: Failed while caching, aborting execution.

From the error log entries, it is clear that the installer program was unable to access a particular URL in order to download the respective installer components.

So, I took the URL “https://go.microsoft.com/fwlink/?linkid=866936”, pasted it in the address bar of a browser and immediately ran into a problem:

Your current security settings do not allow this file to be downloaded.
Enhanced Security Configuration (ESC) preventing file downloads

Enhanced Security Configuration (ESC) preventing file downloads

This clearly indicates that the Internet Explorer Enhanced Security Configuration (IE-ESC) was preventing the download and in-turn resulting into the error.

Solution

I immediately added microsoft.com to the “trusted sites” zone and restarted the installer. This time, the installer completed successfully! (One may suggest to disable Enhaned Security Configuration altogether, but that is not recommended due to the obvious security reasons.)

SSDT 15.5.2 for Visual Studio 2017 Installation continues after necessary package URLs are allowed in Enhanced Security Configuration

SSDT 15.5.2 for Visual Studio 2017 Installation

Hope this helps you someday when you are setting up your environments.

References

  • Download SQL Server Data Tools (SSDT): https://docs.microsoft.com/en-us/sql/ssdt/download-sql-server-data-tools-ssdt
  • Internet Explorer Enhanced Security Configuration (ESC): https://support.microsoft.com/en-in/help/815141/internet-explorer-enhanced-security-configuration-changes-the-browsing

Until we meet next time,

Be courteous. Drive responsibly.

#0400 – SQL Server – SSIS – Using the SQL Server Destination


SSIS packages are quite easy to get started with – it’s mostly drag and drop of various containers, tasks and setting of connections. Ensuring that the components work optimally requires using the right mix of tasks based on the scenario at hand.

Often SSIS packages connect to remote data sources & destinations. However, there are cases where the destination is a Microsoft SQL Server and it is required to run the package on the same server where the instance is hosted and we do not need granular grouping. Such situations may include data import into a staging area during migrations or as part of an ETL.

In such situations, the SQL Server destination may prove to be a better option as compared to the OLE DB destination.

Generally,  we would have a data pipeline with an OLE DB destination on the receiving end. The setup for using SQL Server destination is extremely simple – the only change is replacing OLE DB destination with the SQL Server destination. The SQL Server destination performs Bulk Inserts into the destination SQL Server while leveraging shared memory connections to SQL Server over the existing OLE DB connection manager.

The  screenshots below indicate the simplicity of using the SQL Server destination.

01_sqldestinationtask

Adding the SQL Server destination to a data flow

02_sqldestination_connectionmanager

Selecting a connection manager

03_sqldestination_advancedpane

The “Advanced” tab of the SQL Server destination

The Advanced tab (see above) has a host of options to improve the performance and control the behaviour of the bulk inserts made by the SQL Server destination.

  • Keep Identity – controls whether to insert values into an identity column
  • Keep Nulls – controls whether NULLs should be inserted instead of using the default values defined on the column
  • Table Lock – allows to take a higher-level table lock during the bulk insert
  • Check Constraints – controls whether constraints should be checked during the insert or not
  • Fire Triggers – controls whether or not to fire DML triggers defined on the table
  • First Row – specifies the first row to insert. By default all rows are inserted
  • Last Row – specifies the last row to insert. By default all rows are inserted
  • Maximum number of errors – controls the number of errors before the bulk insert operation stops
  • Timeout – controls the bulk insert operation timeout
  • Order Columns – Allows a user to specify the sort order on one or more columns

Summary

The SQL Server Destination is recommended instead of the OLE DB destination if the SSIS package is to be executed on the same machine/server where the target Microsoft SQL Server instance is located. Below are the finer points about the SQL Server destination:

  1. The SSIS package must be executed on the same server where the Microsoft SQL Server instance is located
  2. The Shared Memory protocol for data exchange is enabled for the instance from the SQL Server Configuration Manager
    • Warning: This may need local security policy updates if User Access Control (UAC) is configured
  3. SQL Server destination
    • Only works with OLE DB connection managers (ODBC is not supported)
    • Supports only one input
    • Does not support an error output
    • Performs bulk insert of data
    • Allows leveraging of fast load options of the OLE DB connection

Further Reading

Until we meet next time,

Be courteous.  Drive responsibly.

 

#0399 – SQL Server – SSIS – Debugging – Exploring the Data Viewer


I often get questions regarding debugging of SSIS packages with the most common scenario being a need to “see” the data flowing  down the data pipeline. SQL Server Integration Services (SSIS) packages are explored and edited visually within the SQL Server Data Tools (SSDT) and hence, the mechanism to troubleshoot an SSIS package when developing it also has to be visual.

The Data Viewer allows a developer to pause the data flow in a data flow task and look at the data as it is on that point in the pipeline. Today, I will introduce you to the Data Viewer.

Sample SSIS package to demonstrate Data Viewer on the Data Flow Pipeline

Sample SSIS package to demonstrate Data Viewer on the Data Flow Pipeline

The data flow is quite simply taking all the records from the [HumanResources].[Employee] table of the sample [AdventureWorks2014] database and putting into an object variable via the Recordset destination (I am using the Recordset destination for the demo here for the sake of simplicity).

Using the Data Viewer

If I want to study the data flowing through the data pipeline, all I need to do is right-click on the data flow path and choose “Enable Data Viewer” (a data flow path being the connection between a source and a transformation, between two transformations or a transformation and a destination).

0399_image2

Enabling the Data Viewer on a Data Flow Pipeline

0399_image3

Magnifying glass indicates Data Viewer is enabled

As can be seen from the screen grab above, a magnifying glass icon on the data flow now appears indicating that the data flow is configured for viewing.

If I execute the SSIS package at this point, I see that the data flow pauses just before it starts writing to the destination and opens a new grid window. The window resembles a normal dataset viewer (if you are familiar with developing C# or ASP.NET applications in Visual Studio, you would feel right at home!) which contains the data flowing through the data pipeline. You can even copy this grid to Excel or any other file for further research (Tip!).

Data Viewer showing data flowing through the data path

Data Viewer showing data flowing through the data path

Once you have studied the data flowing through, you can choose to either stop the transaction (by stopping execution of the package) or allowing the package to execute through by clicking on the “green” arrow on the data viewer.

Allowing the data flow to continue down the pipeline by allowing the package to resume execution

Allowing the data flow to continue down the pipeline by allowing the package to resume execution

Filtering columns displayed on the Data Viewer

Sometimes, we may not want to sift through all the columns in the data pipeline. If we know that a particular column is causing some problems, we may just want to monitor that column. One can explicitly choose which columns should be displayed on the data viewer by going into the data flow properties.

  1. Right-click on the data flow path and choose “Edit”
  2. Go to “Data Viewer”
  3. Use the arrow buttons to selectively choose which columns to display in the data viewer
Selectively choosing columns visible on the Data Viewer

Selectively choosing columns visible on the Data Viewer

Summary

The Data Viewer can be used for design time troubleshooting of an SSIS package, allowing developers to pause the data flow and monitor the data flowing down the data pipeline.

  • You can have multiple data viewers in your data flow task so that you can monitor each part of the data flow
  • You can control the columns seen in the data viewer so that you can focus on the fields of interest
  • You can copy this data for further research/maintaining a record
  • If you have transformation components that use 64-bit components, you may need to turn off the “Run64BitRuntime” under the Debug options (Debug -> Solution Properties -> Configuration Properties -> Debugging)

Further Reading

Until we meet next time,

Be courteous. Drive responsibly.

Overview of the sample SSIS package using the SORT operator

#0398 – SQL Server – SSIS – Performing a DISTINCT using the SORT transformation


Developing SSIS packages is quite easy – it’s mostly drag and drop and some minor configuration. Choosing the right transformations and components and making them work optimally is a task that is heavily dependent upon the use case at hand.

Normally, when working with relational data sources, I am averse to using the SORT transformation within a data flow, because it is a blocking transformation. Blocking transformations not only consume client memory, they also act as roadblocks in what should be an uninterrupted data pipeline. The performance tip (!) therefore is to prefer that sorting, if required, is done as part of the data source query itself so that the data pipeline gets presorted data.

However, presorting of data may not be possible in all cases. For example, if you are receiving data from a web-service, XML data source or any other 3rd party custom component. A SORT transformation becomes essential in such scenarios, but one of the areas where I have seen most people stumble upon is that a custom query is later used to remove unnecessary records from the set. The custom query is not required in most cases, and I will be demonstrating how to use configure the SORT transformation to also perform a DISTINCT.

Demo

Assume that:

  • You just need a list of unique patrons who visited a local library this year in order of their registration Id for sending out some promotional mails
  • The promotions need to be sent irrespective of the visit date and number of visits within the year
  • The input (log of library visits) is coming from a web-service of some sort over which you as an integration team member have no control over
  • Following is a sample of the test data that comes over from the web-service

NOTE: For the purposes of this demo, I will use a T-SQL SELECT to generate my test data, but I would request the reader to assume that the data is coming from a non-relational source.

SELECT LibraryVisitLog.PatronId,
       LibraryVisitLog.PatronName,
       LibraryVisitLog.VisitDate
FROM (VALUES (1201,'John Doe',  '2017-01-05'),
             (1180,'John Smith','2017-01-05'),
             (1201,'John Doe',  '2017-01-06'),
             (1201,'John Doe',  '2017-01-07'),
             (1201,'John Doe',  '2017-01-08'),
             (1201,'John Doe',  '2017-01-09'),
             (1201,'John Doe',  '2017-01-10'),
             (1280,'Jane Doe',  '2017-01-10'),
             (1201,'John Doe',  '2017-01-11'),
             (1201,'John Doe',  '2017-01-12'),
             (1210,'Jack Smith','2017-01-12'),
             (1201,'John Doe',  '2017-01-13'),
             (1201,'John Doe',  '2017-01-14'),
             (1180,'John Smith','2017-01-14')
     ) AS LibraryVisitLog (PatronId, PatronName, VisitDate)
Sample Test Data to be used for the demo

Sample Test Data

We only want the information about the following 4 (four) unique patrons ordered by their Id down the pipeline.

Patron Id Patron Name
1180 John Smith
1201 John Doe
1210 Jack Smith
1280 Jane Doe

Configuring the SSIS package to achieve this is quite simple. As shown in the image below, I have a SORT transformation between my data source and target.

Overview of the sample SSIS package using the SORT operator

Overview of the demo SSIS package

The SORT transformation is configured to:

  • Ignore the VisitDate (as established in the requirements above)
  • SORT based on the PatronId
  • Pass the PatronName through the pipleline
Configuring the SORT operator to perform a DISTINCT on the output rows

Configuring the SORT operator to perform a DISTINCT on the output rows

In addition, notice that I have checked the checkbox “Remove rows with duplicate sort values” in the bottom left corner. This switch controls whether the transformation copies duplicate rows on the transformation output or creates a single, unique entry for all duplicates.

To actually see the data flowing through the pipeline, I have enabled DataViewers on each stage of the data flow. You can see the actual data flowing through the pipeline in the screenshots provided below:

Demonstrating the ability of a SORT transformation to perform a DISTINCT on the data

Demonstrating the ability of a SORT transformation to perform a DISTINCT on the data

0398-image05

Data Viewer showing input data

0398-image06

Data Viewer showing sorted and Distinct data

Summary

With respect to the SORT operator and achieving a balance between the performance and limitations of the data sources used by an SSIS package:

  • It is a best practice to pre-sort the data at the data source before sending it across a data flow pipeline
  • In case pre-sorting is not possible, but sorting is required, one can use the SORT transformation
  • Leverage the SORT transform to filter out unique records from the sorted output – custom logic is not required to do so

Further Reading

Until we meet next time,

Be courteous. Drive responsibly.