SSIS job fails

Advertisement

geninfarun
Joined:
Posts:
14
Location:
USA

SSIS job fails

I have an Execute Process Task with the following settings

<invalid hyperlink removed by admin> (<invalid hyperlink removed by admin>)

ftsqa is my saved session.

This works fine most of the time, but when the server is rebooted it fails with error. I have to login to the saved session manually from winscp and then the jobs will start working again.How can i start the session if it is closed before executing the job?

Reply with quote

Advertisement

martin
Site Admin
martin avatar
Joined:
Posts:
27,226
Location:
Prague, Czechia

Re: SSIS job fails

What error? Please post a log file.
_________________
Martin Prikryl

Reply with quote

geninfarun
Joined:
Posts:
14
Location:
USA

Date 4/1/2010 7:00:00 AM
Log Job History (ExecuteRemitFile)

Step ID 1
Server XYZD
Job Name ExecuteFile
Step Name ProcessFile
Duration 00:00:25
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0

Message
Executed as user: gov\anar. Microsoft (R) SQL Server Execute Package Utility Version 10.0.1600.22 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 7:00:01 AM Error: 2010-04-01 07:00:25.59 Code: 0xC0029151 Source: Execute Process Task Execute Process Task Description: In Executing "C:\Program Files (x86)\WinSCP\WinSCP.exe" "/command "open ftsqa" "option confirm off" "get /outbound/*.csv c:\testpayment.csv"" at "C:\Program Files (x86)\WinSCP", The process exit code was "1" while the expected was "0". End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 7:00:01 AM Finished: 7:00:25 AM Elapsed: 24.57 seconds. The package execution failed. The step failed.

Reply with quote

martin
Site Admin
martin avatar
Joined:
Posts:
27,226
Location:
Prague, Czechia

I've meant log file from WinSCP.
_________________
Martin Prikryl

Reply with quote

geninfarun
Joined:
Posts:
14
Location:
USA

how do I generate the log file. I turned on logging in the properties but it only shows me the latest attempt when I use the gui.

Reply with quote

martin
Site Admin
martin avatar
Joined:
Posts:
27,226
Location:
Prague, Czechia

Use /log=path_to_logfile command line parameter.
_________________
Martin Prikryl

Reply with quote

geninfarun
Joined:
Posts:
14
Location:
USA

martin wrote:

Use /log=path_to_logfile command line parameter.

didnt have an error till yesterday when it failed to copy file to local directory. log file:

. 2010-06-08 07:00:26.060 Listing directory "/outbound".
> 2010-06-08 07:00:26.061 Type: SSH_FXP_OPENDIR, Size: 18, Number: 267
< 2010-06-08 07:00:28.375 Type: SSH_FXP_HANDLE, Size: 10, Number: 267
> 2010-06-08 07:00:28.375 Type: SSH_FXP_READDIR, Size: 10, Number: 524
< 2010-06-08 07:00:28.437 Type: SSH_FXP_NAME, Size: 344, Number: 524
> 2010-06-08 07:00:28.437 Type: SSH_FXP_READDIR, Size: 10, Number: 780
< 2010-06-08 07:00:28.498 Type: SSH_FXP_STATUS, Size: 17, Number: 780
< 2010-06-08 07:00:28.498 Status code: 1
> 2010-06-08 07:00:28.498 Type: SSH_FXP_CLOSE, Size: 10, Number: 1028
. 2010-06-08 07:00:28.499 File: "/outbound/EBR_PSO_2010_06_07.csv"
. 2010-06-08 07:00:28.499 Copying "/outbound/EBR_PSO_2010_06_07.csv" to local directory started.
. 2010-06-08 07:00:28.500 Binary transfer mode selected.
. 2010-06-08 07:00:28.500 Opening remote file.
> 2010-06-08 07:00:28.500 Type: SSH_FXP_OPEN, Size: 49, Number: 1283
< 2010-06-08 07:00:28.557 Type: SSH_FXP_STATUS, Size: 40, Number: 1028
. 2010-06-08 07:00:28.557 Discarding reserved response
< 2010-06-08 07:00:40.887 Type: SSH_FXP_HANDLE, Size: 10, Number: 1283
> 2010-06-08 07:00:40.887 Type: SSH_FXP_FSTAT, Size: 10, Number: 1544
> 2010-06-08 07:00:40.887 Type: SSH_FXP_READ, Size: 22, Number: 1797
< 2010-06-08 07:00:43.969 Type: SSH_FXP_ATTRS, Size: 37, Number: 1544
. 2010-06-08 07:00:43.969 Storing reserved response
< 2010-06-08 07:00:44.030 Status code: 1
. 2010-06-08 07:00:44.030 3 skipped SSH_FXP_WRITE, SSH_FXP_READ, SSH_FXP_DATA and SSH_FXP_STATUS packets.
> 2010-06-08 07:00:44.030 Type: SSH_FXP_CLOSE, Size: 10, Number: 2308
. 2010-06-08 07:00:44.031 Closing connection.
. 2010-06-08 07:00:44.031 Sending special code: 12
. 2010-06-08 07:00:44.031 Sent EOF message    

Reply with quote

martin
Site Admin
martin avatar
Joined:
Posts:
27,226
Location:
Prague, Czechia

There's no error in the log file. It might be useful to see output of the winscp.exe, if you are not able to capture it, try running the winscp.com and redirect its output to a file.

Reply with quote

geninfarun
Joined:
Posts:
14
Location:
USA

martin wrote:

There's no error in the log file. It might be useful to see output of the winscp.exe, if you are not able to capture it, try running the winscp.com and redirect its output to a file.

new error this time, looks like a connection issue.
. 2010-06-10 07:00:06.562 Prompt (6, SSH password, , &Password: )
. 2010-06-10 07:00:06.562 Using stored password.
. 2010-06-10 07:00:06.563 Sent password
. 2010-06-10 07:00:07.256 Access granted
. 2010-06-10 07:00:07.315 Opened channel for session
. 2010-06-10 07:00:22.318 Waiting for data timed out, asking user what to do.
. 2010-06-10 07:00:22.330 Asking user:
. 2010-06-10 07:00:22.330 Host is not communicating for 15 seconds.
. 2010-06-10 07:00:22.330 
. 2010-06-10 07:00:22.330 Wait for another 15 seconds? ()
. 2010-06-10 07:00:22.330 Attempt to close connection due to fatal exception:
* 2010-06-10 07:00:22.330 Terminated by user.
. 2010-06-10 07:00:22.331 Closing connection.
. 2010-06-10 07:00:22.331 Sending special code: 12    

Reply with quote

martin
Site Admin
martin avatar
Joined:
Posts:
27,226
Location:
Prague, Czechia

Well the server is not responding. Are you able to connect now at all (outside of SSIS)?

Reply with quote

geninfarun
Joined:
Posts:
14
Location:
USA

martin wrote:

Well the server is not responding. Are you able to connect now at all (outside of SSIS)?

is there a way to tell winscp to continue trying to connect till it gets a response?

Reply with quote

martin
Site Admin
martin avatar
Joined:
Posts:
27,226
Location:
Prague, Czechia

geninfarun wrote:

is there a way to tell winscp to continue trying to connect till it gets a response?
No.
_________________
Martin Prikryl

Reply with quote

Advertisement

You can post new topics in this forum