SQL SERVER – A Stored Procedure for Executing SSIS Packages in the SSIS Catalog – Notes from the Field #092

[Note from Pinal]: This is a new episode of Notes from the Field series. SQL Server Integration Service (SSIS) is one of the most key essential part of the entire Business Intelligence (BI) story. It is a platform for data integration and workflow applications.

andyleonard SQL SERVER   A Stored Procedure for Executing SSIS Packages in the SSIS Catalog   Notes from the Field #092

In this episode of the Notes from the Field series I asked SSIS Expert Andy Leonard a very crucial question – How to create a stored procedure for executing SSIS Package in the SSIS Catalog? Andy was very kind to answer the questions and provides plenty of information about how a novice developer can learn SSIS from the beginning and become expert in the technology.


The following is a snippet from Chapter 2 of the book SSIS Design Patterns co-written by Matt Masson (Blog | @mattmasson), Tim Mitchell (Blog | @Tim_Mitchell), Jessica Moss (Blog | @jessicammoss), and Michelle Ufford (Blog | @sqlfool). Earlier in the chapter there are demos that describe a simple SSIS package named Chapter2.dtsx which is part of an SSIS project named Chapter2, and which is deployed to an instance of the SSIS Catalog in a Folder named “Chapter 2”. But you can use this stored procedure to execute any SSIS package in the SSIS Catalog. That’s the whole point!

SQL Server 2014 provides a new way to manage and execute Integration Services packages: Integration Server Catalogs. We explore this method next.

Integration Server Catalogs

You can only manage SSIS projects that use the Project Deployment Model in Integration Services Catalogs. To execute a package in the catalog, use SSMS to connect to the instance of SQL Server hosting the SSISDB database. Expand the Integration Services Catalogs node, and then expand the SSISDB node. Drill into the folder containing the SSIS project and package(s). Right-click the package you wish to execute and click Execute, as shown in Figure 2-1.

notes91 1 SQL SERVER   A Stored Procedure for Executing SSIS Packages in the SSIS Catalog   Notes from the Field #092

Figure 2-1. Executing an SSIS Package deployed to the SSIS Catalog

The Execute Package Window displays, as shown in Figure 2-2. It allows you to override Parameter values, ConnectionString properties of Connection Managers built at design-time, or any other externalize-able property accessible from a Package Path (via the Advanced tab) for this execution instance of the SSIS package stored in the SSIS Catalog.

notes91 2 SQL SERVER   A Stored Procedure for Executing SSIS Packages in the SSIS Catalog   Notes from the Field #092

Figure 2-2. Execute Package Window

Integration Server Catalog Stored Procedures

Please note the Script button above the Parameters tab in Figure 2-2. This button allows you to generate Transact-SQL statements that will execute the SSIS package. For the Chapter2.dtsx package stored in the SSIS Catalog, the scripts will appear similar to that in Listing 2-1.

Listing 2-1. Transact-SQL Script Generated From the Execute Package Window

Declare @execution_id bigint
EXEC [SSISDB].[catalog].[create_execution]
   @package_name=N'Chapter2.dtsx'
  ,@execution_id=@execution_id OUTPUT
  ,@folder_name=N'Chapter2'
  ,@project_name=N'Chapter2'
  ,@use32bitruntime=False
  ,@reference_id=Null
Select @execution_id
DECLARE @var0 smallint = 1
EXEC [SSISDB].[catalog].[set_execution_parameter_value]
   @execution_id
  ,@object_type=50
  ,@parameter_name=N'LOGGING_LEVEL'
  ,@parameter_value=@var0
EXEC [SSISDB].[catalog].[start_execution] @execution_id
GO

You can use these same stored procedures to execute SSIS Packages in the SSIS Catalog! In fact, I designed a script to create a wrapper stored procedure that will call the Transact-SQL statements executed when an SSIS Package is executed in the SSIS Catalog. You can see that script in Listing 2-2.

Listing 2-2. Script to Build a Wrapper Stored Procedure for Executing SSIS Packages in the SSIS Catalog

 /* Select the SSISDB database */
Use SSISDB
Go

 /* Create a parameter (variable) named @Sql */
Declare @Sql varchar(2000)

 /* Create the Custom schema if it does not already exist */
print 'Custom Schema'
If Not Exists(Select name 
              From sys.schemas 
                Where name = 'custom')
 begin
   /* Create Schema statements must occur first in a batch */
  print ' - Creating custom schema'
  Set @Sql = 'Create Schema custom'
  Exec(@Sql)
  print ' - Custom schema created'
 end
Else
 print ' - Custom Schema already exists.'
print ''

 /* Drop the Custom.execute_catalog_package Stored Procedure if it already exists */
print 'Custom.execute_catalog_package Stored Procedure'
  If Exists(Select s.name + '.' +  p.name
            From sys.procedures p
            Join sys.schemas s
                On s.schema_id = p.schema_id
         Where s.name = 'custom'
           And p.name = 'execute_catalog_package')
   begin
    print ' - Dropping custom.execute_catalog_package'
    Drop Procedure custom.execute_catalog_package
    print ' - Custom.execute_catalog_package dropped'
   end

   /* Create the Custom.execute_catalog_package Stored Procedure */
  print ' - Creating custom.execute_catalog_package'
go

/*

     Stored Procedure: custom.execute_catalog_package
     Author: Andy Leonard
     Date: 4 Mar 2012
     Description: Creates a wrapper around the SSISDB Catalog procedures
                  used to start executing an SSIS Package. Packages in the
                SSIS Catalog are referenced by a multi-part identifier
                 - or path - that consists of the following hierarchy:
        Catalog Name: Implied by the database name in Integration Server 2014
        |-Folder Name: A folder created before or at Deployment to contain the SSIS project
        |-Project Name: The name of the SSIS Project deployed
        |-Package Name: The name(s) of the SSIS Package(s) deployed

        Parameters:
        @FolderName [nvarchar(128)] {No default} – 
         contains the name of the Folder that holds the SSIS Project
        @ProjectName [nvarchar(128)] {No default} – 
         contains the name of the SSIS Project that holds the SSIS Package
        @PackageName [nvarchar(260)] {No default} – 
         contains the name of the SSIS Package to be executed
        @ExecutionID [bigint] {Output} – 
         Output parameter (variable) passed back to the caller
        @LoggingLevel [varchar(16)] {Default} – 
         contains the (case-insensitive) name of the logging level
         to apply to this execution instance
        @Use32BitRunTime [bit] {Default} – 
         1 == Use 64-bit run-time
                                                      0 == Use 32-bit run-time
        @ReferenceID [bigint] {Default} –          contains a reference to an Execution Environment
        @ObjectType [smallint] –          contains an identifier that appears to be related to the          SSIS PackageType property 
        Guessing: @ObjectType == PackageType.ordinal (1-based-array) * 10
         Must be 20, 30, or 50 for catalog.set_execution_parameter_value
         stored procedure

        Test: 
        1. Create and deploy an SSIS Package to the SSIS Catalog.
        2. Exec custom.execute_catalog_package and pass it the 
          following parameters: @FolderName, @ProjectName, @PackageName, @ExecutionID Output 
        @LoggingLevel, @Use32BitRunTime, @ReferenceID, and @ObjectType are optional and 
        defaulted parameters.

         Example:
           Declare @ExecId bigint
           Exec custom.execute_catalog_package
         'Chapter2'
        ,'Chapter2'
        ,'Chapter2.dtsx'
        ,@ExecId Output
        3. When execution completes, an Execution_Id value should be returned.
        View the SSIS Catalog Reports to determine the status of the execution 
        instance and the test.

*/
Create Procedure custom.execute_catalog_package
  @FolderName nvarchar(128)
 ,@ProjectName nvarchar(128)
 ,@PackageName nvarchar(260)
 ,@ExecutionID bigint Output
 ,@LoggingLevel varchar(16) = 'Basic'
 ,@Use32BitRunTime bit = 0
 ,@ReferenceID bigint = NULL
 ,@ObjectType smallint = 50
As

 begin
  
  Set NoCount ON
  
   /* Call the catalog.create_execution stored procedure
      to initialize execution location and parameters */
  Exec catalog.create_execution
   @package_name = @PackageName
  ,@execution_id = @ExecutionID Output
  ,@folder_name = @FolderName
  ,@project_name = @ProjectName
  ,@use32bitruntime = @Use32BitRunTime
  ,@reference_id = @ReferenceID

   /* Populate the @ExecutionID parameter for OUTPUT */
  Select @ExecutionID As Execution_Id

   /* Create a parameter (variable) named @Sql */
  Declare @logging_level smallint
   /* Decode the Logging Level */
  Select @logging_level = Case 
                           When Upper(@LoggingLevel) = 'BASIC'
                           Then 1
                           When Upper(@LoggingLevel) = 'PERFORMANCE'
                           Then 2
                            When Upper(@LoggingLevel) = 'VERBOSE'
                           Then 3
                           Else 0 /* 'None' */
                          End 
   /* Call the catalog.set_execution_parameter_value stored
      procedure to update the LOGGING_LEVEL parameter */
  Exec catalog.set_execution_parameter_value
    @ExecutionID
   ,@object_type = @ObjectType
   ,@parameter_name = N'LOGGING_LEVEL'
   ,@parameter_value = @logging_level

   /* Call the catalog.start_execution (self-explanatory) */
  Exec catalog.start_execution
    @ExecutionID

 end

GO

If you execute this script to create the custom schema and stored procedure in your instance of the SSISDB database, you can test it using the statement in Listing 2-3.

Listing 2-3. Testing the SSISDB.custom.execute_catalog_package Stored Procedure

Declare @ExecId bigint
Exec SSISDB.custom.execute_catalog_package 'Chapter2','Chapter2','Chapter2.dtsx',
@ExecId Output

Conclusion

This custom.execute_catalog_package stored procedure can be used to execute an SSIS package from any folder and project in the SSIS Catalog.

The SSIS Script Task can accomplish much more than generating log messages. This brief introduction and basic example have demonstrated how to get started configuring and using the SSIS Script Task. As you can see, SSIS Script Tasks give you development capabilities you may not have been aware of.

If you want to get started with SSIS with the help of experts, read more over at Fix Your SQL Server.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Using Project Connections in SSIS – Notes from the Field #088

[Notes from Pinal]: SSIS is very well explored subject, however, there are so many interesting elements when we read, we learn something new. A similar concept has been Using Project Connections in SSIS.

Tim Mitchell SQL SERVER   Using Project Connections in SSIS   Notes from the Field #088Linchpin People are database coaches and wellness experts for a data driven world. In this 88th episode of the Notes from the Fields series database expert Tim Mitchell (partner at Linchpin People) shares very interesting conversation related to how to use raw files in SSIS.


In SQL Server Integration Services, connection managers are used as gateways for most any external read and write operation. Connection managers are type- and format-specific, and in the case of relational database connection managers, they are usually specific to vendor (Oracle, DB2, etc.) as well.

In most use cases, the same connection will be used across multiple packages in the same project. In pre-2012 versions of SSIS, each package would have its own connection manager for every connection used in that package. Creating and maintaining all those connection managers could be time-consuming as the number of packages grows. In SQL Server 2012, Microsoft added project connections to SSIS, allowing for the creation of connections that were accessible across all packages in a project. Instead of having to create a copy of each connection manager in every package, developers can now simply create the connection at the project level. Project connections will automatically show up in the connection manager tray for all packages in that project.

n 88 1 SQL SERVER   Using Project Connections in SSIS   Notes from the Field #088

As shown, any project connection automatically has the designation (project) prepended to the name to clearly indicate that it is a project connection. Those without this designation are package connections, and are only accessible from within that package.

Project connections will also appear in the Solution Explorer window, under the Connection Managers tab.

n 88 2 SQL SERVER   Using Project Connections in SSIS   Notes from the Field #088

You can create a new project connection by right-clicking on the Connection Managers node shown above, and walking through the steps to build the connection. Similarly, you can edit or delete an existing project-level connection manager from this same window.

You can also promote an existing package connection to a project connection by right-clicking on the package connection and selecting Convert to Project Connection.

n 88 3 SQL SERVER   Using Project Connections in SSIS   Notes from the Field #088

Coincidentally, you can also convert back to a package connection through a similar process. Right-clicking on a project connection will expose an option to Convert to Package Connection. However, you have to be careful when choosing this option. If you convert a project connection to a package connection, that connection will then be visible only in the package in which you are currently working. If you have used the connection in any other packages in that project, those operations will fail because the connection is no longer visible at the project level. You will get a warning message when you attempt to convert a project connection to a package connection.

n 88 4 SQL SERVER   Using Project Connections in SSIS   Notes from the Field #088

Finally, if you are using project connections, you can still use dynamic properties such as expressions and parameters. Do be aware that, if you use parameters to configure a project connection, you must use project parameters rather than package parameters. The latter is not accessible beyond the scope of a single package, and therefore would not always be accessible for project connections. Fortunately, the UI for the expression builder limits you to only project parameters when configuring project connections.

n 88 5 SQL SERVER   Using Project Connections in SSIS   Notes from the Field #088

In conclusion, the project connection in SSIS is an excellent tool for configuring connections at the project level to minimize the extra work required for sharing connections across multiple packages in a project.

If you want me to take a look at your server and its settings, or if your server is facing any issue we can Fix Your SQL Server.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – The Basics of the Execute Process Task – Notes from the Field #084

[Note from Pinal]: This is a new episode of Notes from the Field series. Every time I give an introductory note, however, this time there is no need of intro note. This note is from Andy and as we all know he is amazing person when we have to understand the fundamentals. He has written this blog post with such an interesting way that you must read it to understand the very basic of the file system task.

andyleonard SQL SERVER   The Basics of the Execute Process Task   Notes from the Field #084


Many data integration scenarios involve executing some other process, whether starting a custom application or performing an operating system operation.

Remember: SSIS is a software development platform. With “SQL Server” included in the name, it is easy for people to confuse SSIS as a database tool or accessory, but Control Flow Tasks put that confusion to rest.

SSIS provides several Control Flow tasks. Here is a list that provides a good approximation of which tasks I use most, from most-used to least-used:

In this article I provide an example of configuring the SSIS Execute Process Task, shown in Figure 1:

notes 84 1 SQL SERVER   The Basics of the Execute Process Task   Notes from the Field #084
Figure 1: SSIS Execute Process Task

As with the File System Task, the Execute Process Task provides yet another way to implement an SSIS Design Pattern for source file archival via file compression. When you first open the Execute Process Task Editor, you will note several properties in the property grid, as shown in Figure 2:

notes 84 2 SQL SERVER   The Basics of the Execute Process Task   Notes from the Field #084
Figure 2: SSIS Execute Process Task Properties

An important property is the Executable property which holds the path to the application or process you wish to start with the Execute Process Task. In this case, I am going to start the 7-Zip command line executable to zip a data file. 7-zip is a free file compression utility, and the command line utility is pretty cool. On my system, the 7-Zip command line utility is located at “C:\Program Files\7-Zip\7z.exe” so I configure the Executable property of the Execute Process Task as shown in Figure 3:

notes 84 3 SQL SERVER   The Basics of the Execute Process Task   Notes from the Field #084
Figure 3: The Execute Process Task Editor with the Executable Property Configured

The Arguments property allows me to specify command line arguments to the executable. For 7-Zip, the “a” argument informs the application that I want to add files to a compressed file. The text following the “a” argument specifies the name of the compressed file. The argument that follows the name of the compressed file configures the file (or files) to add. My arguments property reads:

a E:\Projects\7Zip\data\archive1.7z E:\Projects\7Zip\data\test.csv

These arguments tell the 7z.exe executable that I want to add the E:\Projects\7Zip\data\test.csv file to a compressed file named E:\Projects\7Zip\data\archive1.7z, as shown in Figure 4:

notes 84 4 SQL SERVER   The Basics of the Execute Process Task   Notes from the Field #084
Figure 4: The Execute Process Task Editor with the Arguments Property Configured

I can configure other Execute Process Task properties. For example, I choose to hide the command line window for 7-Zip when it executes. To do so, I set the WindowStyle property to Hidden, as shown in Figure 5:

notes 84 5 SQL SERVER   The Basics of the Execute Process Task   Notes from the Field #084
Figure 5: The Execute Process Task Editor’s WindowStyle Property

The SSIS Execute Process Task is now configured to compress a file. Let’s test it! Click the OK button to close the Execute Process Task Editor. Press the F5 key or select SSIS->Start Debugging to test your work. My result is shown in Figure 6:

notes 84 6 SQL SERVER   The Basics of the Execute Process Task   Notes from the Field #084
Figure 6: Successful Test Execution of the SSIS Execute Process Task

Viewing the source and destination directories, we see the file was successfully moved – shown in Figure 7:

notes 84 7 SQL SERVER   The Basics of the Execute Process Task   Notes from the Field #084
Figure 7: The File, Compressed!

As I stated earlier, the SSIS Execute Process Task is powerful, flexible, and robust. This article has demonstrated another way you can use the Execute Process Task to compress files for archiving. Archiving files after loading the data they contain is a common practice in data integration.

If you want to get started with SSIS with the help of experts, read more over at Fix Your SQL Server.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – SSIS and How to Load Binary Large Objects, or Blobs – Notes from the Field #080

[Note from Pinal]: This is a new episode of Notes from the Field series. Every time I give an introductory note, however, this time there is no need of intro note. This note is from Andy and as we all know he is amazing person when we have to understand the fundamentals. He has written this blog post with such an interesting way that you must read it to understand the very basic fundamental of SSIS.

andyleonard SQL SERVER   SSIS and How to Load Binary Large Objects, or Blobs   Notes from the Field #080


I still remember my first experience using SSIS to load binary large objects, or blobs. I was doing a gig for a large retailer, moving data for a new in-store application. Part of the data was product images, mostly 3kb-5kb files. During testing, I noticed the load ran much faster if I did not load the images. I wondered why, so I searched the interwebz for an answer.

I did not find an answer. I found lots of comments and posts telling me I could not, in fact, use SSIS (2005) to integrate blob data. At least not the way I was doing it. My response was, “Huh,” as I was already using SSIS to (apparently) do the impossible. I knew right away this represented an opportunity. I learned everything I could about blobs and now, just nine short years later, I’m here to sharea.

When I searched the interwebz this time, I found an excellent blog post by my friend, John Welch (blog | @john_welch), titled Importing Files Using SSIS. John’s idea is straightforward and very “SSIS-y,” as Kevin Hazzard (blog | @KevinHazzard) says. With John’s permission, I am modeling the example for this post on John’s post.

How Does SQL Server Store Blobs?

Before we dive into a demo project, it’s important to know more about how SQL Server stores blob data. The short answer is: it depends. I can hear you asking, “What does it depend on, Andy?” It depends on the size of the blob data. For larger binary large objects, a pointer to a file location is stored in the row. When the row is read, the pointer points to the file location containing the binary data, and the binary data is streamed to the output.

In this example, we’ll take a look at how we use SSIS to move data from the file system into a SQL Server table. I changed a few things but, again, this example was inspired by John Welch’s post titled Importing Files Using SSIS.

Part 1 – Import Column

The Import Column transformation streams file binaries – the contents of a file – into a binary large object (Blob) “column” in a Data Flow Path. From the Data Flow path, these data can be streamed into a database table Blob field. Let’s demonstrate:

In a default instance of SQL Server 2014, I created a database named ImportPics. Then I created a table named PicFile using this statement:

CREATE TABLE PicFile
(
ID INT IDENTITY(1,1)
,
FilePath VARCHAR(255)
,
FileContent IMAGE
)

Next I created an SSIS 2014 project named ImportPicFiles and renamed Package.dtsx to ImportPicFiles.dtsx. I added a Data Flow Task and created a package parameter named ImportFilesDir to hold the path to a directory filled with Snagit screenshots:

notes 70 1 SQL SERVER   SSIS and How to Load Binary Large Objects, or Blobs   Notes from the Field #080

I add a script component as a Source adapter, then configure it to consume the $Package::ImportFilesDir package parameter as a ReadOnlyVariable:

notes 70 2 SQL SERVER   SSIS and How to Load Binary Large Objects, or Blobs   Notes from the Field #080

I add an output column named filename (DT_STR, 255):

notes 70 3 SQL SERVER   SSIS and How to Load Binary Large Objects, or Blobs   Notes from the Field #080

In the Script Editor, I add the following code:

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using System.IO;
[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
public override void CreateNewOutputRows()
{
DirectoryInfo dir = new DirectoryInfo(Variables.ImportFilesDir.ToString());
foreach (var file in dir.GetFiles())
{
Output0Buffer.AddRow();
Output0Buffer.fileName = file.FullName;
}
Output0Buffer.SetEndOfRowset();
}
}

(Yes, that’s C#. I’m learning new things. Yay me! :{>)

Next, I add and configure an Import Columns transformation. On the Input Columns page, I select the fileName field:
notes 70 4 SQL SERVER   SSIS and How to Load Binary Large Objects, or Blobs   Notes from the Field #080

On the Input and Output Properties tab, I expand the Import Column Output node of the treeview, select Output Columns, and click the Add Column button. I name the column “FileContents” and set the DataType property to DT_IMAGE:

notes 70 5 SQL SERVER   SSIS and How to Load Binary Large Objects, or Blobs   Notes from the Field #080

This next part is a little tricky. You can learn more about configuring this – and other tricky SSIS transformation properties – here.

Select the LineageID property value for the FileContents column and copy it to the clipboard:
notes 70 6 SQL SERVER   SSIS and How to Load Binary Large Objects, or Blobs   Notes from the Field #080

Next, expand the Import Column Input treeview node, then expand Input Columns, and then select the fileName column. In the FileDataColumnID property, paste the value you just copied to the clipboard:

notes 70 7 SQL SERVER   SSIS and How to Load Binary Large Objects, or Blobs   Notes from the Field #080

Add an OLE DB Destination adapter, connect it to the database where you created the dbo.PicFile table earlier, and configure the OLE DB Destination adapter to load dbo.PicFile:

notes 70 8 SQL SERVER   SSIS and How to Load Binary Large Objects, or Blobs   Notes from the Field #080

A successful test execution of the data flow task will appear as shown:

notes 70 9 SQL SERVER   SSIS and How to Load Binary Large Objects, or Blobs   Notes from the Field #080

The Import Column transformation is a powerful to load files into database blob columns.

Read SSIS and Blobs, Part 2 to learn even more.

If you want to get started with SSIS with the help of experts, read more over at Fix Your SQL Server.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Using Package Configurations in SSIS 2012 and Beyond – Notes from the Field #079

[Notes from Pinal]: I know quite a lot of things about SSIS but every single time when I read notes from the field, I realize that there are so many small but very important features exist. A similar concept has been Using Package Configurations in SSIS 2012 and Beyond. Packages are the most critical part of the SSIS and configuring it correctly is extremely important.

Tim Mitchell SQL SERVER   Using Package Configurations in SSIS 2012 and Beyond   Notes from the Field #079Linchpin People are database coaches and wellness experts for a data driven world. In this 79th episode of the Notes from the Fields series database expert Tim Mitchell (partner at Linchpin People) shares very interesting conversation related to using package configurations in SSIS 2012 and beyond.


If you are considering upgrading from an older version of SSIS to version 2012 or 2014 but are worried that you’ll lose the ability to use those package configurations you spent so much time developing, there is good news. Although it is not a heavily advertised feature in later versions, the classic package configuration option is still alive and well in SSIS 2012 and 2014.

The Configuration Design Pattern

Storing runtime configuration data outside of SSIS packages is a critical feature of a mature ETL process. Building a design pattern that externalizes values such as database connection strings, file paths, and other data that may change over time can reduce the amount of maintenance effort required later when those values need to be updated.

In versions of SSIS before 2012, the most common way to externalize connection strings and other runtime values was to use one or more SSIS package configurations. Although package configurations could be a little clunky at times, they provided a very effective means through which the ETL developer could avoid hard-coding variable data in packages.

This configuration pattern evolved significantly in 2012.  For new development in SSIS 2012 and later, the typical setup now involves using the SSIS catalog (which was first released with version 2012) to store and execute packages. Similarly, those designs usually include the use of package parameters and SSIS environments to supply runtime values for said parameters. As a result, the package configuration option is no longer the preferred method for variable externalization in new package development.

However, there are many organizations with significant investments in the old-style package configurations. One of the more common questions I’m asked about upgrading SSIS is whether package configurations can still be used in newer versions of SSIS. I’m happy to report that package configurations are still around (albeit a bit harder to find) and are just as usable in later versions of SSIS as they were in prior versions.

Configuring Package Parameters in SSIS 2012 and Later

In SSIS 2005 and 2008, you could access package configurations by simply right-clicking on an empty space in the package and selecting Package Configurations similar to what is shown below.

 notf 79 1 SQL SERVER   Using Package Configurations in SSIS 2012 and Beyond   Notes from the Field #079

However, if you’re using SSIS 2012 or 2014 in project deployment mode (the default setting for new projects), this option no longer exists.

 notf 79 2 SQL SERVER   Using Package Configurations in SSIS 2012 and Beyond   Notes from the Field #079

Even though the option no longer appears in this shortcut menu, it can still be accessed directly by using the package properties. In the package properties window, there is a collection called Configurations that will allow you to set one or more package configurations.

notf 79 3 SQL SERVER   Using Package Configurations in SSIS 2012 and Beyond   Notes from the Field #079

Clicking on the ellipsis next to this collection brings up the familiar package configurations menu, in which you can create XML, environment variable, or table storage configurations.

notf 79 4 SQL SERVER   Using Package Configurations in SSIS 2012 and Beyond   Notes from the Field #079

There are a couple of things to keep in mind on using package configurations in SSIS 2012 and beyond. First of all, you can use package configurations in addition to newer configuration methods (including package parameterization and SSIS environments). However, my recommendation is that you choose just one configuration method per project to avoid confusion or conflicting values. Also, be aware that the way package configuration values are logged differs from the way package parameter and SSIS environment values are logged in the SSIS catalog. If you do use the classic package configuration design pattern, be sure to review your execution logs to confirm that you’re getting all of the information you need to test, troubleshoot, and audit your package executions.

Conclusion

The old-style SSIS package configurations have largely yielded to the newer and more popular package parameters and SSIS environments. However, package configurations are still around and are fully accessible in later versions of the product.

If you want me to take a look at your server and its settings, or if your server is facing any issue we can Fix Your SQL Server.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – The Basics of the File System Task – Part 2 – Notes from the Field #075

[Note from Pinal]: This is a new episode of Notes from the Field series. SQL Server Integration Service (SSIS) is one of the most key essential part of the entire Business Intelligence (BI) story. It is a platform for data integration and workflow applications.

andyleonard SQL SERVER   The Basics of the File System Task   Part 2   Notes from the Field #075

In this episode of the Notes from the Field series I asked SSIS Expert Andy Leonard a very crucial question – What are the Basics of the File System Task and where do we start with it? Andy was very kind to answer the questions and provides plenty of information about how a novice developer can learn SSIS from the beginning and become expert in the technology.


Many data integration scenarios involve reading data stored in flat files or performing extracts from a relational (or legacy) system into flat files. Learning how to configure and use the SQL Server Integration Services (SSIS) File System Task will support your efforts when loading data to and from flat files. In a previous article, I described configuring the File System Task to archive a file. In this article, I will repeat the exercise, but I will add flexibility (and complexity – the two always go together) by using SSIS Variables to manage the Source File and Destination Directory locations. This article is an edited version of The Basics of the File System Task, Part 1. I chose to write it this way for those who find this article but haven’t read Part 1.

Remember: SSIS is a software development platform. With “SQL Server” included in the name, it is easy for people to confuse SSIS as a database tool or accessory, but Control Flow Tasks put that confusion to rest.

SSIS provides several Control Flow tasks. Here is a list that provides a good approximation of which tasks I use most, from most-used to least-used:

In this article I provide an advanced example of configuring the SSIS File System Task, shown in Figure 1:

notes 75 1 SQL SERVER   The Basics of the File System Task   Part 2   Notes from the Field #075
Figure 1: SSIS File System Task

The File System Task provides one way to implement an SSIS Design Pattern for source file archival. When you first open the File System Task Editor, you will note several properties in the property grid. Whenever you see an Operation property in an SSIS task editor, know that that property drives the other property selections. Options for the Operation property of the SSIS File System Task are shown in Figure 2:
notes 75 2 SQL SERVER   The Basics of the File System Task   Part 2   Notes from the Field #075
Figure 2: SSIS File System Task Operation Property Options

The Operation options are:

  • Copy directory
  • Copy file (default)
  • Create directory
  • Delete directory
  • Delete directory content
  • Delete file
  • Move directory
  • Move file
  • Rename file
  • Set Attributes

I stated the Operation property drives the other property selections. Take a look at the File System Task Editor when I change the Operation option from “Copy file” (Figure 2) to “Delete file” as shown in Figure 3:
notes 75 3 SQL SERVER   The Basics of the File System Task   Part 2   Notes from the Field #075
Figure 3: The File System Task Editor with the “Delete file” Operation Selected

See? There are less properties required for the “Delete file” operation. The available properties are even more different for the “Set Attributes” operation, shown in Figure 4:
notes 75 4 SQL SERVER   The Basics of the File System Task   Part 2   Notes from the Field #075
Figure 4: The File System Task Editor with the “Set Attributes” Operation Selected

The Operation property changes the editable properties, exposing some and hiding others. With flexibility come complexity. Even though the File System Task is complex, I’ve found the task is stable and extremely useful. Let’s look at a practical example; using the File System Task to archive a flat file.

To begin configuring the SSIS File System Task for file archival, select the “Move file” operation as shown in Figure 5:
notes 75 5 SQL SERVER   The Basics of the File System Task   Part 2   Notes from the Field #075
Figure 5: SSIS File System Task with the “Move file” Operation Selected

Using the IsSourcePathVariable and IsDestinationPathVariable properties extends the flexibility of the File System Task and further changes the list of available properties in the property grid, as shown in Figure 6:
notes 75 6 SQL SERVER   The Basics of the File System Task   Part 2   Notes from the Field #075
Figure 6: Opting to Use Variables for Source and Destination Paths

Note the SourceConnection and DestinationConnection properties are hidden and the SourceVariable and DestinationVariable properties are available in their place. Click the SourceVariable property dropdown, and click “<New variable…>” as shown in Figure 7:
notes 75 7 SQL SERVER   The Basics of the File System Task   Part 2   Notes from the Field #075
Figure 7: Selecting “<New variable…>” from the SourceVariable Property

When the Add Variable window displays, enter “SourceFilePath” for the variable name property and a full path to your source file in the Value textbox, as shown in Figure 8:
notes 75 8 SQL SERVER   The Basics of the File System Task   Part 2   Notes from the Field #075
Figure 8: Configuring the SourceFilePath SSIS Variable

Click the OK button to close the Add Variable window and return to the File System Task Editor. Click the DestinationVariable property dropdown, and then click “<New variable…>” to open a new Add Variable window. Configure the new variable by setting the Name property to “DestinationFolder” and the Value property to a location you wish to move the file, as shown in Figure 9:
notes 75 9 SQL SERVER   The Basics of the File System Task   Part 2   Notes from the Field #075
Figure 9: Configuring the DestinationFolder SSIS Variable

Click the OK button to close the Add Variable window and return to the File System Task Editor. You have configured an SSIS File System Task to move a file using SSIS Variables to manage the source and destination of the file, as shown in Figure 10:
notes 75 10 SQL SERVER   The Basics of the File System Task   Part 2   Notes from the Field #075
Figure 10: An SSIS File System Task Configured to Move a File Using SSIS Variables

The SSIS File System Task is now configured to archive a file. Let’s test it! Click the OK button to close the File System Task Editor. Press the F5 key or select SSIS->Start Debugging to test your work. My result is shown in Figure 11:
notes 75 11 SQL SERVER   The Basics of the File System Task   Part 2   Notes from the Field #075
Figure 11: Successful Test Execution of the SSIS File System Task

Viewing the source and destination directories, we see the file was successfully moved – shown in Figure 12:
notes 75 12 SQL SERVER   The Basics of the File System Task   Part 2   Notes from the Field #075
Figure 12: The File, Moved!

One tricky part when configuring the SSIS File System Task to move a file is realizing that you need to select the actual file for the source and the directory for the destination.

As I stated earlier, the SSIS File System Task is powerful, flexible, and robust. This article has demonstrated another way you can use the File System Task to archive files. Archiving files after loading the data they contain is a common practice in data integration.

If you want to get started with SSIS with the help of experts, read more over at Fix Your SQL Server.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – The Basics of the File System Task, Part 1 – Notes from the Field #071

[Note from Pinal]: This is a new episode of Notes from the Field series. Every time I give an introductory note, however, this time there is no need of intro note. This note is from Andy and as we all know he is amazing person when we have to understand the fundamentals. He has written this blog post with such an interesting way that you must read it to understand the very basic of the file system task.

andyleonard SQL SERVER   The Basics of the File System Task, Part 1   Notes from the Field #071


Many data integration scenarios involve reading data stored in flat files or performing extracts from a relational (or legacy) system into flat files. Learning how to configure and use the SQL Server Integration Services (SSIS) File System Task will support your efforts when loading data to and from flat files. Remember: SSIS is a software development platform. With “SQL Server” included in the name, it is easy for people to confuse SSIS as a database tool or accessory, but Control Flow Tasks put that confusion to rest.

SSIS provides several Control Flow tasks. Here is a list that provides a good approximation of which tasks I use most, from most-used to least-used:

In this article I provide a basic example of configuring the SSIS File System Task, shown in Figure 1:
image001 SQL SERVER   The Basics of the File System Task, Part 1   Notes from the Field #071
Figure 1: SSIS File System Task

The File System Task provides one way to implement an SSIS Design Pattern for source file archival. When you first open the File System Task Editor, you will note several properties in the property grid. Whenever you see an Operation property in an SSIS task editor, know that that property drives the other property selections. Options for the Operation property of the SSIS File System Task are shown in Figure 2:
image002 SQL SERVER   The Basics of the File System Task, Part 1   Notes from the Field #071
Figure 2: SSIS File System Task Operation Property Options

The Operation options are:

  • Copy directory
  • Copy file (default)
  • Create directory
  • Delete directory
  • Delete directory content
  • Delete file
  • Move directory
  • Move file
  • Rename file
  • Set Attributes

I stated the Operation property drives the other property selections. Take a look at the File System Task Editor when I change the Operation option from “Copy file” (Figure 2) to “Delete file” as shown in Figure 3:
image003 SQL SERVER   The Basics of the File System Task, Part 1   Notes from the Field #071
Figure 3: The File System Task Editor with the “Delete file” Operation Selected

See? There are less properties required for the “Delete file” operation. The available properties are even more different for the “Set Attributes” operation, shown in Figure 4:
image004 SQL SERVER   The Basics of the File System Task, Part 1   Notes from the Field #071
Figure 4: The File System Task Editor with the “Set Attributes” Operation Selected

The Operation property changes the editable properties, exposing some and hiding others. With flexibility come complexity. Even though the File System Task is complex, I’ve found the task is stable and extremely useful. Let’s look at a practical example; using the File System Task to archive a flat file.

To begin configuring the SSIS File System Task for file archival, select the “Move file” operation as shown in Figure 5:
image005 SQL SERVER   The Basics of the File System Task, Part 1   Notes from the Field #071
Figure 5: SSIS File System Task with the “Move file” Operation Selected

Using the IsSourcePathVariable and IsDestinationPathVariable properties extends the flexibility of the File System Task and further changes the list of available properties in the property grid, as shown in Figure 6:
image006 SQL SERVER   The Basics of the File System Task, Part 1   Notes from the Field #071
Figure 6: Opting to Use Variables for Source and Destination Paths

Note the SourceConnection and DestinationConnection properties are hidden and the SourceVariable and DestinationVariable properties are available in their place. For the sake of simplicity, we’re not going to use SSIS Variables to define the source and destination paths in this article. Reset the IsSourcePathVariable and IsDestinationPathVariable properties to False. Click the SourceConnection property dropdown, and click “<New connection…>” as shown in Figure 7:
image007 SQL SERVER   The Basics of the File System Task, Part 1   Notes from the Field #071
Figure 7: Selecting a New Connection for the SSIS File System Task SourceConnection Property

The File Connection Manager Editor displays with the “Usage type” property set to “Existing file” as shown in Figure 8:
image008 SQL SERVER   The Basics of the File System Task, Part 1   Notes from the Field #071
Figure 8: File Connection Manager Editor

Click the “Browse…” button and select a flat file you wish to move, as shown in Figure 9:
image009 SQL SERVER   The Basics of the File System Task, Part 1   Notes from the Field #071
Figure 9: Selecting the Source File

Once the File Connection Manager Editor is configured, it should appear similar to that shown in Figure 10:
image010 SQL SERVER   The Basics of the File System Task, Part 1   Notes from the Field #071
Figure 10: A Configured File Connection Manager Editor

Click the OK button to close the File Connection Manager Editor. Next, click the DestinationConnection property in the SSIS File System Task Editor. As with the SourceConnection property, click the dropdown and select “<New connection…>”. When the File Connection Manager Editor displays, select the “Existing folder” Usage type, as shown in Figure 11:
image011 SQL SERVER   The Basics of the File System Task, Part 1   Notes from the Field #071
Figure 11: Configuring the Destination File Connection Manager

Note the warning at the bottom of the File Connection Manager Editor window: “Folder name must be specified.” Click the “Browse…” button and navigate to the directory where you wish to archive the source file as shown in Figure 12:
image012 SQL SERVER   The Basics of the File System Task, Part 1   Notes from the Field #071
Figure 12: Configuring the Location of the DestinationConnection File Connection

Click the OK button in the “Browse For Folder” dialog to return to the File Connection Manager Editor, as shown in Figure 13:
image013 SQL SERVER   The Basics of the File System Task, Part 1   Notes from the Field #071
Figure 13: A File Connection Manager Configured for the DestinationConnection Property

Click the OK button to close the File Connection Manager Editor and return to the File System Task Editor, as shown in Figure 14:
image014 SQL SERVER   The Basics of the File System Task, Part 1   Notes from the Field #071
Figure 14: An SSIS File System Task Configured to Archive a File

The SSIS File System Task is now configured to archive a file. Let’s test it! Click the OK button to close the File System Task Editor. Press the F5 key or select SSIS->Start Debugging to test your work. My result is shown in Figure 15:
image015 SQL SERVER   The Basics of the File System Task, Part 1   Notes from the Field #071
Figure 15: Successful Test Execution of the SSIS File System Task

Viewing the source and destination directories, we see the file was successfully moved – shown in Figure 16:
image016 SQL SERVER   The Basics of the File System Task, Part 1   Notes from the Field #071
Figure 16: The File, Moved!

One tricky part when configuring the SSIS File System Task to move a file is realizing that you need to select the actual file for the source and the directory for the destination.

As I stated earlier, the SSIS File System Task is powerful, flexible, and robust. This article has demonstrated one way you can use the File System Task to archive files. Archiving files after loading the data they contain is a common practice in data integration.

If you want to get started with SSIS with the help of experts, read more over at Fix Your SQL Server.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Using Raw Files in SSIS – Notes from the Field #070

[Notes from Pinal]: SSIS is very well explored subject, however, there are so many interesting elements when we read, we learn something new. A similar concept has been Using Raw Files in SSIS.

Tim Mitchell SQL SERVER   Using Raw Files in SSIS   Notes from the Field #070Linchpin People are database coaches and wellness experts for a data driven world. In this 70th episode of the Notes from the Fields series database expert Tim Mitchell (partner at Linchpin People) shares very interesting conversation related to how to use raw files in SSIS.


SQL Server Integration Services is well designed for retrieving and processing data on the fly, directly in the data flow pipeline. However, there are circumstances that occasionally require the persistence of result sets in SSIS for use during package execution. For these such cases, one option is to use SSIS raw files.

The raw file in SSIS is a special kind of binary file, optimized for and intended for use only through SSIS. This special file is intended to store temporary result sets on disk to allow reuse of data across multiple packages and multiple executions.

Although these are not tools one would likely use on a daily basis, the raw file tools in SSIS are helpful in a number of scenarios:

  • The same result set is reused several times as part of the different data flow (or even different packages)
  • Due to size or query complexity, retrieving a particular set of reference data takes a significant amount of time
  • Due to business rules, one or more interim result set is required during the ETL process

In cases such as this, using raw files to store result sets can be a handy solution.

To create and use raw files, SSIS comes with two components – a raw file source and a raw file destination – to allow reading from and writing to these files. I’ll start by showing the destination (ordinarily I would start with the source, but since we need to create a raw file with the destination component before we can use the source component, I’m changing the typical demonstration order). As shown below, this example uses a data flow with an OleDB source – connected to AdventureWorks – for sending sales data into a raw file destination.

notes 70 1 SQL SERVER   Using Raw Files in SSIS   Notes from the Field #070

On the raw file destination, we’ve got a handful of configuration options, including:

  • The type of file name (either direct or from an SSIS variable)
  • A selector to choose the file name or the variable supplying that file name
  • An option to select the write method (create, overwrite, append, etc.)
  • A column pane to allow source-to-target mappings

notes 70 2 SQL SERVER   Using Raw Files in SSIS   Notes from the Field #070

Note that on the Write Option selection, there are some limitations with respect to whether the specified output file already exists. For example, if you choose the Create Once option, you’ll see a design-time error if the file already exists. Similarly, if you choose either the Append or the Truncate and Append option, you’ll see an error if the file does not yet exist. To make the design-time experience easier, there is a button in the UI of the destination to create the initial raw file in case you want to use either of the Append options without having previously created the file.

The raw file source, the simpler of the two raw file components, has just a couple of configuration options: the type of file name, a selector to choose the file name or the variable supplying that file name, and a data grid to allow the selection of the columns to be included from the raw file.

notes 70 3 SQL SERVER   Using Raw Files in SSIS   Notes from the Field #070

As shown in use below, the raw file source feeds the data flow pipeline, ultimately sending its data into the OleDB destination.

notes 70 4 SQL SERVER   Using Raw Files in SSIS   Notes from the Field #070

Final thoughts

One thing you’ll want to keep in mind is that there is no automatic purge process to empty or remove the raw files after use. Therefore, if there is a concern about persisting the files, either because of disk space or data sensitivity, there should be a follow-on process that truncates or deletes the raw files after they are no longer needed. Also, if you want to track how recently the raw file was loaded, you could either check the update stamp of the file (using a script task), or use a date stamp column in the process that loads the raw file to allow storage of the load date in the data within the file.

Conclusion

The raw file source and destination in SSIS can be used to address situations where temporary file system storage of result sets is required in an ETL process. Although not an everyday set of tools, this source and destination are useful tools to know how to use in cases where persisting interim result sets is needed.

If you want me to take a look at your server and its settings, or if your server is facing any issue we can Fix Your SQL Server.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – The Basics of the Execute Package Task – Notes from the Field #067

[Note from Pinal]: This is a new episode of Notes from the Field series. SQL Server Integration Service (SSIS) is one of the most key essential part of the entire Business Intelligence (BI) story. It is a platform for data integration and workflow applications.

andyleonard SQL SERVER   The Basics of the Execute Package Task   Notes from the Field #067

In this episode of the Notes from the Field series I asked SSIS Expert Andy Leonard a very crucial question – What are the Basics of the Execute Package Task and where do we start with it? Andy was very kind to answer the questions and provides plenty of information about how a novice developer can learn SSIS from the beginning and become expert in the technology.


Learning how to configure and use the SQL Server Integration Services (SSIS) Execute Package Task gives you a great starting point for understanding SSIS package orchestration. I advocate writing small, functional SSIS packages that perform a unit of work. That’s a great idea for many reasons. But it begs the question: “How do I string together the execution of these packages?” Remember: SSIS is a software development platform. With “SQL Server” included in the name, it is easy for people to confuse SSIS as a database tool or accessory, but Control Flow Tasks put that confusion to rest.

SSIS provides several Control Flow tasks. Here is a list that provides a good approximation of which tasks I use most, from most-used to least-used:

In this article I provide a basic example of configuring the SSIS Execute Package Task, shown in Figure 1:

notd 66 1 SQL SERVER   The Basics of the Execute Package Task   Notes from the Field #067
Figure 1: SSIS Execute Package Task

The Execute Package Task provides one way to implement an SSIS Design Pattern for SSIS package execution known as the Parent-Child pattern. When an SSIS package uses the Execute Package Task to start another SSIS package, the package with the Execute Package Task is called the Parent and the package started by the Execute Package Task is called the Child.

The Execute Package Task changed between SQL Server 2008 R2 Integration Services and SQL Server 2012 Integration Services. The changes support the SSIS 2012 (and 2014) Catalog. There is a new property called ReferenceType which defaults to “Project Reference” in packages executing in Project Deployment Mode (the default mode for building SSIS 2012 and SSIS 2014 SSIS packages), as shown in Figure 2:

notd 66 2 SQL SERVER   The Basics of the Execute Package Task   Notes from the Field #067
Figure 2: Options for the Execute Package Task ReferenceType Property

Project Reference is used to execute an SSIS package – a Child package – in the same SSIS project with the package that contains the Execute Package Task – the Parent package. When Project Reference is selected, the next property in the property grid is PackageNameFromProjectReference, a dropdown containing a list of all the SSIS package in the SSIS project.

Setting the ReferenceType property to External Reference is a way to execute SSIS packages that are stored in the file system or the msdb database. When External Reference is selected the next properties in the property grid change to reflect this backwards-compatible functionality, as shown in Figure 3:

notd 66 3 SQL SERVER   The Basics of the Execute Package Task   Notes from the Field #067
Figure 3: Setting the ReferenceType Property to External Reference

The Location and Connection properties are used to specify an OLE DB Connection (to the msdb database) or a File Connection (to the location of the dtsx file). SQL Server locations also require the name of the SSIS package; the File System option does not because the SSIS package is the only thing in the file. The External Reference ReferenceType setting is useful for importing SSIS solutions that contain earlier versions of the Execute Package Task because External Reference behaves exactly like previous versions of the Execute Package Task.

The remaining properties in the property grid – Password and ExecuteOutOfProcess – are shared between ReferenceType options. Password is used if the SSIS package is password-protected (if the ProtectionLevel property of the SSIS package is set to either EncryptSensitiveWithPassword or EncryptAllWithPassword). ExecuteOutOfProcess is a setting that determines whether the package will be executed as part of the current process or a new process.

When executing SSIS packages in Project Deployment Mode (using the Project Reference ReferenceType), Parameter Bindings are enabled and allow values to be passed from the Parent package to the Child package, as shown in Figure 4:

notd 66 4 SQL SERVER   The Basics of the Execute Package Task   Notes from the Field #067
Figure 4: Binding a Parent Package Parameter to a Child Package Parameter

Parameter Bindings are disabled when the ReferenceType property is set to External Reference.

Once configuration is complete, click the OK button to close the Execute Package Task Editor. You can test execution by pressing the F5 key or selecting “Start Debugging” from the SSIS dropdown menu. A successfully-executed Execute Package Task will appear as shown in Figure 5:

notd 66 5 SQL SERVER   The Basics of the Execute Package Task   Notes from the Field #067
Figure 5: A Successful Execution!

The Execute Package Task drives a powerful data integration architecture pattern: Parent-Child execution. Using SSIS Precedent Constraints and Sequence Containers with the Execute Package Task, a data integration developer can develop SSIS “driver” packages that call Child SSIS packages in any combination of parallel and serial orders of execution.

If you want to get started with SSIS with the help of experts, read more over at Fix Your SQL Server.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – The Basics of the SSIS Script Task – Notes from the Field #065

[Note from Pinal]: This is a new episode of Notes from the Field series. SQL Server Integration Service (SSIS) is one of the most key essential part of the entire Business Intelligence (BI) story. It is a platform for data integration and workflow applications.

andyleonard SQL SERVER   The Basics of the SSIS Script Task   Notes from the Field #065

In this episode of the Notes from the Field series I asked SSIS Expert Andy Leonard a very crucial question – What are the basics of the SSIS script task and where do we start with it? Andy was very kind to answer the questions and provides plenty of information about how a novice developer can learn SSIS from the beginning and become expert in the technology.


Learning how to configure and use the SQL Server Integration Services (SSIS) Script Task gives you a great starting point for learning how to use SSIS Control Flow tasks. You’ll find that SQL Server Control Flow tasks provide powerful development capabilities that may surprise you. Let there be no mistake, SSIS is a software development platform. With “SQL Server” included in the name, it is easy for people to confuse SSIS as a database tool or accessory, but Control Flow Tasks put that confusion to rest.

SSIS provides several Control Flow tasks. Here is a list that provides a good approximation of which tasks I use most, from most-used to least-used:

This article will focus on a basic example of configuring the SSIS Script Task, shown in Figure 1. People often think the SSIS Script Task is just for generating log messages, but let’s look at some of its more powerful capabilities.

ntf 65 1 SQL SERVER   The Basics of the SSIS Script Task   Notes from the Field #065

Figure 1: SSIS Script Task

The Script Task supports Microsoft Visual C# and Microsoft Visual Basic languages. You can choose which language you will use prior to clicking the Edit Script button. Once you’ve clicked the Edit Script button, you cannot change the Script Language property.

At the heart of the Script Task is the ability to read a variable value from the SSIS package into the script and then write a message out of the Script Task. Because the Script Task can interact with SSIS variables, you can use .Net code to manipulate and respond to variable values.

For the purpose of our demonstration, we will add code to the Script Task to create a message for SSIS logging.

Messaging is a key component of enterprise architecture. Messages are generated (published, raised, produced, etc.) by Senders and received (subscribed, consumed, requested, etc.) by Listeners. Messaging is a complex topic. Entire books are dedicated to the topic.

Add variables to the ReadOnlyVariables and ReadWriteVariables properties by clicking the ellipsis inside the value textbox for each property. When you click the ellipsis, a Select Variables dialog appears, as you see in Figure 2.

ntf 65 2 SQL SERVER   The Basics of the SSIS Script Task   Notes from the Field #065

Figure 2: Adding Read Only Variables to the Script Task

SSIS variables use a two-part naming convention: <Namespace>::<VariableName>. I added the SSIS variables System::TaskName and System::PackageName. The selected variables are in the System namespace and contain the name of the task and package, respectively.

Click the Edit Script button to open the script editing window as shown in Figure 3:

ntf 65 3 SQL SERVER   The Basics of the SSIS Script Task   Notes from the Field #065

Figure 3: The Script Task Script Editor

Add the following code beneath the instruction to Add your code here:

Dim sPackageName As String = _ Dts.Variables("System::PackageName").Value.ToString
Dim sMsg As String = "I am " & sPackageName
Dts.Events.Fireinformation(1001, sPackageName, sMsg, "", 0, True)

Listing 1: VB.Net Code

The first line of the code in Listing 1 creates a VB.Net variable named sPackageName and initializes the value of this variable, setting it to the value of the SSIS Variable named System::PackageName. This is one way to read SSIS Variable values into a Script Task’s script.

The second line of the code in Listing 1 creates a VB.Net variable named sMsg and initializes the value of this variable with the text, “I am” plus the value contained in the sPackageName VB.Net variable.

The third line of the code shown in Listing 1 calls the FireInformation method in the Dts.Events assembly. FireInformation accepts six arguments:

  1. InformationCode – an integer you can use to identify custom messages sent from the SSIS package.
  2. SubComponent – a string you can use to populate the source of the message.
  3. Description – a string containing the message you wish to send.
  4. HelpFile – a string containing the location of a Help file (should a help file exist).
  5. HelpContext – an integer pointing to the help context (should a help context exist).
  6. FireAgain – a Boolean value indicating whether the event should subsequently fire.

My VB code includes a reference to the FireAgain argument. Note: This MSDN post states: “Because firing of an event may be expensive, the run-time engine provides a mechanism for suppressing events that you are not interested in. Every event firing method has a FireAgain parameter. If the value of this variable is false, after the method returns, the caller will not fire this event again for the duration of the current execution.” I understand this to mean setting FireAgain to False would suppress future FireInformation events in my Script Task, but testing indicates this is not correct. This is important for you to know because you may opt to use the FireAgain argument to manage when you raise events. I do not use the FireAgain argument for this purpose. I typically manage the decision to raise events in conditional logic surrounding the Event-raising code.

A test execution of the package reveals an Information message recorded on the Progress / Execution Results tab as shown in Figure 4.

ntf 65 4 SQL SERVER   The Basics of the SSIS Script Task   Notes from the Field #065

Figure 4: the Information Message on the Progress / Execution Results Tab

Information messages generated from Script Tasks will appear in SSIS logs and the SSIS Catalog. With this information, you can monitor the progress of SSIS execution while the package is running, or view the historical status of variable values or package messages after the package execution has completed.

The SSIS Script Task can accomplish much more than generating log messages. This brief introduction and basic example have demonstrated how to get started configuring and using the SSIS Script Task. As you can see, SSIS Script Tasks give you development capabilities you may not have been aware of.

If you want to get started with SSIS with the help of experts, read more over at Fix Your SQL Server.

Reference: Pinal Dave (http://blog.sqlauthority.com)