Is it possible to update a value in an xml file via SSIS?

Posted on 2011-10-24
Last Modified: 2012-08-14
I want to update a value in an xml file (a variable value in a xml configuration file actually for a SSIS package), and was wondering whether it was possible within SSIS. I've noticed there's a XML Task in SSIS, and I think the XPATH operation type may be the way forward but I can't seem to find anything that would show me how to do it!
Question by:Grffster
    LVL 39

    Expert Comment

    I believe that just give you the oportunity to create more complex XML however you could execute a SQL stored procedure from SSIS that will output the XML file on the SQL server.
    LVL 39

    Expert Comment

    Here's an example how to use an SP to send the results into an XML file:
    LVL 2

    Expert Comment

    have you resolve your issue yet?


    Author Comment

    Just trying to get my head round lcohan's solution to be honest. It may well work but I want to ensure I understand what I'm trying out. Do you have an alternative method, Frank?
    LVL 2

    Expert Comment

    I don't really understand your setup and what you are trying to do, but if you just want to update a value in an XML config file this script might work.
    the parameters are self explanatory. you will have to enable the xp_cmdshell on you sql server to use the script.

    @MyVariable  nvarchar(200),
    @VarValue nvarchar(200),
    @sql nvarchar(3000),
    @FileLocation nvarchar(1000)

    set @FileLocation = 'C:\MyConfigFile.dtsConfig'
    set @MyVariable='User::VariableName'
    set @VarValue ='new Variable Value'

    set @sql=

    Create TABLE ConfigXMLTable(
                 XMLCol XML
    INSERT INTO ConfigXMLTable(XMLCol)
    FROM  (SELECT *    
      FROM OPENROWSET (BULK N'''+ @FileLocation +''', SINGLE_BLOB)
      AS xCol
     AS R(xCol);

     update ConfigXMLTable
       SET XMLCol.modify(''
       replace value of (/DTSConfiguration/Configuration[@Path="\Package.Variables['+ @MyVariable +'].Properties[Value]"]/ConfiguredValue[1]/text())[1]
      with  "'+ @VarValue+'"

    DECLARE @cmd NVARCHAR(400)
    SET @cmd=''bcp "SELECT  XMLCol FROM [MyDataBase].[dbo].[ConfigXMLTable]"  queryout  '+ @FileLocation +' -c -T'''
    +'EXEC xp_cmdshell @cmd;'

    +' drop table [MyDataBase].[dbo].[ConfigXMLTable];'


    Author Comment

    I have a sql server agent job that runs an ssis package via an xml package configuration file. I want to set one of the package variables dynamically, but I can't set it within the package itself as it's used in another sql agent job.

    Your script there looks a little easier so I'll give that a go. Will have to wait until monday though to get an answer from my manager to see if I'm allowed to activate xp_cmdshell.

    Will let you know how I get on...

    LVL 2

    Expert Comment

    No prob.
    The script should work when you enable xp_cmdshell.
    let me know.
    LVL 39

    Accepted Solution

    I suggest put an extra step  to update the XML config file in the same SQL job that executes the SSIS and just before the SSIS package execution.
    LVL 2

    Assisted Solution

    That's correct. A seperate SSIS package with a SQL task that call the script or SP. and make that the first step in the Job.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
    In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
    Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    7 Experts available now in Live!

    Get 1:1 Help Now