How can I programmatically modify the connection string of a flat file connection to a SSIS package?

Question

I have 33 SSIS packages that I currently schedule through different batch files that run throughout the day and I am looking for solutions that are easier to maintain. Since my organization only allows DBAs to utilize the SQL server agent, what I landed on was for each looping through a resultset with variables User::PCKG and User::RUN that will split out the name of the package and whether the package should be run or not based on the time of day when and when it was last run. The idea is then to use a precedence constraint (@[User::RUN]>0) to determine whether the package should flow to run the package in question and programmatically feeding the execute package task the file location of the package to run.

--FAKESCHDL
SELECT      'PCKG1' as PCKG
            ,cast('08:00:00' as time) as SCHDL
INTO        #FAKESCHDL
INSERT INTO #FAKESCHDL
SELECT      'PCKG2' as PCKG
            ,cast('18:00:00' as time) as SCHDL
--FAKELOG
SELECT      'PCKG1' as PCKG
            ,cast('2019-11-16' as date) as RUN_DT
INTO        #FAKELOG
INSERT INTO #FAKELOG
SELECT      'PCKG2' as PCKG
            ,cast('2019-11-16' as date) as RUN_DT
--QUERY
SELECT      a.PCKG
            ,case   when a.SCHDL <= cast(getdate() as time) and b.RUN_DT < cast(getdate() as date) then 1
                    when b.RUN_DT < dateadd(d,-1,cast(getdate() as date)) then 1
                    else 0
                    end as RUN
FROM        #FAKESCHDL as a
            LEFT JOIN #FAKELOG as b
                on a.PCKG = b.PCKG
--DROP TABLES
DROP TABLE  #FAKESCHDL
DROP TABLE  #FAKELOG

public void Main()
        {
            Variables varCollection = null;

            Dts.VariableDispenser.LockForWrite("User::PCKG");

            string path = @"C:\Users\thisuser\source\repos\Integration Services Project2\Integration Services Project2\";
            string PCKG = path + (string)varCollection["User::PCKG"].Value + ".dtsx";

            Dts.Connections["PCKG"].ConnectionString = PCKG;

            Dts.TaskResult = (int)ScriptResults.Success;
        }

The precedence constraint seems to works fine, the issue I'm having is with programmatically modifying the connection string with C#. I think perhaps this is a simple issue of syntax or perhaps I need to do something to make the Dts.Connection["PCKG"].ConnectionString readwrite, but since I think most have the preferable solution of what I'm trying to do is using the server agent, there doesn't seem to be much that I've found that would answer the question.

Answer 1

By design of SSIS engine, package tasks cannot modify Connection Managers or other package properties directly. So, Connection Manager properties are read-only at Script Task.
What you could do to solve your problem:

• Create a package string variable, say, MyPath
• On Connection Manager PCKG set an expression on property ConnectionString to MyPath variable
• Assign the MyPath var correct value, say, in Expression Task inside your Foreach Loop

This will do the following - Expression task will create a full path in the variable, and each time SSIS refers to PCKG Connection Manager, SSIS substitutes contents of MyPath variable into Connection String of the Conn Manager.