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!
GrffsterAsked:
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.
0
 
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.

http://msdn.microsoft.com/en-us/library/ms345137(v=sql.90).aspx
0
 
lcohanDatabase AnalystCommented:
Here's an example how to use an SP to send the results into an XML file:

http://www.sitepoint.com/forums/showthread.php?633391-Output-xml-file-from-MS-SQL-Server-2005-stored-procedure
0
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.

 
Frank_BaninCommented:
have you resolve your issue yet?




0
 
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?
0
 
Frank_BaninCommented:
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.

DECLARE
@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=
N'

Create TABLE ConfigXMLTable(
             XMLCol XML
        );
        
INSERT INTO ConfigXMLTable(XMLCol)
SELECT
   xCol
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];'


Exec(@sql);
0
 
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...

Thanks.
0
 
Frank_BaninCommented:
No prob.
The script should work when you enable xp_cmdshell.
let me know.
0
 
Frank_BaninCommented:
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.
0
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.