Runtime Settings with SSIS Package

Introduction

Clients may require to integrate the SSIS packages in their environment(Production). SSIS package settings needs to be changed at run time and all settings needs to be pulled from the database using set of stored procedures.

1. Create a SSIS package and drag the Execute SQL task to control flow in the package as follows

2.  Add a package level variable by right clicking on the Control Flow design surface and name the variable “MyFile” and set the value to C:\ExecuteSQLsample.txt.

3.  Execute the following procedure in your database

USE AdventureWorks
go
create proc dbo.usp_GetConfigParamValue(
@ApplicationName Varchar(30), -- the name of the application
@ParameterName Varchar(50), -- the name of the parameter
@ParameterTypeName Varchar(30), -- the name of the parameter type
@ParameterValueVar Varchar(255)OUTPUT  -- output buffer for value
)

AS 

 SET NOCOUNT ON
 SET @ParameterValueVar = 'C:\ExecuteSampleChanged.txt'
 
 SET NOCOUNT OFF

4. Set the ConnectionType property of the Execute SQL Task to ADO.NET. Select

the <New Connection> from the Connection property. Use the drop down to select SQL Server instance and database “AdventureWorks”. Save the connection.

5. Set the SQLStatement property to the following

EXEC usp_GetConfigParamValue 'MYAPP', 'MYPARAM', 'STRING',
@MYVALUE OUTPUT

7. To finish out the package, Drop a dataflow task onto the Control Flow surface. Connect the output of the Execute SQL task to Data Flow task.

8. In the Data Flow design surface, add a Data Reader Source and a Flat File Destination.

9. Configure the Data Reader Source to use AdventureWorks connection and set SQLCommand property to the following

10. Connect the Data Reader transformation to the flat file destination.

11. Configure the flat file destination by creating a new connection to a delimited file in the destination file editor. set the file name to “C:\myfile.txt”.

12. Now we can change the flat file destination at run time. This package pull all records from [HumanResources].[Shift] table and dump to “C:\myfile.txt” in a delimited format. The package retrieves a string “C:\myfile.txt” from the stored procedure. You can change this file name at runtime by creating a expression.

13. Locate the expressions in SQL Execute task and select the @[user:Myfile] for expression.

Run the package and check your local drive. you should see the file “executesqlsamplechanged.txt”

Share this post :