Passing values dynamically from config file to SQL query in SSIS

Question

I have developed a SSIS Package in which I am using a SQL task. The .SQL file is being picked up from a particular folder in my file system. My SQL Script is creating a database and creating some SQL logins.

I need to pass the username/login name dynamically through config file in my .SQL file.

CREATE USER [userneme/login name] FOR LOGIN [userneme/login name ] WITH DEFAULT_SCHEMA=[dbo]
GO
ALTER ROLE [db_datareader] ADD MEMBER [userneme/login name ]
GO

The SQL Task Property SQLSourceType has been set as FileConnection. How can I pass these values dynamically? I am already reading connection string from config file.

SQL File Looks like: USE [databasename] GO /****** Object: User [userneme/login name] Script Date: 15/09/2016 14:03:40 ******/ CREATE USER [userneme/login name] FOR LOGIN [userneme/login name] WITH DEFAULT_SCHEMA=[dbo] GO CREATE USER [userneme/login name] FOR LOGIN [userneme/login name] WITH DEFAULT_SCHEMA=[dbo] GO — kapil

Raj More Raj More · Accepted Answer · 2016-12-12T15:07:22

You can set up your input file as a CSV, and loop through the rows, and execute a script one row at a time.

Passing values dynamically from config file to SQL query in SSIS

1 Answers