February 24, 2011 at 1:55 am
Hi all
Tried to find a similar situation to avoid double posting, but found none. If there is, please accept my apologies.
So here's my story.
I want to implement a custom DR process (due to constraints) based on backup files being copied to the offsite location using an FTP task. I built the process similar to this:
1. Backup takes place on each server (5 of them in the process)
2. SSIS package is kicked off as a job
3. SSIS package builds a list of backup files needed to be FTPd based on a table containing the settings (db name, bak file name, ftp folder, backup folder). These settings are also sent to the remote DR server (as a text file), to know where to restore the copied databases.
4. Those are saved into a variable in SSIS as a recordset
5. In the ForEach container I am shredding that recordset built at 4) and for each record found in there, I am trying to alter on-the-fly the required FTP Task properties (source file and remote folder) in an attempt to FTP the file (bak file) to the remote folder (ftp folder).
6. On the other side, another SSIS is periodically scanning the target folder and if a settings file is found, is processed and databases included in that file are restored according to the passed parameters.
But, to my desperation, no matter what I do, I keep on getting this error:
Error at DRCopy: The connection "" is not found. This error is thrown by Connections collection when the specific connection element is not found.
Error at FTP Task [FTP Task]: Connection manager "" can not be found.
Error at FTP Task: There were errors during task validation.
Few screenshots with details from the SSIS package, here or in the attached ZIP file. I remember I found something related to limitations on using expressions on FTP tasks inside a ForEach container, but not able to find it again.
I tested everything without FTP, by manually copying the files over and the process works as I expected. Except for FTP.
Any ideas / thoughts, greatly appreciated.
February 24, 2011 at 3:31 am
Hi
The problem is only with your FTP Task.So first you try to execute only the FTP task .Crete a FTP connection ,set the localpath to a hardcoded value like "D:\Test\" instead of variable ,also set the remort path hardcoded
.Then try to execute the FTP task .If it is copyng the file from ur source folder to destination successfully,You can go for variable instead of hardcoded value .You can also check the value of the variable by using breakpoint and local window.
Hope this is gonna help you
Incase any query let me know
Thanks & Regards
Rashmi
February 24, 2011 at 3:41 am
Try setting the property DelayValidation to TRUE on the FTP task.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 24, 2011 at 5:26 am
Thank you, guys.
The DelayValidation did the trick. I don't know how I overlooked it. Thanks again.
But.... 🙂
Still got errors. When I assign the local path to the LocalPath property, I got an error stating the connection "E:\DBBackups\TEST.BAK' does not exists.
Do I need to do it in other ways, am I doing something wrong ? On all online resources I came across, all stated that by changing this property using expressions, you can implement a dynamic connection for an FTP task.
Any thoughts on this second issue, greatly appreciated.
TIA
Regards,
Alin
February 24, 2011 at 5:53 am
alin.selicean (2/24/2011)
Still got errors. When I assign the local path to the LocalPath property, I got an error stating the connection "E:\DBBackups\TEST.BAK' does not exists.Do I need to do it in other ways, am I doing something wrong ? On all online resources I came across, all stated that by changing this property using expressions, you can implement a dynamic connection for an FTP task.
Does the SSIS package have access to that directory?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 24, 2011 at 5:58 am
Yes, it does. All SQL Services run as a local user account with read privilleges on the local folder. I have already checked that. I'm stuck...
February 24, 2011 at 6:20 am
alin.selicean (2/24/2011)
Yes, it does. All SQL Services run as a local user account with read privilleges on the local folder. I have already checked that. I'm stuck...
What if we take a little detour?
Create a file connection manager and configure that one dynamically. Then point your ftp task to that File connection manager.
Let's see if that works...
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 24, 2011 at 6:25 am
Working on that... Will keep you all posted. Thank you, all, so far.
May 4, 2011 at 10:27 am
I was having a very similar problem where I was setting the LocalPath using an expression, and it would say that it could not find the file even though the file was there. I removed the expression and made the local path point to a new file connection that used that flat file connection manager with an expression to dynamically change the connection string in order to select the proper file, and now it works.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply