SQL Server Performance

Script task Locking

Discussion in 'SQL Server 2005 Integration Services' started by danny123, Aug 5, 2008.

  1. danny123 New Member

    Hi there,
    In my SSIS package i am renaming the file using script task. The path for the file location is stroed in package variables. In the code section i am using "LockOneForWrite" to access the variable. And after my processing i use UNLOCK function.
    But in next step where it insert the data in the file its failing with the error specifing cant access the file and file is used by some other process.
    Please advise.
    Thanks!!
  2. satya Moderator

    What kind of file is that?
  3. danny123 New Member

    Thanks Satya for the support.
    This is a csv file and here is the code i am using to create/rename it.
    First it checks if the file with yesterdays date is there, if yes then it renames the file to todays date. and if not there then it creates a new file.
    Imports System
    Imports System.Data
    Imports System.Math
    Imports Microsoft.SqlServer.Dts.Runtime
    Imports System.IO
    Public Class ScriptMain
    Public Sub Main()
    Dim srcFile1, dstFile1 As String
    Dim var1 As Variables

    Dts.VariableDispenser.LockOneForWrite("Path1", var1)

    srcFile1 = var1("Path1").Value.ToString() & Date.Now.AddDays(-1).ToShortDateString().Replace("/", "") & ".csv"
    dstFile1 = var1("Path1").Value.ToString() & Date.Now.Date.ToShortDateString().Replace("/", "") & ".csv"
    If File.Exists(srcFile1) Then
    File.Copy(srcFile1, dstFile1, True)
    File.Delete(srcFile1)
    Else
    File.Create(dstFile1)
    End If
    var1.Unlock()
    Dts.TaskResult = Dts.Results.Success
    End Sub
    End Class
    Thanks!
  4. satya Moderator

  5. danny123 New Member

    Allright this is what i did to make it work.
    First which i did not know was while specifing the file destination is Flatfile task its not necessary to have that file already created just specify the name and file will create itself on run time so i took the script task out of package.
    Now file was created but the send mail object was not able to grab it to mail because the file created in previous step was still locked and send mail task was not able to attach/access it. which i think that the files created in a package is accessible only when the whole package is finished successfully.
    So i created another package which will send out the email with those file as attachment and called that package in my package.
    This worked perfectly fine to me.
    Now can you please verify whether its true or not that i cant access the files created in same package untill the whole package is finished running.
    Thanks!
  6. satya Moderator

    We haven't implemented such a process, but it sounds like only workaround due to the nature of process within SSIS that performs and step 1 creates file means unless the package execution is complete you won't be able to get locks out, so your process looks fine to me.
  7. danny123 New Member

    Thanks Satya for support!

Share This Page