{"id":150,"date":"2006-08-30T00:00:00","date_gmt":"2006-08-29T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/executing-ssis-packages\/"},"modified":"2021-06-03T16:45:31","modified_gmt":"2021-06-03T16:45:31","slug":"executing-ssis-packages","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/bi-sql-server\/executing-ssis-packages\/","title":{"rendered":"Executing SSIS Packages"},"content":{"rendered":"
\n

Executing all SSIS packages in a folder: three methods<\/h2>\n

A common requirement is to execute several SSIS packages as part of one logical process. This is especially true when one is transferring existing data from an external system.<\/p>\n

If the indivisual packages are placed in a folder and named in such a way that a batch can be executed via a wildcard filemask, then they can be altered without changing any code, and can be used for different processes by changing the filemask. This article presents three different methods of executing all packages in a folder:<\/p>\n

    \n
  1. SSIS control package<\/li>\n
  2. Stored procedure<\/li>\n
  3. DOS batch file<\/li>\n<\/ol>\n

    The methods are of differing complexity and flexibility, the SSIS package being by far the most difficult to code, and the DOS command being by far the simplest but also the least flexible.<\/p>\n

    The folder that contains the packages is d<\/span>:\\TestPackages\\<\/span>.<\/p>\n

    All the packages to be run have names of the format t1…….dtsx<\/span>.<\/p>\n

    SSIS control package<\/h3>\n

    For this we create a package that uses a for each loop task to call an execute package task for all packages in the folder.<\/p>\n

    The first task is to create an SSIS package with a Foreach Loop container that will loop round the packages in the folder, setting a variable called “PackageToRun” to the file name for each package.<\/p>\n

      \n
    1. Load Business Intelligence Development Studio and start a SSIS project.<\/li>\n
    2. Create a new package.<\/li>\n
    3. Add a Foreach Loop container to the package.<\/li>\n
    4. Right-click on the Foreach Loop<\/strong> container and select Edit<\/b>.<\/li>\n
    5. Click on Collection<\/b>.<\/li>\n
    6. Set the Enumerator to Foreach File Enumerator<\/b>.<\/li>\n
    7. In the Enumerator configuration:\n
        \n
      1. Set Folder<\/b> to “d:\\TestPackages\\”<\/li>\n
      2. Set Files<\/b> to “t1*.dtsx”<\/li>\n
      3. Under Retrieve file name<\/b> select Fully qualified<\/b>.<\/li>\n<\/ol>\n<\/li>\n
      4. Click OK<\/b>.<\/li>\n<\/ol>\n

        \"270-image001.gif\"<\/p>\n

          \n
        1. Click on Variable Mappings<\/b>.<\/li>\n
        2. Click on the Variable<\/b> drop-down list and select New Variable<\/b>.<\/li>\n
        3. Set Name to PackageToRun<\/span>.<\/li>\n
        4. Click OK<\/b>.<\/li>\n<\/ol>\n

          \"270-image002.gif\"<\/p>\n

          Next, we need to add the Execute Package<\/b> task to the Foreach Loop container so that this task will be executed for each package that we wish to run. We then set the variable value to be the name of the package to be executed by the ExecutePackage<\/b> task.<\/p>\n

            \n
          1. Drag an Execute Package<\/b> task into the Foreach Loop container.<\/li>\n
          2. Right-click on the Execute Package<\/b> task and select Edit<\/b>.<\/li>\n
          3. Select Package<\/b>.<\/li>\n
          4. Set Location to File system<\/span><\/li>\n<\/ol>\n

            \"270-image003.gif\"<\/p>\n

              \n
            1. Click on the Connection<\/strong> drop-down list and select <New connection…><\/span>.<\/li>\n
            2. Set the File to an existing package.<\/li>\n<\/ol>\n

              \"270-image004.gif\"<\/p>\n

                \n
              1. Click OK<\/b> to save the connection.<\/li>\n
              2. Click OK<\/b> to complete the Execute Package task configuration.<\/li>\n<\/ol>\n

                Finally, we configure the connection to use the variable package name:<\/p>\n

                  \n
                1. Right-click on the connection and select Properties<\/b>.<\/li>\n
                2. In the Properties window change the name to PackageToRunConnection<\/span>.<\/li>\n<\/ol>\n

                  Note<\/i><\/b>: this will automatically update the connection name in the Execute Package Task.<\/i><\/p>\n

                    \n
                  1. Select Expressions and add a new expression.<\/li>\n
                  2. In the property drop-down list select ConnectionString<\/b>.<\/li>\n
                  3. Click on the Expression Edito<\/b>r button.<\/li>\n
                  4. From the Variables tree drag @[User::PackageToRun]<\/span> into the Expression window.<\/li>\n<\/ol>\n

                    \"270-image005.gif\"<\/p>\n

                      \n
                    1. Click OK<\/b> twice, to save the expression.<\/li>\n<\/ol>\n

                      The package should now look like this:<\/p>\n

                      \"270-image006.gif\"<\/p>\n

                      If you now run the package, it will execute each package in the folder with the correct filename mask.<\/p>\n

                      Stored Procedure<\/h3>\n

                      This involves the following steps:<\/p>\n

                        \n
                      1. Retrieve a list of the package names to run from the folder<\/li>\n
                      2. Loop through each package name<\/li>\n
                      3. Execute each package<\/li>\n<\/ol>\n

                        First of all, we need to get a list of all packages in the folder. The following code executes the DOS command dir \/B d:\\TestPackages\\ t1*.dtsx<\/b> and places the output in the temporary table #dir<\/b>. The DOS command gets a list of the filenames in the folder which satisfy the filemask.<\/p>\n

                        Create table #dir (Filename varchar(1000))\r\nInsert #dir\r\nExec master..xp_cmdshell 'dir \/B d:\\TestPackages\\ t1*.dtsx'\r\ndelete #dir where Filename is null or Filename like '%not found%'<\/pre>\n
                        The following is a useful technique for looping through values in a table and here we use it to loop through the filenames in #dir<\/b>:<\/p>\n
                        Declare @Filename varchar(1000)\r\nSelect @Filename = ''\r\nWhile @Filename < (select max(Filename) from #dir)\r\nBegin\r\n\u00a0\u00a0 Select @Filename = min(Filename) from #dir where Filename > @Filename\r\nend<\/pre>\n
                        Now we just need to create the command to execute the package:<\/p>\n
                        Declare @cmd varchar(1000)\r\nselect @cmd = 'dtexec \/F \" d:\\TestPackages\\' + @FileName + '''\r\nexec master..xp_cmdshell @cmd\r\n<\/pre>\n
                        This takes the package name and uses the dtexec<\/strong> command to execute it. Putting this all together, we get:<\/p>\n
                        Declare @Filename varchar(1000)\r\nDeclare @cmd varchar(1000)\r\nCreate table #dir (Filename varchar(1000))\r\nInsert #dir\r\nExec master..xp_cmdshell 'dir \/B d:\\TestPackages\\t1*.dtsx'\r\ndelete #dir where Filename is null or Filename like '%not found%'\r\nSelect @Filename = ''\r\nWhile @Filename < (select max(Filename) from #dir)\r\nBegin\r\n\u00a0\u00a0 Select @Filename =\u00a0 min(Filename) from #dir where Filename > @Filename\r\n\u00a0\u00a0 select\u00a0 @cmd =\u00a0 'dtexec \/F \"d:\\TestPackages\\' + @Filename + '\"'\r\n\u00a0\u00a0 exec master..xp_cmdshell @cmd\r\nend\r\ndrop table #dir\r\n<\/pre>\n
                        The above completes the code needed for the requirement but to simplify the implementation I like to keep a utility stored procedure which calls another stored procedure for each file (SSIS package) in a folder. The following utility stored procedure, ProcessAllFilesInDir<\/b>, uses the above techniques to obtain a list of files from a folder and calls another stored procedure for each one. The file path and mask are input as parameters together with the @ProcSP<\/b> parameter that defines the stored procedure to be called for each file (in this case this will be the ExecutePackage<\/b> procedure that executes each SSIS package).<\/p>\n
                        Create procedure [dbo].[s_ProcessAllFilesInDir]\r\n@FilePath varchar(1000) ,\r\n@FileMaskvarchar(100) ,\r\n@ProcSp varchar(128)\r\nas\r\n\u00a0\u00a0 set nocount on\r\n\u00a0\u00a0 \r\ndeclare @File varchar(128) ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 @MaxFile varchar(128) ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 @cmd\u00a0\u00a0\u00a0\u00a0 varchar(2000)\r\n\u00a0\u00a0 \r\n\u00a0\u00a0 create table #Dir (s varchar(8000))\r\n\u00a0\u00a0 \r\n\u00a0\u00a0 select @cmd = 'dir \/B ' + @FilePath + @FileMask\r\n\u00a0\u00a0 insert #Dir exec master..xp_cmdshell @cmd\r\n\u00a0\u00a0 \r\n\u00a0\u00a0 delete #Dir where s is null or s like '%not found%'\r\n\u00a0\u00a0 \r\n\u00a0\u00a0 select @File = '', @MaxFile = max(s) from #Dir\r\n\u00a0\u00a0 while @File < @MaxFile\r\n\u00a0\u00a0 begin\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 select @File = min(s) from #Dir where s > @File\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 \r\n\u00a0\u00a0\u00a0\u00a0\u00a0 select @cmd = @ProcSp + ' ''' + @FilePath + ''' , ''' + @File + ''''\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 \r\n\u00a0\u00a0\u00a0\u00a0\u00a0 exec (@cmd)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 \r\n\u00a0\u00a0 end\r\n\u00a0\u00a0 drop table #Dir\r\ngo\r\n<\/pre>\n
                        With this, the only coding needed is to create the procedure to execute the package and to call it. The ExecutePackage<\/b> procedure is as follows:<\/p>\n
                        \t\tCreate procedure [dbo].[s_ExecutePackage]\r\n@FilePath\u00a0\u00a0 varchar(1000) ,\r\n@Filename\u00a0\u00a0 varchar(128)\r\nas\r\nDeclare @cmd varchar(1000)\r\n\u00a0\u00a0 select @cmd =\u00a0 'dtexec \/F \"' + @FilePath + @Filename + '\"'\r\n\u00a0\u00a0 exec master..xp_cmdshell @cmd\r\ngo\r\n<\/pre>\n
                        And the call:<\/p>\n
                        Exec [dbo].[s_ProcessAllFilesInDir]\r\n\u00a0\u00a0 @FilePath = 'd:\\TestPackages\\' ,\r\n\u00a0\u00a0 @FileMask = 't1*.dtsx' ,\r\n\u00a0\u00a0 @ProcSp = '[dbo].[s_ExecutePackage]'\r\n<\/pre>\n
                        DOS batch file<\/h3>\n
                        The DOS command required is dtexec \/F “d:\\TestPackages\\mypackagename<\/b>“<\/b> as above. We can make use of a DOS For loop to execute this command for all files in a folder:<\/p>\n
                        for %1 in (d:\\TestPackages\\t1*.dtsx) do dtexec \/F \"%1\"<\/pre>\n
                        This uses the for loop construct:<\/p>\n
                          \n
                        • (d:\\TestPackages\\t1*.dtsx) <\/b>returns a list of the files which meet the path (similar to a dir command).<\/li>\n
                        • For %1 in (d:\\TestPackages\\t1*.dtsx)<\/b> loops through the file list setting the variable %1 to the filename.<\/li>\n
                        • Do dtexec \/F %<\/b> executes dtexec \/F<\/b> with each filename in turn.<\/li>\n<\/ul>\n
                          Note that you can place this command in a bat<\/b> file to execute.<\/p>\n
                          Summary<\/h2>\n
                          We have introduced three different methods of executing all packages in a folder. The methods are of differing complexity, the SSIS control package being by far the most difficult to code, and the DOS command by far the simplest.<\/p>\n
                          There are a few things missing from the solutions presented which would (or should) prevent them from being used in a production system. At a minimum this should include logging and error handling. The start and end time of each package execution should be logged by the control process and also errors should be detected and logged.<\/p>\n
                          At first sight, it may seem that the DOS comman<\/b>d is the one that we should be using, but this method loses a lot of flexibility. There is much you can do with batch files, but not many people have skills in writing such code beyond very simple commands, so you can quickly end up with an un-maintainable system.<\/p>\n
                          The stored procedure<\/b> solution gives a lot of flexibility – it is easy to add logging and pre\/post processing to the process. However, it does require an installation of SQL Server. Although this process would usually be run via SQL Server, it may sometimes need to be run on a client machine (or not involve SQL Server at all); this is quite difficult with a stored procedure as it is server based whereas the other two solutions are client applications.<\/p>\n
                          The SSIS solution<\/b> keeps the process within SSIS, so you can take advantage of the integration of package processing, e.g. inheritance of properties from the calling package, development and debugging of packages, within the development studio.<\/p>\n
                          Overall, none of these solutions is very difficult to implement, so the decision which to use will probably depend on the experience of the developer, how much flexibility is required, and the environment in which it is to be run.<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"
                          Nigel Rivett demonstrates how to execute all SSIS packages in a given folder using either an SSIS control package, a stored procedure or a DOS batch file.…<\/p>\n","protected":false},"author":223267,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143528],"tags":[4405,4179,4150,4404,4306],"coauthors":[48340],"class_list":["post-150","post","type-post","status-publish","format-standard","hentry","category-bi-sql-server","tag-package-execution","tag-source-control","tag-sql","tag-sql-server-2005-integration-services","tag-ssis"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/150","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/223267"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=150"}],"version-history":[{"count":6,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/150\/revisions"}],"predecessor-version":[{"id":74762,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/150\/revisions\/74762"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=150"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=150"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=150"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=150"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}