• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 231
  • Last Modified:

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!
0
Grffster
Asked:
Grffster
  • 4
  • 3
  • 2
2 Solutions
 
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
 
Frank_BaninCommented:
have you resolve your issue yet?




0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
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
 
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
 
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

Featured Post

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

  • 4
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now