x

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
9k 24 27 30

(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 6 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
9k 24 27 30

(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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x900

asked: Oct 22 '09 at 07:47 AM

Seen: 3291 times

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