FTP Task in a SQL Server Agent Scheduled Job - Corrupts Excel file?

We are not certain if this has happened due to the SSIS FTP Task, but incidently the Excel file that is being copied from the FTP site using an SSIS FTP Task got corrupted a couple hours after the package was scheduled as a SQL Server Agent Job on SQL Server 2005.

I had a SQL Server 2000 DTS package doing the same thing, but it was never an issue then. I was using the FTP Task there along with an Excel Data source in that and has been working for a couple years atleast with never any corruption related issues.

In the SSIS SQL Server 2005 package I am using an FTP Task with an Excel Connection Manager and Excel source and the Excel file got corrupted within a couple hours of the package being scheduled as a SQL Server Agent job.

Has anyone experienced this issue? Any inputs will be appreciated.

Just as an fyi, the excel file has a lot of vlookups.

Thanks,

MShah

The FTP task does not delete existing file when downloading new one with the same name (we have recently found it and planning to fix for SP2). So if the downloaded file is smaller than existing one, it overwrites the beginning of existing file with new content, but the remaining file content might be from previous file.

The workaround is to use File System Task to delete existing file before downloading new one.

FTP Delete task in SQL agent

I have a FTP task created using SSIS that need to delete lot of files in a directory. This task is working fine if I'm Executing the SSIS package manualy but not working if I'm scheduling the SSIS package in SQL agent job.
Anybody have faced this kind if situation is there any solution for this?.

have you gotten a resolution on this problem? I am having the same issue on a FTP send task and it works fine when I run it manually but it bombs out when I schedule it.

Let me know... Thanks

|||

Error messages really help in these situations guys.

-Jamie

|||

I actually found the answer for my problem here:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=356558&SiteID=1

I just made the protection level to "DontSaveSensitive" and when the SQL Server agent ran the package it ran properly.

Hope it helps :)

ftp command help

I need to schedule copying of files to an ftp share using a SQL agent
job..but i dont know how to do that in one step
Say for eg: the source of files are on \\computerA\share1\*.*
and the destination is an ftp site ftp.x.com with a username and password
And i want to run this job on say computerB.
Then what would be the command ?
ThanksYou can either
1) create a DTS package (FTP task) then schedule the job by using
'dtsrun' command in the job.
2) use native ftp cmds in the job (jobs steps in an order). e.g.
ftp ftp.x.com
cd (to the location)
put <file>
dir (check if the file there)
close (close the connection)
bye
Mel

Frequency Of SnapShot Agent

How often should the snapshot agent job run with merge replication? My
schema won't be changing anytime soon but I will have new subscribers coming
on everyday until we get everyone on board.
Thanks
Tina
Hello Tina,
The snapshot agent is required in case there are schema changes, but not
only. For example, cleaning up meta data using procedure
sp_mergecleanupmetadata requires the snapshot agent to be run (more in BOL).
It also creates the latest bcp files so if you apply the snapshot to new
subscribers they start off with the latest data.
I also think having read somewhere (but I am not too sure about it) whereby
the automatic cleanup of merge tracking meta data (normally stored in
Msmerge_Contents and Msmerge_Tombstone tables) depends on periodically
running the Snapshot agent. If someone can shed some more light on this...
In your case you might want to run the agent once a day, because you would
want each subscriber to be updated using the latest snapshot data. However,
you MUST be conscious that during the running of the snapshot agent,
conflict tables ARE DROPPED and RECREATED (I am told by Microsoft Support
this is by design). So, if you have concerns with conflicts, make sure you
resolve them before running the snapshot agent.
Raj Moloye
|||Raj,
Thanks for all your input.
Tina
"Raj Moloye" <rkmoloye@.hotmail.com> wrote in message
news:uCZy0DkHEHA.1220@.tk2msftngp13.phx.gbl...
> Hello Tina,
> The snapshot agent is required in case there are schema changes, but not
> only. For example, cleaning up meta data using procedure
> sp_mergecleanupmetadata requires the snapshot agent to be run (more in
BOL).
> It also creates the latest bcp files so if you apply the snapshot to new
> subscribers they start off with the latest data.
> I also think having read somewhere (but I am not too sure about it)
whereby
> the automatic cleanup of merge tracking meta data (normally stored in
> Msmerge_Contents and Msmerge_Tombstone tables) depends on periodically
> running the Snapshot agent. If someone can shed some more light on this...
> In your case you might want to run the agent once a day, because you would
> want each subscriber to be updated using the latest snapshot data.
However,
> you MUST be conscious that during the running of the snapshot agent,
> conflict tables ARE DROPPED and RECREATED (I am told by Microsoft Support
> this is by design). So, if you have concerns with conflicts, make sure you
> resolve them before running the snapshot agent.
> Raj Moloye
>