x
login about faq Site discussion (meta-askssc)

SSIS Script Task to change encoding

I am want to take a CSV output (which is UTF-16) and converting it using a script task. The reason is that I need to send the file to 3 different customers/suppliers that have different encoding.

I have the following .NET code but it keep throwing amd error.

Error: Failed to lock variable "C:\EXPORT.csv" for read/write access with error 0xC0010001 "The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.".

Imports System
Imports System.Data
Imports System.Math
Imports System.Text
Imports System.IO
Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain

    ' The execution engine calls this method when the task executes.
    ' To access the object model, use the Dts object. Connections, variables, events,
    ' and logging features are available as static members of the Dts class.
    ' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
    ' 
    ' To open Code and Text Editor Help, press F1.
    ' To open Object Browser, press Ctrl+Alt+J.

    Public Sub Main()
    	'
    	' Add your code here
        '
        Dim inputFileName As String = Convert.ToString(Dts.Variables("InputFileName"))
        Dim outputFileName As String = Convert.ToString(Dts.Variables("OutputFileName"))

        If Not File.Exists(inputFileName) Then
            Throw New Exception("The input file could not be found.")
        End If

        ConvertFile(inputFileName, Encoding.Unicode, outputFileName, Encoding.UTF8)

        Dts.TaskResult = Dts.Results.Success
    End Sub

    Private Sub ConvertFile(ByVal inputFileName As String, ByVal inputEncoding As Encoding, ByVal outputFileName As String, ByVal outputEncoding As Encoding)
        Using reader As New StreamReader(inputFileName, inputEncoding)
            Using writer As New StreamWriter(outputFileName, False, outputEncoding)
                Dim line As String
                While (line = reader.ReadLine()) <> Nothing
                    writer.WriteLine(line)
                End While
                writer.Flush()
                writer.Close()
            End Using
        End Using
    End Sub
End Class
more ▼

asked Oct 22 '09 at 07:47 AM in Default

sp_lock gravatar image

sp_lock
8.1k 20 26 29

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

You code looks fine, but I have a theory. When you wanted to add the variable for read/write access, how did you add it?

If you directly add the variable to the list (on the Script page of the Task Editor), it should work fine (but see additional note below). If you added the variable as an expression for the ReadWriteVariables property, then the value of the variable is included in the list instead of the variable itself.

Additional Note: I believe you also need to change your code from

Convert.ToString(Dts.Variables("OutputFileName"))

to

Convert.ToString(Dts.Variables("OutputFileName").Value)

I haven't used VB.NET in awhile, but I know you have to do that with C#. Otherwise, you are trying to convert the Variable object itself.

more ▼

answered Oct 22 '09 at 03:07 PM

Tom Staab gravatar image

Tom Staab
5.8k 5 8 10

Tom.. I also made that that change, Thanks.

Took me 3 hours! Taking a break from a problem clears the mind and makes way for a "eureka" moment.

Thanks again

Oct 22 '09 at 04:32 PM sp_lock
(comments are locked)
10|1200 characters needed characters left

Sort this... I didnt defind the input variables correctly via the Task.

more ▼

answered Oct 22 '09 at 03:02 PM

sp_lock gravatar image

sp_lock
8.1k 20 26 29

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments



Facebook logo Follow Ask SSC on Facebook
Find Ask SSC on Google+
linkedin logo Find us on LinkedIn

Topics:

x749

asked: Oct 22 '09 at 07:47 AM

Seen: 2625 times

Last Updated: Oct 22 '09 at 07:47 AM

Copyright © 2002-2012 Simple Talk Publishing. All Rights Reserved. If you have any queries, please contact the site administrators.
Ask SQL Server Central is a community service provided by Red Gate.