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!!
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!
I think processmonitor http://technet.microsoft.com/en-us/sysinternals/bb896645.aspx is a good one to identify which is causing this issue.
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!
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.