0

I am new with SSIS and struggling with Script task. Trying to figure out how to build script for following scenario.

Here is scenario

I have XML file has following data

<?xml version="1.0" encoding="utf-8"?> 
<environments>   
<environment>  
<name>DEV3WEB</name> 
<databaseservername>DEV3DB.apc.com</databaseservername
<webservername>DEV3WEB.abc.com/</webservername>
<appservername>DEV3APP.abc.com</appservername>
</environment>
<environment>   
<name>DEV3APP</name>
<databaseservername>DEV3DB.abc.com</databaseservername
<webservername>DEV3WEB.abc.com/</webservername
<appservername>DEV3APP.abc.com</appservername>
</environment>
</environments>

**I want script task read 'Name' from XML file - match with System variable 'Machine Name' and write output to user variable 'ServerNumber'.

If machine name is DEV3WEB - output will be number '1' and DEV3APP output will be number '2'.**

Thanks in advance.

Yahfoufi
  • 2,220
  • 1
  • 22
  • 41
M. Mehta
  • 63
  • 3
  • 10

1 Answers1

0

You can read the information like this:

  • first create a CTE with all <environment>-nodes
  • Use the derived table in a simple SELECT with a normal WHERE-clause

Try it like this:

DECLARE @xml XML='<environments>
  <environment>
    <name>DEV3WEB</name>
    <databaseservername>DEV3DB.apc.com</databaseservername>
    <webservername>DEV3WEB.abc.com/</webservername>
    <appservername>DEV3APP.abc.com</appservername>
  </environment>
  <environment>
    <name>DEV3APP</name>
    <databaseservername>DEV3DB.abc.com</databaseservername>
    <webservername>DEV3WEB.abc.com/</webservername>
    <appservername>DEV3APP.abc.com</appservername>
  </environment>
</environments>';

DECLARE @MachineName NVARCHAR(MAX)=N'DEV3APP';
WITH AllEnvironments AS
(
    select env.value('name[1]','nvarchar(max)') AS Environment_Name
          ,env.value('databaseservername[1]','nvarchar(max)') AS Environment_DatabaseServername
          ,env.value('webservername[1]','nvarchar(max)') AS Environment_WebServername
          ,env.value('appservername[1]','nvarchar(max)') AS Environment_AppServername
    FROM @xml.nodes(N'environments/environment') AS A(env)
)
SELECT *
FROM AllEnvironments
WHERE Environment_Name=@MachineName;

With bigger XML it would be faster to set the filter into XQuery to reduce the workload there (Look at the .nodes()-function):

DECLARE @MachineName NVARCHAR(MAX)=N'DEV3APP';
SELECT   env.value('name[1]','nvarchar(max)') AS Environment_Name
        ,env.value('databaseservername[1]','nvarchar(max)') AS Environment_DatabaseServername
        ,env.value('webservername[1]','nvarchar(max)') AS Environment_WebServername
        ,env.value('appservername[1]','nvarchar(max)') AS Environment_AppServername
FROM @xml.nodes(N'environments/environment[name=sql:variable("@MachineName")]') AS A(env)
Shnugo
  • 66,100
  • 9
  • 53
  • 114
  • Hey, Thanks for your help and interest. Situation is I don't have SQL installed in Web and App server. I have to deploy package remotely. Is it possible to work with Script task in SSIS? – M. Mehta Oct 31 '16 at 15:04
  • @M.Mehta, sorry. don't know this... No experience in this area... Good luck! – Shnugo Oct 31 '16 at 15:08