Using Standalone SQL Server Integration Service (SSIS) DevOps Tools to build SSIS project

Question

Hi,

We've created SSIS packages with package parameters containing Oracle PL-sql queries, which are used in SSIS task expression. All packages can be built and executed within Visual Studio 2019.
To deploy, we use Standalone SQL Server Integration Service (SSIS) DevOps Tools version 1.0.0.0 - SSISBuild.Exe to build the SSIS project, but we are getting XML parsing issues as below.
'System.Xml.XmlException: Name cannot begin with the ‘=’ character.
This issue relates to a package parameter containing a pl-sql query as mentioned above.

<DTS:PackageParameter
DTS:CreationName=“”
DTS:DataType=“8”
DTS:DTSID=“{008B2843-5381-4B0A-81B4-BAB517AABE18}”
DTS:ObjectName=“ExtractSQL”>
<DTS:Property
DTS:DataType=“8”
DTS:Name=“ParameterValue”>
Select a.clmn1
FROM Schema.tablename a
WHERE DAYKEY >= (SELECT TRUNC (SYSDATE) - TO_DATE (‘01/01/1980’, ‘mm/dd/yyyy’) - 4 FROM DUAL)
and rownum <= 100 ORDER BY a.Id, a.FileRecNo, a.DayKey, a.Position
</DTS:Property>
</DTS:PackageParameter>

The XML parser is complaining with ‘rownum <= 100’ (translated into rownum '&lt= 100' within XML), where it’s erroring because of less than symbol in the query. The package builds fine if ‘<’ get removed.

By the way, we’re not using DevOps pipeline; instead, we use ‘.bat’ file to build SSIS packages and deploy them. And here is the syntax of the code used within the batch file.

SSISBuild.exe -project|-p:<dtproj file path> [-configuration|-c:<configuration name>] [-projectPassword|-pp:<project password>] [-stripSensitive|-ss] [-output|-o:<output path>] [-log|-l:<log level>[;<log path>]] [-quiet|-q] [-help|-h|-?]

Could you shed some light on why SSISbuild is complaining about a regular SQL statement?

Softwares and tools used in the VM:
Visual Studio 2019
SQL Server 2019
SQL Server Integration Services 15.0
Microsoft Connector for Oracle (SSIS Oracle connector) 15.0

Microsoft resource:
https://learn.microsoft.com/en-us/sql/integration-services/devops/ssis-devops-standalone?view=sql-server-ver16

Many thanks,

Answer 1

Hi @Raj T ,

You encountered an issue with one of the five XML entities. The "<" is one of them.

Please try the following approach in VS2019 first. It is based on use of the CData section.

Replace the SSIS package parameter value as follows, and test it to see if it is working:

<![CDATA[Select a.clmn1  
FROM Schema.tablename a  
WHERE DAYKEY >= (SELECT TRUNC (SYSDATE) - TO_DATE (‘01/01/1980’, ‘mm/dd/yyyy’) - 4 FROM DUAL)  
and rownum <= 100 ORDER BY a.Id, a.FileRecNo, a.DayKey, a.Position]]>