Showing posts with label SSIS. Show all posts
Showing posts with label SSIS. Show all posts

Publish an SSIS Package result set as a SQL View

From SQL server version 2012 onwards there is an in-built mechanism to publish SSIS package result set into SQL view

1.    Requires SSIS package to be designed with Data Streaming Destination.

This destination component needs to be separately downloaded in 2012 and 2014. From 2016, it is part of standard toolbox item.

2.    Running Data Feed Publish Wizard

    i)  Creates a linked server – SSISOLEDB.

    ii) Creates a SQL view in the specified database.

Below are the steps to publish an SSIS Package result set as a SQL View

Microsoft Azure - Move data from another computer to SQL Server instance on VM using SSIS

In this article, let us see on how to move data from another computer over internet to SQL Server instance on virtual machine using SSIS.

If you have no idea in creating and connecting to Azure SQL Server VM, Please refer below links :

In this post, let us also see on how to import JSON file on local computer to SQL Server on VM through this example.

SSIS - Loop through recordset and load into multiple file dynamically

In this article let us see an example on
*) how to loop through records in a table in SSIS
*) how to load into multiple (.sql) file dynamically

Consider the requirement is to generate index script for tables that belong to particular schemas and export as separate .sql files.

Long back I wrote an article on How to Generate Index Creation Scripts for all Tables in a Database using T-SQL

Going to create this script as Stored procedure:

SSIS - Export XML - XML destination

In this post, I will share with you on how I have exported xml returned from a Stored procedure as xml file in a folder using SSIS.
Below is my Stored procedure:

SSRS - Make common change in multiple reports in one click

Consider a scenario, say if there are more than 100 reports developed already. Each & every report has company logo on header as embedded image. Suddenly, if the company has decided to make minor change in the logo and all the reports needs to be modified with new logo image.

To make common change in multiple reports in one click, we need to create 
  1. Stored procedure 
  2. SSIS package

 Worked Example:

 I have created six sample reports with "SQL Server 2014" image in the header as shown below:

SSIS Logging- sysssislog table not getting populated

In SSIS, if logging is enabled with provider type as SQL Server,

sysssislog table :
Contains one row for each logging entry that is generated by packages or their tasks and containers at run time.

This table is created in the msdb database when you install Microsoft SQL Server Integration Services.

If you configure logging to log to a different SQL Server database, a sysssislog table with this format is created in the specified database.

In SSIS, if sysssislog table is not getting populated after package execution, then make sure :

i)   Logging is enabled
ii)  OLEDB connection for Configuration - Pointing to correct database
iii) You have sufficient permission : 



SSIS - Merge Transformation

As stated on MSDN BOL :

The Merge transformation combines two sorted datasets into a single dataset .

By including the Merge transformation in a data flow, you can perform the following tasks:
  • Merge data from two data sources, such as tables and files.
  • Create complex datasets by nesting Merge transformations.
  • Remerge rows after correcting errors in the data.

In this article , let us see an example for Merge Transformation .

SSIS - WMI Event Watcher Task

As Stated here , we can use the WMI Event Watcher task for the following purposes:

  • Wait for notification that files have been added to a folder and then initiate the processing of the file.
  • Run a package that deletes files when the available memory on a server drops lower than a specified percentage.
  • Watch for installation of an application, and then run a package that uses the application.

SQL Server - Different ways to shred XML into table

 SQL Server - Different ways to shred XML into table :

1.) Using OPENXML or nodes()

2.) SSIS - XML Source task

3.) Powershell  

4.) Using VB/C# script (Script task in SSIS)

5.) Using SQL XML Bulkload API

 Reference links :

SQL Server XML Bulk load examples

SQL Server - Import/Shred XML from XML file placed in a folder into table

SSIS - Shred data from XML file in folder into columns of a table

Best Way to Shred XML document and insert the result set into a table in SQL Server

SSIS Error code 0x80131537 / 2146233033

When I was trying to shred XML into table using SSIS - Data Flow task with XML Source task and OLE DB destination task , I ended up with error code 0x80131537 / 2146233033 because input XML data fails the XSD validation (data type mismatch) .

I searched for the error description on MSDN site - Integration Services Error and Message Reference , but i did not find the error description for this particular error code .
So I started a discussion on MSDN SSIS forumn , refer this thread for explanation on error code 0x80131537 / 2146233033 .

SSIS - Extract filename from file path using TOKEN and TOKENCOUNT

TOKEN and TOKENCOUNT are new string functions introduced from SSIS 2012 .

As mentioned in MSDN :

TOKEN - Returns a token (substring) from a string based on the specified delimiters that separate tokens in the string and the number of the token that denotes which token to be returned. 

Syntax : 
TOKEN(character_expression, delimiter_string, occurrence)
TOKENCOUNT - Returns the number of tokens in a string that contains tokens 
separated by the specified delimiters. 
Syntax : 
TOKENCOUNT(character_expression, delimiter_string)

Example : (To  extract filename from file path using TOKEN and TOKENCOUNT )

Below expression returns 3 , because delimiter_string "\\" splits the character_expression 
"G:\\GAMES\\fifa.exe" into three parts .

TOKENCOUNT("G:\\GAMES\\fifa.exe", "\\")

Below expression returns fifa.exe , because  delimiter_string "\\" splits the character_expression "G:\\GAMES\\fifa.exe" into three parts, Occurrence is specified as 3, so the third token in the string is returned.

TOKEN("G:\\GAMES\\fifa.exe", "\\",3)

 To  extract filename from file path using TOKEN and TOKENCOUNT 

TOKEN("G:\\GAMES\\fifa.exe", "\\", TOKENCOUNT("G:\\GAMES\\fifa.exe", "\\"))


For more info & examples :

Token - 
TokenCount -

SSIS - Shred data from XML file in folder into columns of a table

This article is about shredding data from XML file in a folder into columns of a table using SQL Server Integration services (SSIS) .

Example :

Step 1 : Drag-drop Data Flow Task on to the Control Flow tab

Step 2 : Double-click on Data Flow Task , We will be directed to the Control Flow tab

Step 3 : In the Control Flow tab  , drag - drop
XML Source , Data Conversion and OLE DB Destination tasks .

Consider I have XML file  named Stud.xml in the path D:\


Step 4 : Double-click XML Source task and do the settings as shown in below image :

SSIS - Execute SQL Task - Result Set and Parameter Mapping

In this post,let us see an example for capturing query result (single valued)  into SSIS variable and insert the captured data in the variable into another table.

First create a variable in the scope of package with Data Type as INT32.

Drag & drop two Execute SQL Task on the Control Flow tab.

Follow as shown in below screen shots :

Executing Powershell commands using SSIS

In this post ,we are going to see two about two cases

i)saving powershell commands inside a file with file extension as ps1 and then invoking that file to execute the powershell commands

ii)executing powershell commands using SSIS - Execute Process Task

As a first step, i am going to open a notepad and place the below powershell commands inside it and then save it as .ps1 under path location "C:\Scripts\test.ps1"

$UserInput = Read-Host "Is Powershell really a cool feature"
##moving file to archieve folder
Move-Item D:\myflatfile.txt D:\archieve

And in my Execute Process Task i have entered only Executable & Arguments

And when you execute the package

Reference -

SQL Server Integration Services - ForceExecutionResult

By right-clicking on any tasks in SSIS,We can find under the property - ForceExecutionResult

In the below example ,I am using Execute SQL Task and creating a table.

I have set the ForceExecutionResult to Failure,which actually shows the task as failed but the table will be created in the database.


A value that specifies the forced result of running the package or container. The values are None, Success, Failure, and Completion. The default value for this property is None.

Reference -

SSIS - Capture Filenames while looping through multiple files inside folder

In this post,I am going to show an example on how to capture filenames while looping through multiple files inside folder.

In this example,I am going to capture filenames of files located in a folder.
This will be useful to keep a track on list of files already got processed.

Step 1: ForEachLoop Container with DataFlow Task

   Create variable inside ForEach Loop Container to hold filenames while looping through files inside folder.   



                         Returns the Unicode code point of the leftmost character of a character expression.

Syntax :

  • character_expression must have the DT_WSTR data type.
  • CODEPOINT returns a null result if character_expression is null or an empty string.


Sample data:

INSERT TEST123 (Column1) SELECT 'A12345678'
INSERT TEST123 (Column1) SELECT 'B12345678'
INSERT TEST123 (Column1) SELECT '123456789'


In the above sample data,if we are required to export only the rows with starting character as alphabet in the Column -  Column1.

If we are required to achieve the above scenario using SSIS ,  CODEPOINT function can be used .

 Use this expression in the Conditional Split Task as shown in below images :

     CODEPOINT(UPPER(Column1)) >= 65 && 
   CODEPOINT(UPPER(Column1)) <= 90