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

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!
Who is Participating?
lcohanDatabase AnalystCommented:
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.
lcohanDatabase AnalystCommented:
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.
lcohanDatabase AnalystCommented:
Here's an example how to use an SP to send the results into an XML file:
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

have you resolve your issue yet?

GrffsterAuthor Commented:
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?
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
  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+'"

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

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

GrffsterAuthor Commented:
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...

No prob.
The script should work when you enable xp_cmdshell.
let me know.
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.