Using T-SQL to programmatically edit and save a SSIS config file

Question

Using T-SQL to programmatically edit and save a SSIS config file

Weegee2017

SSC Eights!

Points: 994
More actions
September 18, 2019 at 10:10 am

#3681018
I want to be able to edit a SSIS Config file for a different company, each time that I ran it.
Each time, we run the SSIS package, we modify the Company name in each of the Configuration Values
This is highlighted in the attached screenshot
Is there a way from T-SQL that this file can be read in and the Company Name on of the ConfigurationValue lines be updated and then the file re-saved?
- This topic was modified 5 years, 2 months ago by Weegee2017.
- This topic was modified 5 years, 2 months ago by Weegee2017.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

GonnaCatchIT SSCrazy Points: 2797 More actions · Answer 1

U can try exploring the CLR assemblies feature of SQL. However not recommended to keep modifying the DTSConfig files programatically. OR another (kind of dirty) way

Write a SSIS package use a script task to modify the DTSCOnfig file...

Host a SQL job to invoke the SSIS package.

Call this SQL job from your SP or however you want to.

This reply was modified 5 years, 2 months ago by GonnaCatchIT.

Weegee2017 SSC Eights! Points: 994 More actions · Answer 2

Thank you....so is it quite straight forward to create a script task that can modify the DTSconfig file?

GonnaCatchIT SSCrazy Points: 2797 More actions · Answer 3

It should be, since its just a XML file. You use .net c# script task to modify DTSConfig files, search for it - you will be able to find lot of help already.