October 8, 2012 at 12:55 am
Hey I am also working on same task like this In SSIS ftp task...
Here I created a variable for getting dynamic file name path for
"LOCAL PATH"..
@[User::LDirectoy] = "E:\LocalFilefolder\"
@[User::LZipFilePrefix] = "KK_"
@[User::LZipFileEndExtension] = ".rar"
@[User::LDirectoy]+ @[User::LZipFilePrefix] + (DT_STR, 4, 1252) DAY( DATEADD("dd", -1, GETDATE() ) )+"-"+RIGHT("0"+(DT_STR, 4, 1252) MONTH( GETDATE() ),2)+"-"+(DT_STR, 4, 1252) YEAR( GETDATE() )+ @[User::LZipFileEndExtension]
op: for this expression.." E:LocalFilefolder/KK_26-09-2012.rar"
while debugging i am getting
Error: Failed to lock variable "E:\LocalFilefolder\KK_26-09-2012.rar" for read 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.".
Any help will be apreciated.......
October 8, 2012 at 8:05 am
Is the variable in a scope that can be used by your task?
October 8, 2012 at 10:35 pm
These variables are created at package scope...
I hope this might correct as of your concern..
other than this anything i can do please let me know.
October 9, 2012 at 6:07 am
What if you set the variable to Evalute as expession?
Other wise, is the file being brought over from the ftp? if so, rename it once its your side?
Ian Cockcroft
MCITP BI Specialist
October 10, 2012 at 2:54 pm
If you are using ssis 2008, you can set up variables read/write in a vb script task.
Easy to modify, just set-up the variables you will be reading and writing from and modify them in the script.
Should be easy to figure out...
' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
<System.AddIn.AddIn("ScriptMain", Version:="1.0", Publisher:="", Description:="")> _
<System.CLSCompliantAttribute(False)> _
Partial Public Class ScriptMain
Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
Enum ScriptResults
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
End Enum
' 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()
'Assume success
Dts.TaskResult = ScriptResults.Success
Dim FileName As String
Dim Path As String
Dim FTP_User As String
Path = CType(ReadVariable("Data_Location"), String)
FTP_User = CType(ReadVariable("FTP_UserName"), String)
''FileName = "/" & FTP_User & "/" & Replace(CType(ReadVariable("FileName"), String), Path, "")
FileName = "/ftp_root/" & FTP_User & "/"
WriteVariable("Admin", FileName)
End Sub
Private Function ReadVariable(ByVal varName As String) As Object
Dim result As Object
Try
Dim vars As Variables
Dts.VariableDispenser.LockForRead(varName)
Dts.VariableDispenser.GetVariables(vars)
Try
result = vars(varName).Value
Catch ex As Exception
Throw ex
Finally
vars.Unlock()
End Try
Catch ex As Exception
Throw ex
End Try
Return result
End Function
Private Sub WriteVariable(ByVal varName As String, ByVal varValue As Object)
Try
Dim vars As Variables
Dts.VariableDispenser.LockForWrite(varName)
Dts.VariableDispenser.GetVariables(vars)
Try
vars(varName).Value = varValue
Catch ex As Exception
Throw ex
Finally
vars.Unlock()
End Try
Catch ex As Exception
Throw ex
End Try
End Sub
End Class
October 10, 2012 at 3:06 pm
Sorry had to add my two cents about using expressions with Date included in the file name.
This is a bad idea if you will be running the package around midnight and the date change will screw up the file name, especially if you have steps later on like archiving or deleting using that expression.
Found this in a package a few weeks ago, I hate expressions as they are very cumbersome to maintain.
(Note I did not write the package, work in a shop where there are SSIS packages written by numerous SQL developers, past and present)
I suppose they have uses but not in my world.
October 11, 2012 at 4:39 am
churlbut (10/10/2012)
Sorry had to add my two cents about using expressions with Date included in the file name.This is a bad idea if you will be running the package around midnight and the date change will screw up the file name, especially if you have steps later on like archiving or deleting using that expression.
That is easily dealt with
get the date at the start of the process, store it in a variable and always use the stored value. Do not get it multiple times throughout the process.
That way you do not get any issues with dateparts changing whilst you are building up the string.
i.e. use
@DATETOWORKWITH=GETDATE()
do something with @DATETOWORKWITH
do something else with @DATETOWORKWITH
do yet another thing with @DATETOWORKWITH
and NOT
do something with GETDATE()
do somethingelse with GETDATE()
do yetanother thing with GETDATE()
October 12, 2012 at 6:58 am
Thank you ........
I will give a shot to my flow using this script
hope it works...........:-):-)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply