CODEWITHSUNDEEP
vb.netssisvisual-studio-2005dsnsystem.io.file
JeffK627
JeffK627

Reputation: 305

SSIS Script Task System.IO.File.WriteAllText throws "The process cannot access the file" Error

I have been tasked with writing a SSIS package that automates the creation of a SQL Server database. During the process it needs to create a DSN file (text file) so that an Access tool can connect to the SQL Server database. This is all legacy, I cannot change the requirements. I also can't change the fact that I'm working in Visual Studio 2005 using VB.NET.

The problem is, when I try to write to the DSN file I get an error: "The process cannot access the file '\Acmshares2\clntrial\DataMgt\DM_DSNs\DM_C001.dsn' because it is being used by another process." The file is being created, but I can't write to it. It's as if the line that creates the file is "holding on" to it somehow. Here's the code - note that the error gets thrown whether the line System.IO.File.OpenWrite(sFilePath) is there or not:

Public Sub Main()
    Dim sStudyID As String
    Dim sText As String
    Dim sFileName As String
    Dim sFilePath As String

    If Dts.Variables.Contains("StudyID") = True Then
        sStudyID = CType(Dts.Variables("StudyID").Value, String)
    End If

    sText = "[ODBC]" & vbCrLf
    sText = sText & "DRIVER=SQL Server" & vbCrLf
    sText = sText & "UID=DM" & vbCrLf
    sText = sText & "DATABASE=DM_" & sStudyID & vbCrLf
    sText = sText & "WSID=ACMDM" & vbCrLf
    sText = sText & "APP=Microsoft Data Access Components" & vbCrLf
    sText = sText & "SERVER=ACMDM" & vbCrLf

    sFileName = "DM_" & sStudyID & ".dsn"
    sFilePath = "\\Acmshares2\clntrial\DataMgt\DM_DSNs\" & sFileName

    If Not System.IO.File.Exists(sFilePath) Then
        System.IO.File.Create(sFilePath)
    End If
    System.IO.File.OpenWrite(sFilePath)
    System.IO.File.WriteAllText(sFilePath, sText)

    Dts.TaskResult = Dts.Results.Success
End Sub

Any help will be much appreciated!

Upvotes: 1

Views: 890

Answers (1)

theduck
theduck

Reputation: 2617

WriteAllText() creates a new file or overwrites any existing file so you don't need the Create() or OpenWrite() calls.

Upvotes: 1

Related Questions