October 3, 2008 at 2:57 pm
Hey Guys
I have finally completed my SSIS Task, does anyone know how I can upload it to the Forum,
All the issues that I had developing the package has been solved
Regards,
Wilbur
November 8, 2008 at 9:19 pm
I am running into the same problem but I am not understanding your solution.
I am using the File System Task. I have three variables; Source, Destination and FileName. In my expression, I have @destination + @filename + ((DT_STR,2,1252) DATEPART("MM",GETDATE())) + ((DT_STR,2,1252) DATEPART("DD",GETDATE())) + ((DT_STR,4,1252) DATEPART("yyyy",GETDATE())) +".txt".
I have done all that has been suggested, set the evaluateexpression property to True but I still cannot get passed this error - failed to lock variable. This seems too easy to be causing so many issues. Any suggestions please let me know.
November 10, 2008 at 9:10 am
as I mentioned above
"The values that show in the properties are normally the values that exist based on the debugging environment and can differ based on how you're actually calling the package. That said you should try to see what the values are at run time.
For me I check variable by putting a script task right before subsequent tasks (FTP Task in your case) and in that Script Task, I pull the variable in and use the tried and true MsgBox(Variable) name approach.
'i.e. Put Pull the variable into the script task
Dim vars As Variables
Dts.VariableDispenser.LockOneForRead("User::VariableName", vars)
Dim variable As String = vars("VariableName").Value.ToString()
vars.Unlock()
Then
MsgBox(Variable)"
Try this to see what your variable values actually are.
If you've set them manually sometimes they are not properly over ridden by the expression you've built.
October 22, 2009 at 11:28 am
How were you able to solve the issue of the FTP task failing due to no files being on the FTP server to download? I have this same issue and would like the task to complete successfully even if there arn't files to process, as this would be a normal occurence! Thanks so much for any help you can provide me!
October 22, 2009 at 2:06 pm
I used a script task, then placed some VB Code which would connect to the FTP site and check if the file existed, if the file did exists, I would proceed or else quit
I will try to upload the whole package online,
October 25, 2009 at 6:31 pm
Did you have a chance to upload this package? I'd love to see how you did this, especially your script! Let me know!
April 29, 2010 at 12:38 pm
Actually, I just had the same thing and intuitively set the DelayValidation to True, so it would stop choking on the file that doesn't exist yet when the package is started...and voila!
July 27, 2010 at 8:13 am
I am trying the same thing. I have the FTP task set to use variable for the local file, then created a variable named FullFilePath set to evaluate as expression true and then the expression written
"\\\\Europe\\fieldpub\\FMFTP\\PM Check In-Out-"+ (DT_STR, 2, 1252) Month( @[System::StartTime] )+ (DT_STR, 2, 1252) Day( @[System::StartTime] )+(DT_STR, 4, 1252) Year( @[System::StartTime]) + "-" +substring((DT_WSTR,30)GETDATE(), 12,2)+ "-" +substring((DT_WSTR,30)GETDATE(), 15,2)+".csv"
When I click the evaluate expression it gives me \\Europe\fieldpub\FMFTP\PM Check In-Out-7272010-08-32.csv which is what matches the file that my data flow created.
Error at FTP Task [FTP Task]: The remote path is empty and when I look at the output, it further states: Error: 0xC002917C at FTP Task, FTP Task: The variable "User::FullFilePath" doesn't contain file path(s).
So I am totally stuck here. I hope someone has a suggestion.
Thanks!
July 27, 2010 at 9:25 am
I don't know if this will help, but I code the path with one variable and the file name with another and then combine them at the end. The first is straightforward; the second is so.
(DT_STR,4,1252)YEAR(@[User::StartDate])+
RIGHT("0"+(DT_STR,2,1252)MONTH(@[User::StartDate]), 2)+
RIGHT("0"+(DT_STR,2,1252)DAY(@[User::StartDate]) , 2)+ ".txt"
Today this produces 20100727.txt. This looks similar to what you have but I'm not using the substring function.
July 27, 2010 at 9:41 am
Are you actually creating the file with content before trying to FTP?
You may want to disable the FTP step, and verify that the file is being created.
My other guess would be to verify that the FTP Task can handle the server path...this could get tricky. Depending on how the package is run, and the permissions of the account that runs the job on the final production destination, you will need to make sure the \\server\path\file naming convention will resolve properly. So, you may want to start with a local reference if you can, like D:\export\testfile.txt.
Just some thoughts from an SSIS newb.
Adam
July 27, 2010 at 9:46 am
Yes, I have verified that the file is created. and I have also removed the variable reference and pasted the actual path into the ftp, run it manually and verified that it finds and copies the file onto the ftp server.
July 27, 2010 at 12:12 pm
Here is what I've ended up doing.
set the Is Local Path Variable back to false, created a file connection to the dummy file in the folder as my local path and set the name in the file connection to the expression.
That, believe it or not, is working. I have browsed to the FTP site and verified that my newly created file is there.
I'm sure there is a way to make that work like it should, but all my searching the web has not found the step-by-step to make it work. I only find questions like the one here with no solution.
Now to make the file system task work like it's supposed to and move the file to a processed folder.
Thanks everyone!
November 1, 2011 at 2:32 pm
My SSIS package was working perfectly. I populated the FTP Local Path via a variable FTPLocalPath that was an expression "@[User::ArchiveSourcePath] + @[User::RootFileName] + @[User::FileDateStamp]". Each of those variables are succesfully populating from a Table via an Execute SQL Task. I created a Script Task that shows the value for each of the four variables one at a time from source path through the full local path, and it's clearly correct.
It worked manually from my desktop and my coworkers desktop via BIDS no problem. Then one time I tried to manually start it from a job in SSMS (unsuccessfully). Since then it won't work anymore from any context. The message boxes show clearly the variables are being populated correctly, but the FTP Task throws the error:
Error: 0xC002917C at FTPExctractToCCSF, FTP Task: The variable "User::FTPLocalPath" doesn't contain file path(s)
ABSOLUTELY NOTHING WAS CHANGED IN THE PACKAGE. It worked before and after the attempt to run it from a job it won't run in any context.
Anybody encounter this? Anybody solved this? I really don't want to use a script task to replace the FTP Task. I want the FTP Task to work as it should. I worked many, many hours just to get a successful package (executed it at least 12 times successfully), and then kaput!
November 1, 2011 at 2:40 pm
At what point is this error appearing? Is it during package execution, or when the package is firing up and validating?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
November 1, 2011 at 2:41 pm
Can you put a pre-execute breakpoint on the FTP task to check its properties at run-time?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
Viewing 15 posts - 16 through 30 (of 39 total)
You must be logged in to reply to this topic. Login to reply