ftp task to network drive?

Hi I have a simple ftp task that downloads some files from an ftp to a local directory. That package run as a sheduled job from the sql 2005 server without a problem so far.

Now I whant to have the same task but to download the files to different mapped network drives , so I mapped the drives then I changed the paths to the config file but since then the job fails...

is it possible to do that or I did something wrong?

Thanks
g

Drive mappings are local to the user profile, so trying to use them in unattended jobs which run under a service account context does not make too much sense. I would strongly advise you to use a UNC path instead, e.g. \\Server\Share\Folder\File.ext

You will need top ensure that the service account or job proxy account has access to the share, as well as the normal folder permissions required for local as well.

|||Hi Darren,

Thanks a lot for the reply!!

I try the UCN path from the BI studio and it works fine but since I run it as a job faild. It must be something with the access to the share as you already said but since I am a newbie I don't know where exaclly to look for the problem... I am not running the job from a proxy do I have to create one? And if yes could you let me know for the process ?

As about the folder permissions I ve set full permission to "Everyone".

Thanks in advanced
G
|||

There is no need for a proxy account. By default your job will run under the context of the SQL Server Agent service account. Make sure you know what this is, and then check if that account has access to the share.

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 Task failure in SQL Job

I have a SQL IS package, with FTP task. It runs fine when I am running from the client. When deployed it on the server as a SQL Job, it failes with error message

"Unable to connect to FTP server using 'FTP Connection Manager' (Name I used for the FTP connection in IS package)".

I tried to run the .dtsx on the server connecting to Intergration services. And I get same error message with little more details, "Password was not allowed".

With same USER_ID and Password and default port number I am able to connect to the FTP site with no problems.

Do I need to do it differntly making the FTP connection inside the IS package? What's the correct way of doing it ?

satya

When you deploy your package to SQL Server make sure you use Protection Level: "Rely on server storage and roles for access control"

|||Thanks for responding to my questions. I did what you suggessted and am still having the same issue.

Steps I followed:

1. Copy the .dtsx to server location from my local.
2. Imported the package to SQL server location with the suggested options.

Do you think, I am missing any thing still ?|||

Yes.
It gets corrupted when you copy.

You can either:
1. Deploy without copy

or

1. Set protection level to EncryptSensitiveWithPassword
2. Copy
3. Imported the package to SQL server location with options I suggested before.

FTP job in I64 machine running Windows2003 and SQL2000(64bits)

I am in the process of moving our old SQLServer to a new I64 server.
Everything has been tested and working fine except there is a scheduled FTP
job task that didn't go through. I am hoping if I need to make changes to
accomodate the new I64 platform.
In the new SQL Server, the job task is the following:
ftp -s:"\\fileserverA\data\FTPScript.txt" 10.10.1.99
The content FTPScript.txt is as follows:
userid
userpassword
cd unixdatafolder
lcd D:\Data
put FileTransfer.txt
bye
After the job is run, the status shows successful but when I telnet to the
unix server, the file is not there. When I do ftp manually and locally in
the I64 server, it has no problem.
Thank you for your asssistance.
MartinHi Martin
If you change the FTP command for (say) an echo statement do you get output?
If so you may want to add the -d parameter to show the commands and capture
the output into a file to see what has happened. Also agent service account
has access to the source file, you may want to try using a cd command before
starting ftp to remove the need for the lcd subcommand.
John
"Martin" wrote:
> I am in the process of moving our old SQLServer to a new I64 server.
> Everything has been tested and working fine except there is a scheduled FTP
> job task that didn't go through. I am hoping if I need to make changes to
> accomodate the new I64 platform.
> In the new SQL Server, the job task is the following:
> ftp -s:"\\fileserverA\data\FTPScript.txt" 10.10.1.99
> The content FTPScript.txt is as follows:
> userid
> userpassword
> cd unixdatafolder
> lcd D:\Data
> put FileTransfer.txt
> bye
> After the job is run, the status shows successful but when I telnet to the
> unix server, the file is not there. When I do ftp manually and locally in
> the I64 server, it has no problem.
> Thank you for your asssistance.
> Martin|||John,
Thank you. The new 64bits machine did have some 'strange behavior'. I will
try using the echo as recommended and see if it makes the difference. I've
spent last two days trying to resolve this issue and after I sent the
question out, just by a random chance, I've found out that the 64bits Job
schedule won't take the url or mapped network drive. After I moved the file
to the local drive on the 64bits machine instead of using network drive other
than the local in the ftp scheduled job task statement, I got the file in
unix box after running the job.
Hope this information could save others time.
Sincerely,
Martin
"John Bell" wrote:
> Hi Martin
> If you change the FTP command for (say) an echo statement do you get output?
> If so you may want to add the -d parameter to show the commands and capture
> the output into a file to see what has happened. Also agent service account
> has access to the source file, you may want to try using a cd command before
> starting ftp to remove the need for the lcd subcommand.
> John
> "Martin" wrote:
> > I am in the process of moving our old SQLServer to a new I64 server.
> > Everything has been tested and working fine except there is a scheduled FTP
> > job task that didn't go through. I am hoping if I need to make changes to
> > accomodate the new I64 platform.
> >
> > In the new SQL Server, the job task is the following:
> >
> > ftp -s:"\\fileserverA\data\FTPScript.txt" 10.10.1.99
> >
> > The content FTPScript.txt is as follows:
> > userid
> > userpassword
> > cd unixdatafolder
> > lcd D:\Data
> > put FileTransfer.txt
> > bye
> >
> > After the job is run, the status shows successful but when I telnet to the
> > unix server, the file is not there. When I do ftp manually and locally in
> > the I64 server, it has no problem.
> >
> > Thank you for your asssistance.
> >
> > Martin|||Hi Martin
You may want to try http://www.sqldts.com/default.aspx?302 and see if it is
more reliable. I don't know if the wininet libraries are compatible with this
for I64.
John
"Martin" wrote:
> John,
> Thank you. The new 64bits machine did have some 'strange behavior'. I will
> try using the echo as recommended and see if it makes the difference. I've
> spent last two days trying to resolve this issue and after I sent the
> question out, just by a random chance, I've found out that the 64bits Job
> schedule won't take the url or mapped network drive. After I moved the file
> to the local drive on the 64bits machine instead of using network drive other
> than the local in the ftp scheduled job task statement, I got the file in
> unix box after running the job.
> Hope this information could save others time.
> Sincerely,
> Martin
> "John Bell" wrote:
> > Hi Martin
> >
> > If you change the FTP command for (say) an echo statement do you get output?
> > If so you may want to add the -d parameter to show the commands and capture
> > the output into a file to see what has happened. Also agent service account
> > has access to the source file, you may want to try using a cd command before
> > starting ftp to remove the need for the lcd subcommand.
> >
> > John
> >
> > "Martin" wrote:
> >
> > > I am in the process of moving our old SQLServer to a new I64 server.
> > > Everything has been tested and working fine except there is a scheduled FTP
> > > job task that didn't go through. I am hoping if I need to make changes to
> > > accomodate the new I64 platform.
> > >
> > > In the new SQL Server, the job task is the following:
> > >
> > > ftp -s:"\\fileserverA\data\FTPScript.txt" 10.10.1.99
> > >
> > > The content FTPScript.txt is as follows:
> > > userid
> > > userpassword
> > > cd unixdatafolder
> > > lcd D:\Data
> > > put FileTransfer.txt
> > > bye
> > >
> > > After the job is run, the status shows successful but when I telnet to the
> > > unix server, the file is not there. When I do ftp manually and locally in
> > > the I64 server, it has no problem.
> > >
> > > Thank you for your asssistance.
> > >
> > > Martin

FTP connection only works in BIDS

I have an FTP task that will only work when running in BIDS.

When trying to run as a package on the server or calling the package from a job, I get the following error in the log file:

OnError,<servername>,<user>,FTP Task,{B2F5BB68-C6F8-4EE5-ABC0-71C3636E3E4A},{B7B41A88-18DD-4AD7-8CDE-9E0C1B74DA02},6/26/2007 12:09:11 PM,6/26/2007 12:09:11 PM,-1073573489,0x,Unable to connect to FTP server using "FTP Connection Manager".

When running in BIDS it is fine.

Any know what is causing this?

Can you access the ftp site when RDP'd onto the server? My guess it is blocked.|||

we tried that

we used the old cmd file that was being called by the 'execute' task and it works fine on the server

can connect to ftp, can login, can transfer file, can rename

|||

When trying the command file, was that from a job -

- created in the same was as the SSIS job

- owned by the same SQL user

- and using the same proxy account if one is used at all?

Those points would effect the security context used to host the process within Windows, which in turn may impact credentials passed to the FTP service, or even just having network access or proxy access.

|||

It is because the security level was wiping the passwords when it was deployed.

We have now set it to use a password and it works ok.

|||

I am trying in vain to do the same. When deployed and ran the SSIS job it fails at the FTP Connection. Can you please sent me the format for FTP Connection used in SSIS jobs?

|||

Reji George wrote:

I am trying in vain to do the same. When deployed and ran the SSIS job it fails at the FTP Connection. Can you please sent me the format for FTP Connection used in SSIS jobs?

There really isn't a set "format." You just fill in the blanks for server, user name, password, etc...

Did you take note of the Package ProtectionLevel property message above?

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
>

freaking out job!

i have created a sql server job that i have scheduled to run every 10 min. in this job i have diferent steps wich seem to be working fine i also have 2 alerts that i have created for this job in case some steps fails so that i can be notififed via email with specific messages. the Weird thing is this job keeps re executing over and over again and never whaits for that 10 min delay and of course i get flooded with emails since i have set up test data to get these messages on purpose. so y doesnt wait 10 min like i have scheduled .PLZ HELP...Somewhere you are going into a loop ...
Check the Advanced properties for the job steps .. ?

Check on success , on faliure and retry attempts ... the error seems to be from somewhere there only.|||i have set up every step to go to step A and quit on failure on each of my steps and so on and the last one with quit on suceess and quit on failure ...its still looping HELP|||Originally posted by hillcat
i have set up every step to go to step A and quit on failure on each of my steps and so on and the last one with quit on suceess and quit on failure ...its still looping HELP

I always set it up as go to next step on success and quit on failure.Did you check the no of retry attempts also|||i have done what you said and all the retrys are at 0......?|||still doesnt work|||Hmmm .. that is wierd !!!

Will have to do some research here ... try to find the step after which the job re-executes

Can do so by ... changing the advanced option .. on success of each step one by one from the bottom upwards to quit the job returning success.|||Why don't you script the job abd post/attach it?|||wait a minute. Did you say you put every step to go to step A on success? So step 1 (on success) -> step 2 (on success) -> step 1?

Isn't this a perfect endless loop?|||Yup, I love those ;)|||BEGIN TRANSACTION
DECLARE @.JobID BINARY(16)
DECLARE @.ReturnCode INT
SELECT @.ReturnCode = 0
IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'Commander') < 1
EXECUTE msdb.dbo.sp_add_category @.name = N'Commander'

SELECT @.JobID = job_id
FROM msdb.dbo.sysjobs
WHERE (name = N'Test_Upload')
IF (@.JobID IS NOT NULL)
BEGIN

IF (EXISTS (SELECT *
FROM msdb.dbo.sysjobservers
WHERE (job_id = @.JobID) AND (server_id <> 0)))
BEGIN
-- There is, so abort the script
RAISERROR (N'Unable to import job ''Test_Upload'' since there is already a multi-server job with this name.', 16, 1)
GOTO QuitWithRollback
END
ELSE

EXECUTE msdb.dbo.sp_delete_job @.job_name = N'Test_Upload'
SELECT @.JobID = NULL
END

BEGIN

EXECUTE @.ReturnCode = msdb.dbo.sp_add_job @.job_id = @.JobID OUTPUT , @.job_name = N'Test_Upload', @.owner_login_name = N'sa', @.description = N'Execute package: Test_Upload Commandes', @.category_name = N'Commander', @.enabled = 1, @.notify_level_email = 2, @.notify_level_page = 0, @.notify_level_netsend = 0, @.notify_level_eventlog = 2, @.delete_level= 0, @.notify_email_operator_name = N'Email'
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback

EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobstep @.job_id = @.JobID, @.step_id = 1, @.step_name = N'Test_UpLoad_En cours', @.command = N'DTSRun /~Z0x3C2C60E6E69FDE4FD4E6E831CD32E2FC93EBDFD722093E B44F362DAB46FD1068596FBE179AAB0CBFC9253FBE160DD1AC 517927A330B272A8182D277B7FE8A0B25807CCA4CC03F11C88 59B5558AB6C1C60AB73140DD13B81B818692A28E23714096F3 C81B31D380168FE17CAB4BE11405820A0A411D90300F55E071 9CFF7A9D4B7BEA8A', @.database_name = N'', @.server = N'', @.database_user_name = N'', @.subsystem = N'CmdExec', @.cmdexec_success_code = 0, @.flags = 2, @.retry_attempts = 0, @.retry_interval = 1, @.output_file_name = N'c:\DTS\Logs\Test_UploadCommandesStep.log', @.on_success_step_id = 0, @.on_success_action = 3, @.on_fail_step_id = 0, @.on_fail_action = 2
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobstep @.job_id = @.JobID, @.step_id = 2, @.step_name = N'Test_Upload_Vrification', @.command = N'DTSRun /~Z0x77673A40FE51DCB3FDE10E99A1046569DE918CA37A09EA 1936C2699A12469122352F4D900875502D1ABCDEEB38E19B7A A4EEAEB430318EF008E74C940CEEDA61F3128D9C3658E57A07 692F84203B7CDE3FD074CAF73B5B7475F2A2DF33D3CDB9BA2B B1C7909B2656915BD9D8B5695DAD0A14723D9C40A502744A73 559B604CDCA4EAB538C7243875A187BA', @.database_name = N'', @.server = N'', @.database_user_name = N'', @.subsystem = N'CmdExec', @.cmdexec_success_code = 0, @.flags = 2, @.retry_attempts = 0, @.retry_interval = 1, @.output_file_name = N'c:\DTS\Logs\Test_UploadCommandesStep.log', @.on_success_step_id = 0, @.on_success_action = 3, @.on_fail_step_id = 0, @.on_fail_action = 2
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobstep @.job_id = @.JobID, @.step_id = 3, @.step_name = N'Test_Upload_Validation', @.command = N'IF (SELECT PC_TR_UPLOAD
FROM TEST_UPLOAD) = ''X''
BEGIN
PRINT ''Upload en cours dans Pc''
END
ELSE
IF (SELECT MAESTRO_TR_UPLOAD
FROM TEST_UPLOAD) = ''X''
BEGIN
RAISERROR(''*** Avertissement *** Upload en cours dans Maestro'', 16, 1)
END
ELSE
IF (SELECT COPY_TR_UPLOAD
FROM TEST_UPLOAD) = ''X''
BEGIN
RAISERROR(''*** Avertissement *** Copy Upload dans Maestro '', 16, 1)
END
', @.database_name = N'AS400', @.server = N'', @.database_user_name = N'', @.subsystem = N'TSQL', @.cmdexec_success_code = 0, @.flags = 2, @.retry_attempts = 0, @.retry_interval = 1, @.output_file_name = N'c:\DTS\Logs\Test_UploadCommandesStep.log', @.on_success_step_id = 0, @.on_success_action = 3, @.on_fail_step_id = 0, @.on_fail_action = 2
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobstep @.job_id = @.JobID, @.step_id = 4, @.step_name = N'Test_Upload Commandes', @.command = N'DTSRun /~Z0x5F4F7BA66CB617B86BCC563E86B5C07376BFA0C96766A8 972F7E2AE768C0D2D4B5921094A7CFDAF205EAD298C872263D B963EDDEE1E6D5029CCB81BD1ECFF3932DF1A04B107B2B1514 C18176F412201E686A7DC37753F0D7036BC4076C1B87A404EE 673B7DBDC60F860ABED0E9F04600EA05DC37239265F49D18C9 26FC683E0E542F40E7B838BB2664CF12', @.database_name = N'', @.server = N'', @.database_user_name = N'', @.subsystem = N'CmdExec', @.cmdexec_success_code = 0, @.flags = 2, @.retry_attempts = 0, @.retry_interval = 0, @.output_file_name = N'c:\DTS\Logs\Test_UploadCommandesStep.log', @.on_success_step_id = 0, @.on_success_action = 3, @.on_fail_step_id = 0, @.on_fail_action = 3
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobstep @.job_id = @.JobID, @.step_id = 5, @.step_name = N'Test_Upload Collections', @.command = N'DTSRun /~Z0xC4DCE73BFF8EB8958A569CF001E7764EDCA3DBA6CD29A1 3E9150B7C34F88FD0EA3114E6F6CA688F0E6D03DCC2B0B0996 1E529D40BC65AED39E97EBF3153D41F9A663C8AAEBB9D62DDE 119F217125C85BA50A662F8EEA95E08B5ECAB21C3627000E13 CAC07CA608E402106F5ECA139DA115EE94F9C5709FDCF46256 2B00F3020D772FBBCBD9D77397E3BD3A398CB8D8E360998F ', @.database_name = N'', @.server = N'', @.database_user_name = N'', @.subsystem = N'CmdExec', @.cmdexec_success_code = 0, @.flags = 2, @.retry_attempts = 0, @.retry_interval = 1, @.output_file_name = N'c:\DTS\Logs\Test_UploadCollectionsStep.log', @.on_success_step_id = 0, @.on_success_action = 3, @.on_fail_step_id = 0, @.on_fail_action = 3
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobstep @.job_id = @.JobID, @.step_id = 6, @.step_name = N'UpLoad_V_Entete', @.command = N'DECLARE
@.TransactionNb varchar(10),
@.EqId varchar(10)

DECLARE TransactionNb_cursor CURSOR
FOR
SELECT TransactionNb, EqId
FROM EntCom
WHERE UpdCode = ''C''

OPEN TransactionNb_cursor

FETCH NEXT FROM TransactionNb_cursor
INTO @.TransactionNb, @.EqId

WHILE @.@.FETCH_STATUS <> -1
BEGIN
EntCom
IF (SELECT UpdCode
FROM DetCom
WHERE TransactionNb = @.TransactionNb and EqId = @.EqId) = ''C''
BEGIN
CONTINUE
END
ELSE
BEGIN
RAISERROR (50005, 10, 0, @.TransactionNb, @.EqId)
END

FETCH NEXT FROM TransactionNb_cursor
INTO @.TransactionNb, @.EqId
END

CLOSE TransactionNb_cursor
DEALLOCATE TransactionNb_cursor
', @.database_name = N'Test_Commander', @.server = N'', @.database_user_name = N'', @.subsystem = N'TSQL', @.cmdexec_success_code = 0, @.flags = 2, @.retry_attempts = 0, @.retry_interval = 1, @.output_file_name = N'c:\DTS\Logs\Test_UploadCommandesStep.log', @.on_success_step_id = 0, @.on_success_action = 3, @.on_fail_step_id = 0, @.on_fail_action = 3
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobstep @.job_id = @.JobID, @.step_id = 7, @.step_name = N'UpLoad_V_Dtail', @.command = N'DECLARE
@.TransactionNb varchar(10),
@.EqId varchar(10)

DECLARE TransactionNb_cursor CURSOR
FOR
SELECT TransactionNb, EqId
FROM DetCom
WHERE UpdCode = ''C''

OPEN TransactionNb_cursor

FETCH NEXT FROM TransactionNb_cursor
INTO @.TransactionNb, @.EqId

WHILE @.@.FETCH_STATUS <> -1
BEGIN
-- Vrifier s''il existe une transaction avec le UpdCode = ''C'' dans EntCom
IF (SELECT UpdCode
FROM EntCom
WHERE TransactionNb = @.TransactionNb and EqId = @.EqId) = ''C''
BEGIN
CONTINUE
END
ELSE
BEGIN
RAISERROR (50006, 10, 0, @.TransactionNb, @.EqId)
END

FETCH NEXT FROM TransactionNb_cursor
INTO @.TransactionNb, @.EqId
END

CLOSE TransactionNb_cursor
DEALLOCATE TransactionNb_cursor
', @.database_name = N'Test_Commander', @.server = N'', @.database_user_name = N'', @.subsystem = N'TSQL', @.cmdexec_success_code = 0, @.flags = 2, @.retry_attempts = 0, @.retry_interval = 1, @.output_file_name = N'c:\DTS\Logs\Test_UploadCommandesStep.log', @.on_success_step_id = 0, @.on_success_action = 3, @.on_fail_step_id = 0, @.on_fail_action = 3
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobstep @.job_id = @.JobID, @.step_id = 8, @.step_name = N'Test_UpLoad_Demande', @.command = N'DTSRun /~Z0xBCAC64110FBAE51F592FE69C2AD17FA3221C1CD7655D78 57A08FFE18C4FCA0A3F030E6ED91BD8962BBDE9C3FCDC5F340 ED2D68443C7577C5DBD2AD344EA9A1CED6568FA45E339E95C9 831E4DD5D783C0C52D2BD5DD5964040600AA94A3272BB3AB2A 5A5C9F9AAA3F428F8B4D327E4307B6E379BAAEAE4DCBC5F1EB 02416E763CEBCE05', @.database_name = N'', @.server = N'', @.database_user_name = N'', @.subsystem = N'CmdExec', @.cmdexec_success_code = 0, @.flags = 2, @.retry_attempts = 0, @.retry_interval = 1, @.output_file_name = N'c:\DTS\Logs\Test_UploadCommandesStep.log', @.on_success_step_id = 0, @.on_success_action = 1, @.on_fail_step_id = 0, @.on_fail_action = 2
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @.ReturnCode = msdb.dbo.sp_update_job @.job_id = @.JobID, @.start_step_id = 1

IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback

-- Add the job schedules
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobschedule @.job_id = @.JobID, @.name = N'Test_Upload Commandes', @.enabled = 1, @.freq_type = 8, @.active_start_date = 20031020, @.active_start_time = 60000, @.freq_interval = 62, @.freq_subday_type = 4, @.freq_subday_interval = 10, @.freq_relative_interval = 1, @.freq_recurrence_factor = 1, @.active_end_date = 99991231, @.active_end_time = 220000
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback

-- Add the Target Servers
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobserver @.job_id = @.JobID, @.server_name = N'(local)'
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback

END
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@.@.TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:|||Originally posted by Brett Kaiser
Why don't you script the job abd post/attach it?

Well, you asked for it, and hillcat ran with it ;)|||but it hink that its this part that doesnt not work

DECLARE
@.TransactionNb varchar(10),
@.EqId varchar(10)

DECLARE TransactionNb_cursor CURSOR
FOR
SELECT TransactionNb, EqId
FROM DetCom
WHERE UpdCode = 'C'

OPEN TransactionNb_cursor

FETCH NEXT FROM TransactionNb_cursor
INTO @.TransactionNb, @.EqId

WHILE @.@.FETCH_STATUS <> -1
BEGIN
-- Vrifier s'il existe une transaction avec le UpdCode = 'C' dans EntCom
IF (SELECT UpdCode
FROM EntCom
WHERE TransactionNb = @.TransactionNb and EqId = @.EqId) = 'C'
BEGIN
CONTINUE
END
ELSE
BEGIN
RAISERROR (50006, 10, 0, @.TransactionNb, @.EqId)
END

FETCH NEXT FROM TransactionNb_cursor
INTO @.TransactionNb, @.EqId
END

CLOSE TransactionNb_cursor
DEALLOCATE TransactionNb_cursor|||shes a tuffy hehe|||can someone please respond to this|||This is the same as the other thread, isn't it?

Anyway, make sure the on success and on failure directives are not looping.

Make sure you use WHILE @.@.FETCH_STATUS = 0

Using <> -1 could cause it to loop unexpectedly.

Frant permission to exec a DTS

Hi,
I created a DTS job, I need win user group able to exec that DTS through
TDYrun untility.
How do I grant permssion to that group
ThanksI guess this user (domain\username) should have administrative rights
on your SQL Server Box.

Frant permission to exec a DTS

Hi,
I created a DTS job, I need win user group able to exec that DTS through
TDYrun untility.
How do I grant permssion to that group
ThanksI guess this user (domain\username) should have administrative rights
on your SQL Server Box.