How to set parameters' values to SSIS packages

Hi,

This post to walkthrough step-by-step how to pass parameters to a SSIS package.
Usually you have set of parameters and you want to set the values at run time before the package runs.

The methodology that i will implement in this post, is to export the parameters that i want to set from an XML file by defining the SSIS configuration file using Visual Studio BIDS 2008/2010 or SQL Data tools.

Let's say, you have a SSIS package with a parameter called @myParameter, Here is how to setup the configuration file for the SSIS so we can pass it when the package runs.

Steps:
1) Open yout SSIS package.
2) Right click after you open the package and select on "Package Configurations".

 

3) Set the configuration file name and click on Next button.



4) Under Variables node, select the parameter name "@myParameter" you want to export to the configuration file, then click on Next button.
5) Write a name for the configuration and click on next.
6) The VS BIDS will create a XML file with the exported parameter properties.
7) open "TestPackage.dtsConfig" using the notepad and set the value property for the exported parameter.
8) Change the value of the parameter value in the dtsConfig and then save the file.
9) Run your package and the SSIS package will get the updated value from the xml file.

Hope this helps.


 

How to parse comma delimited string into IN Clause in SQL Server

Hi Folks,

Today i was trying to pass multi-value parameter into my select query in SQL Server. To accomplish this i stored the multi-value parameter in a comma delimited string. Then, i want to pass it in my IN clause of the select statement. here is an example to what i want to accomplish:

Delcare @myVar varchar(500)
Set @myVar='1,2,3,4,5,7'

Select * from Employee
where EmployeeId IN (@myVar)

You will get this error:
Msg 245, Level 16, State 1, Line 7
Conversion failed when converting the varchar value '1,2,3,4,5,7' to data type int.

It makes sense, because i have a varchar that holds all my values and i want to pass those in the IN clause that only accepts integer values!.

The solution for this problem is to parse this string into set of integers and pass it back to your query in your IN clause. For this reason, i have created a function that return a table of 1 column of type int. This will be passed back to the select statement i have mentioned above.

The function code to parse comma delimited string into set of integers:

-- =============================================
-- Author: Mostafa Elzoghbi
-- Create date: 12/10/2012
-- Description: Parse a string into set of numbers
-- =============================================
CREATE FUNCTION [dbo].[ParseCommaDelimitedString]

( @CommaSeparatedStr
nvarchar(1000) =NULL)
RETURNS @myTable TABLE ([Id] [int] NOT NULL)
AS
BEGIN
declare @pos int
declare @piece varchar(500)
-- Need to tack a delimiter onto the end of the input string if one doesn't exist
if right(rtrim(@CommaSeparatedStr ),1) <> ','
set @CommaSeparatedStr = @CommaSeparatedStr + ','
set @pos = patindex('%,%' , @CommaSeparatedStr )
while @pos <> 0
begin
set @piece = left(@CommaSeparatedStr , @pos - 1)
-- You have a piece of data, so insert it, print it, do whatever you want to with it.
insert @myTable
select @piece

set @CommaSeparatedStr = stuff(@CommaSeparatedStr , 1, @pos, '')
set @pos = patindex('%,%' , @CommaSeparatedStr )
end
RETURN
END
-- =============================================

What you need after that to re-write your query as follows:

Select *
from Employee
where EmployeeId IN
( select * from ParseCommaDelimitedString(@myVar))

Now, you will be able to pass any comma delimited string, varchar,nvarchar to this function and it returns table of integer that you can set in the IN clause of you select statement or any other T-SQL statements you works with your logic.

Hope this helps.

--ME