SQL SERVER – SSIS – Get Started with the For Loop Container – Notes from the Field #113

[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 Get Started with the For Loop Container.

Tim Mitchell SQL SERVER   SSIS   Get Started with the For Loop Container   Notes from the Field #113Linchpin People are database coaches and wellness experts for a data driven world. In this 113th episode of the Notes from the Fields series database expert Tim Mitchell (partner at Linchpin People) shares very interesting conversation related to how to get started with the FOR LOOP Container.


SQL Server Integration Services is equipped with tasks and containers to make it easy to design and maintain the flow of ETL: which logic should be executed, when should it be executed, and how many times should it occur. Most SSIS developers are familiar with the sequence container and the For Each Loop container, which can be used to group together tasks and execute the same logic a discrete number of times. In addition to these, there is a lesser-known but still very useful container for controlling logic flow: the For Loop container.

Simply put, the For Loop container executes its ETL logic zero to n times. It has three functions that control how many times the loop will be executed:

  • InitExpression: Used for the initial setting of a starting variable (such as setting a counter variable to zero).
  • EvalExpression: This is the expression that evaluates whether the loop should continue. Of the three functions described here, this is the only one that requires a value – the others are optional.
  • AssignExpression: This allows the use of an assignment expression, such as incrementing a loop counter.

For those with a programming background, this look very much like a for() loop statement in the C-derived languages. Functionally, it works in the exact same way as the for() loop, by continuing to execute the contained logic as long as the control condition remains true. This helps to draw contrast between the For Each Loop and the For Loop in SSIS. The former is list-based, and will execute for every item in the list supplied to it. The latter is value-based, and will execute as long as the EvalExpression is true.

In fairness, most ETL loads lean toward the list-based approach, but there are valid cases where a value-based approach is necessary. Some of those include:

  • Processing a fixed subset of data
  • Sampling for test or validation purposes
  • Forcing a “wait state” until some milestone is reached
  • Allowing the loop logic to be executed for some specified amount of time

Configuring the For Loop Container

As noted above, the only value that is required for the For Loop container is the EvalExpression. A very simple For Loop configuration is shown below.

113 1 SQL SERVER   SSIS   Get Started with the For Loop Container   Notes from the Field #113

The above supplies only the required value – a value of true to the EvalExpression. However, this is a very poorly configured For Loop, because the loop will continue executing indefinitely! True will always be true, so there is no logical end to this loop.

A more practical design pattern would use either an initialization expression, an assignment expression, or possibly both, to constrain the number of iterations. A simple example of this is shown below. I set up an SSIS package variable, typed as an Integer and named @vLoopCounter, with a default value of 0. In the For Loop settings, I’ve used the EvalExpression to check to see if this value is less than 10, and I use the AssignExpression to increment the @vLoopContainer value by 1 for every iteration of the loop.

113 2 SQL SERVER   SSIS   Get Started with the For Loop Container   Notes from the Field #113

This example works, executing any logic contained in the For Loop exactly ten times. However, this pattern is very static. What if I want to increase the value expression to let the loop run more than 10 times? I’d need to open the package and modify it. Fortunately, the configurations expressions allow for the use of both variables and parameters. Below, the package has a couple of updates: an initial value of 1 is set for the @vLoopCounter variable, and the static comparison in EvalExpression is replaced by using the package parameter @pMaxLoops for the maximum number of loops.

113 3 SQL SERVER   SSIS   Get Started with the For Loop Container   Notes from the Field #113

In the example above, the number of maximum loops can be specified at runtime, making for a more dynamic pattern.

The examples above show only an iteration based on the number of times the loop has run. Keep in mind when using the For Loop container, this logic can be based on any statement we choose: whether a particular set of files exist, how long the For Loop has been running, how many records have been processed, or some other custom metric specified in the expression. Even with a small number of inputs controlling how many times the For Loop container will execute, the possible uses for this are many, and can be as complex as needed.

Conclusion

The For Loop container provides another way to execute repeating logic in SSIS. By using an approach similar to the for() loop in structured programming languages, the For Loop container adds more ETL flexibility through a value-based iterative pattern.

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 – Getting Started with Project Versions in the SSIS Catalog – Notes from the Field #106

[Notes from Pinal]: We are human and we make mistakes. However, sometimes mistakes are so big that we can’t reverse it. Version control is our rescue when we make mistakes. In my life I have been fortunate few times when I deleted something important and version controlled saved me. Similarly, for SSIS Catalog version control is very important as well.

Tim Mitchell SQL SERVER   Getting Started with Project Versions in the SSIS Catalog   Notes from the Field #106Linchpin People are database coaches and wellness experts for a data driven world. In this 106th episode of the Notes from the Fields series database expert Tim Mitchell (partner at Linchpin People) shares very interesting conversation related to how to do project versions in SSIS catalog .


A handy but frequently overlooked feature of the SSIS catalog is project versioning. When the SSIS catalog is installed on a server, by default the catalog is configured to store a limited number of previous versions of each distinct SSIS project deployed to the catalog. Not only does the catalog keep those versions, but the SSMS interface makes it easy to browse – and even revert to – previous versions.

In this post, I’ll show how you can use the version history feature in the SSIS catalog to access previously deployed versions of a project.

Accessing the Version History

The fact that the version history is available doesn’t really stand out when you’re browsing the SSIS catalog. To find it, you have to right-click the project in question, and in the context menu you’ll see the option to show versions as indicated below.

107 1 SQL SERVER   Getting Started with Project Versions in the SSIS Catalog   Notes from the Field #106

When I access the version history for my project named Testing SSIS Packages, the following dialog is shown. The version history here indicates that this project has ten different versions stored in the history (the default maximum – more on that momentarily). Also, the most recent version is the current version, as indicated by the check in the Current column on the far left.

107 2 SQL SERVER   Getting Started with Project Versions in the SSIS Catalog   Notes from the Field #106

Although this interface does not show what has changed, it does indicate when each version was deployed. There is also a Last Restored column, indicating if an older version was ever restored. By default, the Last Restored value will be null, until and unless that version is changed from a historical version to the current version.

Why Restore?

In a moment, I’ll demonstrate how to restore an older version of a project. First, though, I like to start with the why before I show the how. Why might you want to restore an older version of a deployed project? There are a few cases when this might be useful, but one of the most practical is for regression testing. Let’s say you deploy a project to your dev/test server (and you’re always testing before deploying to production, right?). If there is a question about a new version of an SSIS project, you can very easily revert to an older version and compare the old behavior to the new behavior. The other obvious case is simple human error – someone mistakenly deployed an incorrect or not-yet-ready project, which could be easily remedied by rolling back using the following method.

Rolling Back to a Prior Version

Reverting to an older version of a project is very easy. In the Project Versions dialog box, simply select the version you want to use as the current version and click the Restore to Selected Version button. You’ll be prompted to confirm that you want to do this, and if you answer in the affirmative, the selected version will then become the current version. As shown below, you can see that I have restored to a month-old version of the project, as indicated by the Current check box and the Last Restored date.

107 3 SQL SERVER   Getting Started with Project Versions in the SSIS Catalog   Notes from the Field #106

You might ask, what happened to the version I just replaced? Don’t worry – it’s still there. This does not do a restore in the same way a relational database is overwritten when it is restored. Rather, a configuration setting stored in the SSIS catalog simply points to the Project LSN value of the current version for each project, which may or may not be the most recently deployed version. When a package in this project is executed, the plumbing within the SSIS catalog will execute the version marked as current.

I can easily undo the change I just made by selecting the most recent version again, go through the restore exercise again, and we’re back to where we started.

107 4 SQL SERVER   Getting Started with Project Versions in the SSIS Catalog   Notes from the Field #106

As shown above, we’re back to where we started – the most recent version is the current version, and that version will be used when package(s) in that project are executed. The only difference is that we can see – by referencing the Deployed Time and Last Restored values – that the prior release version (Project LSN = 15) was temporarily restored, and then replaced as current by the more recent version (Project LSN = 16).

One last note on restoring a project: Because the code deployed to the SSIS catalog is grouped at the project (not package) granularity, it is not possible in current versions of SSIS to restore just a single package. However, in SSIS 2016, package-level deployment will return, which will change the behavior of restoring project versions.

Version Retention

As shown in the previous example, there are exactly ten historical versions of this project stored in the catalog. That is no accident – the maximum number of project versions is set to 10 by default. This is a configurable value, however. If you open the catalog properties window in SSMS, you’ll see that the maximum number of stored versions can be configured.

107 5 SQL SERVER   Getting Started with Project Versions in the SSIS Catalog   Notes from the Field #106

There are two configurable properties here: the maximum number of version to keep, and the Boolean value to enable the periodic removal of old versions. I have rarely found a need to stray from the default settings, but they are available for modification should you need to do so.

This Is Not Source Control!

One final though on versioning: Please don’t use this as a substitute for real source control! Just because SSIS will store the version history, it doesn’t mean you should use that as a means for source control. The versioning functionality is intended as an administrative tool for logging, testing, and if necessary, emergency rollback. The SSIS catalog version store does not have most of the features of a full-service source control tool. Therefore, you should be checking in your code to a proper source control repository even if you maintain the version history in the SSIS catalog.

Conclusion

The version history feature in the SSIS catalog is one of the best-kept secrets of this product. Although you hopefully won’t have to use this on a daily basis, it is a very handy tool to have at your disposal.

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 – 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)