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, 2009 at 07:47 AM in Default

avatar image

sp_lock
10.5k 27 37 37

(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, 2009 at 03:07 PM

avatar image

Tom Staab ♦
14.5k 7 14 18

(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, 2009 at 03:02 PM

avatar image

sp_lock
10.5k 27 37 37

(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

SQL Server Central

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

Topics:

x1140

asked: Oct 22, 2009 at 07:47 AM

Seen: 4557 times

Last Updated: Oct 22, 2009 at 07:47 AM

Copyright 2016 Redgate Software. Privacy Policy