Learn how to a build a cloud-first strategyRegister Now


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

Posted on 2014-08-05
Medium Priority
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
  • 2
LVL 25

Assisted Solution

by:Ron Malmstead
Ron Malmstead earned 1000 total points
ID: 40241912
            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 36

Accepted Solution

ste5an earned 1000 total points
ID: 40242010
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="http://www.w3.org/1999/XSL/Transform">
<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 66

Assisted Solution

by:Jim Horn
Jim Horn earned 0 total points
ID: 40242032
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 66

Author Comment

by:Jim Horn
ID: 40242321
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
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.
Suggested Courses

810 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