SSIS 2012 Script Task:  How to add text to a file before working with it (prefer VB)

Posted on 2014-08-05
Last Modified: 2016-02-11
Hi all

Question:  How in SSIS 2012 can I take a file such as the below code, insert the string<?xml version="1.0"?> in the beginning, then close it?  

Connections are fine.  Would prefer a VB script solution, but C# is fine.

    <Compute Timestamp="2014-06-30 09:09:00">
        <Fuels Unit="LBS">
                <Origin Currency="USD" Quantity="USG">3.11</Origin>
                <Destination Currency="USD" Quantity="USG">4.00</Destination>
                <Savings Currency="USD">2098.98</Savings>
        <Dispatcher>JOHNNY ROCKET</Dispatcher>

Open in new window

Question by:Jim Horn
    LVL 25

    Assisted Solution

    by:Ron M
                Dim filecontents As String = "<?xml version=" & """" & "1.0" & """" & "?>" & vbCrLf
                filecontents = filecontents & FileIO.FileSystem.ReadAllText("c:\test.txt").ToString

                FileIO.FileSystem.WriteAllText("c:\test.txt", filecontents, False)
            Catch ex As Exception
            End Try
    LVL 32

    Accepted Solution

    Why? The XML declaration is optional.

    When it's really necessary I would use a XSLT to transform it, e.g. with MSXML on the command line or a XML Task.

    <?xml version="1.0" encoding="UTF-8"?>
    <xsl:stylesheet version="1.0" xmlns:xsl="">
    <xsl:output method="xml" indent="yes" omit-xml-declaration="no" />
      <xsl:template match="@*|*|processing-instruction()|comment()">
          <xsl:apply-templates select="*|@*|text()|processing-instruction()|comment()"/>

    Open in new window

    LVL 65

    Assisted Solution

    by:Jim Horn
    I've been reading that it's optional, but when I use it in a Data Source Task I get a 'Data at the root level is invalid' error message, and the quick workaround I discovered is to manually open it up and paste <?xml version="1.0"?> at the beginning of the file.  That question is here.

    Unless there's a property somewhere that I'm not seeing...
    LVL 65

    Author Comment

    by:Jim Horn
    Hmm.  Now it's working, and I don't know why, as the only changes where moving the root folder, and changing the XSD location to be parameter-driven instead of hard-coded.

    Thanks for the help

    Featured Post

    PRTG Network Monitor: Intuitive Network Monitoring

    Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

    Join & Write a Comment

    Lessons learned during ten years of interviewing for SQL Server Integration Services (SSIS) and other Extract-Transform-Load (ETL) contract roles and two years of staff manager interviewing contractors.
    My client sends data in an Excel file to me to load them into Staging database. The file contains many sheets that they have same structure. In this article, I would like to share the simple way to load data of multiple sheets by using SSIS.
    Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
    Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now