2016

Get error column name in Data Flow Task in SSIS

How to get error column name and error description in Data Flow Task in SSIS During execution of an SSIS package, when a bad row comes in the data flow task, the task gets failed. However, most of the components (source, transformation, and destination) in the data flow task exposes an error output path which can […]

Get error column name in Data Flow Task in SSIS Read More »

Unfolding | Set Based Approach | Get Continuous Start Date of the Current Project – Episode 8

In this video, we are going to learn; How and when to use Apply operator in SQL Server, and How to compare rows on a given logic You can download the script used in this example from below the video.

Unfolding | Set Based Approach | Get Continuous Start Date of the Current Project – Episode 8 Read More »

Dynamic SQL in OLEDB source component in SSIS

The metadata could not be determined because statement contains dynamic SQL When we use a batch of T-SQL statements which contains dynamic SQL or uses temporary tables inside, in an OLEDB source component placed in a Data Flow Task of a SSIS package, we get an error during column names extraction because the source component gets failed to fetch the metadata (columns and their data types)

Dynamic SQL in OLEDB source component in SSIS Read More »

STRING_SPLIT function – SQL Server 2016

Finally, STRING_SPLIT function, one of the most awaiting feature for a long time, has been introduced by Microsoft. In SQL Server 2016, now, we have a built-in system function to split a string with a specified separator. Earlier, whenever we had to map a denormalized string (e.g. comma separated string values) with the values stored in a table column (normalized

STRING_SPLIT function – SQL Server 2016 Read More »

DATEDIFF and DATEDIFF_BIG – SQL Server 2016

We are already familiar with DATEDIFF function introduced in the very initial version of SQL Server. But, in SQL Server 2016, Microsoft has introduced DATEDIFF_BIG function which can be used to compute the difference between two given dates in terms of the given date part. In this post, we are going to explore the use of

DATEDIFF and DATEDIFF_BIG – SQL Server 2016 Read More »

Unfolding | Set Based Approach | Create Attendance Report – Episode 7

In this video, we are going to learn; Generate date list from the given date range dynamically Repeat the date list for each employee without using a loop or recursive CTE You can download the script used in this example from below the video.  

Unfolding | Set Based Approach | Create Attendance Report – Episode 7 Read More »

Select all elements regardless of level in XML

We have already published a post on “Read XML Data as a table in SQL Server” and “Read and compare XML nodes dynamically with unknown elements“. In this post, we will see that how we can skip levels in XML string to read all the elements at a given level regardless of the level name. A wildcard character * (asterisk) can be used

Select all elements regardless of level in XML Read More »

Unfolding | Set Based Approach | Generate Rows Dynamically – Episode 6

In this video, we are going to learn; How we can Generate rows dynamically for each record of a table in set based approach, and How to do calculations on each row of a table generated dynamically You can download the script used in this example from below the video.

Unfolding | Set Based Approach | Generate Rows Dynamically – Episode 6 Read More »