FTP SSIS Task

  • I have developed a package that reads some info from a database, transform some data, puts into an Excel Spreadsheet, and is using the FTP task to get it to it's final destination. During the development of this package (ie running it locally) everything worked fine. I am now attempting to call this package via SQL Agent job and I am not having much luck.

    Googling the errors, I have tried numerous suggestions and nothing seems to work.

    The account running the package, is set up as a credential/proxy and is used to run all the other SSIS packages. I have changed to the Protection level of the package to different settings, putting username/password into Config file, among other things.

    Below are some of the errors messages I am getting. When the Protection level is set default I am getting this:

    Description: Failed to decrypt protected XML node "DTS:Property" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available. End Error Error: 2011-07-11 13:07:32.32 Code: 0xC001602A Source: ININ_Sync_BCFOrg-Aprimo Connection manager "FTP Connection Manager" Description: An error occurred in the requested FTP operation. Detailed error description: 200 Type I ok. 200 PORT command successful. 451 Requested action aborted: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond 209.43.1.25:3922 . End Error Error: 2011-07-11 13:07:32.36 Code: 0xC002918B Source: FTP - Aprimo - Send File to Aprimo for Upload FTP Task Description: Unable to send files using "FTP Connection Manager". End Error Error: 2011-07-11 13:07:32.74 Code: 0x00000001 Source: SSISOpsNFObj_SendAlert Description: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.Net.Mail.SmtpException: Mailbox unavailable. The server response was: 5.7.1 Client does not have permissions to send as this sender at System.Net.Mail.DataStopCommand.CheckResponse(SmtpStatusCode statusCode, String serverResponse) at System.Net.Mail.DataStopCommand.Send(SmtpConnection conn) at System.Net.Mail.SmtpConnection.OnClose(Object sender, EventArgs args) at System.Net.ClosableStream.Close() at System.Net.Mail.MailWriter.Close() at System.Net.Mail.SmtpClient.Send(MailMessage message) at st_a05591c55163409d9ef98e2e342d682c.vbproj.ScriptMain.PerformNotificationAction() at st_a05591c55163409d9ef98e2e342d682c.vbproj.ScriptMain.Main() --- End of inner exception stack trace --- at System.RuntimeMethodHandle._InvokeMethodFast(Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner) at System.RuntimeMethodHandle.InvokeMethodFast(Object target, Object[] arguments, Signature sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner) at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks) at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture) at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams) at System.Type.InvokeMember(String name, BindingFlags invokeAttr, Binder binder, Object target, Object[] args, CultureInfo culture) at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript() End Error Error: 2011-07-11 13:07:32.81 Code: 0x00000001 Source: SSISOpsNFObj_SendAlert Description: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.Net.Mail.SmtpException: Mailbox unavailable. The server response was: 5.7.1 Client does not have permissions to send as this sender at System.Net.Mail.DataStopCommand.CheckResponse(SmtpStatusCode statusCode, String serverResponse) at System.Net.Mail.DataStopCommand.Send(SmtpConnection conn) at System.Net.Mail.SmtpConnection.OnClose(Object sender, EventArgs args) at System.Net.ClosableStream.Close() at System.Net.Mail.MailWriter.C... The package execution fa... The step failed.

    When I set the Protection Level to Dont save sensitive I get:

    thodAttributes methodAttributes, RuntimeTypeHandle typeOwner) at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks) at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture) at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams) at System.Type.InvokeMember(String name, BindingFlags invokeAttr, Binder binder, Object target, Object[] args, CultureInfo culture) at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript() End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 2:05:26 PM Finished: 2:06:38 PM Elapsed: 72.369 seconds. The package execution failed. The step failed.

    Any and all help will be greatly appreciated...

  • Have you verified existence of the mailbox?

    System.Net.Mail.SmtpException: Mailbox unavailable.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • The mailbox errors are coming from this SSISOpsNFObj_SendAlert, which is a part of this Pragmatic Works Auditing Framework, called BI xPress, we insert into our packages. That is the not the problem, The real problem is here:

    Connection manager "FTP Connection Manager" Description: An error occurred in the requested FTP operation. Detailed error description: 200 Type I ok. 200 PORT command successful. 451 Requested action aborted: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond <IP address Removed>.

    I believe this has something to do with our firewall and I am working with one our Network Admins to see about getting corrected.

  • A quick question, does the out of box FTP Task support secure FTP transfers? If not does any one know of a (free) third party FTP Task that does?

  • GBeezy (7/12/2011)


    A quick question, does the out of box FTP Task support secure FTP transfers? If not does any one know of a (free) third party FTP Task that does?

    Out of the box does not support SFTP. I usually just use a command to issue dos commands to run SFTP parts from within SSIS.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Hi

    It may be a case of not supplying the user credentials.

    Depending on your security settings the credentials Will either be blind to the developer or not stored at all. It is not possible to set the credentials directly with variables in the ftp task.

    In order to set the cresentials you need to set them through a script task.

    Hope that this can fix your issue

    //Peter

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply