February 22, 2016 at 1:28 am
Hi Experts,
When executing Script Task in SSiS 2008 R2, I am getting the following errors on open SFTP connection line "sFtpConnection.Connect();"
SSIS package "avande_auth_response.dtsx" starting.
Error: 0x0 at Script Task, SFTP Script Task: Error: Object reference not set to an instance of an object.
Warning: 0x80019002 at Script Task: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
Task failed: Script Task
Warning: 0x80019002 at avande_auth_response: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "avande_auth_response.dtsx" finished: Failure.
Here is the C# script. SftpDownloadDirctory variable is used as ReadOnly, and SftpFileExists variable is used as ReadWrite
using System;
using System.Collections;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.IO;
using System.Net;
namespace ST_ea426d8891b44047be9925a61b74f82f.csproj
{
[System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
#region VSTA generated code
enum ScriptResults
{
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
};
#endregion
/*
The execution engine calls this method when the task executes.
To access the object model, use the Dts property. Connections, variables, events,
and logging features are available as members of the Dts property as shown in the following examples.
To reference a variable, call Dts.Variables["MyCaseSensitiveVariableName"].Value;
To post a log entry, call Dts.Log("This is my log text", 999, null);
To fire an event, call Dts.Events.FireInformation(99, "test", "hit the help message", "", 0, true);
To use the connections collection use something like the following:
ConnectionManager cm = Dts.Connections.Add("OLEDB");
cm.ConnectionString = "Data Source=localhost;Initial Catalog=AdventureWorks;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;";
Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
To open Help, press F1.
*/
public void Main()
{
try
{
// Get the ftp connection from the Connection Managers collection
ConnectionManager sftpServer = Dts.Connections["avande.SFTP"];
// Create a FTP connection object and use the credentials of connection manager
FtpClientConnection sFtpConnection = new FtpClientConnection(sftpServer.AcquireConnection(null));
// Open the connection
sFtpConnection.Connect();
// Set work folder with the value of the variable
sFtpConnection.SetWorkingDirectory(Dts.Variables["User::SftpDownloadDirctory"].Value.ToString());
// Create StringArrays for filenames and folders
// The folderNames aren't used, but is mandatory
// for the next method.
String[] fileNames;
String[] folderNames;
// Get a directory listing and fill the StringArray variables
sFtpConnection.GetListing(out folderNames, out fileNames);
//if (fileNames != null)
{
// Copy StringArray to ArrayList to fit in Object variable
ArrayList fileNamesArray = new ArrayList(fileNames);
// Fill ssis object variable
Dts.Variables["User::SftpFileList"].Value = fileNamesArray;
}
// Close connection
sFtpConnection.Close();
// Close Script Task, set result to success
Dts.Variables["User::SftpFileExists"].Value = true;
}
catch (Exception ex)
{
// Fire error and set result to failure
Dts.Events.FireError(0, "SFTP Script Task", "Error: " + ex.Message, string.Empty, 0);
Dts.Variables["User::SftpFileExists"].Value = false;
}
}
}
}
I have been working on this for a long while now. Any solution is greatly appreciated.
MC
February 22, 2016 at 3:36 pm
How are you authenticating to the remote site that the connection points to? The answer might help you see what's going on, as an SSIS package, when run on the server, as an agent job, will run with the execution context supplied by the SQL Agent, whereas running the package locally on your machine will run with your user context. Are there any variables involved in having the password supplied to the connection? That's another spot where a difference in execution context has potential for grief, due to differences in things like environment variables. Just trying to come up with possible ways for it to fail...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
February 22, 2016 at 4:22 pm
Greetings Steve,
This error is generated during Debug within BIDS. The SFTP credentials (no variables) are coming from the SSH Connection Manager, a COZYROC add-on.
February 22, 2016 at 9:57 pm
Okay, that takes out execution context differences. How about a corporate firewall issue with connectivity? Have you ever successfully connected to the server in question? Any chance the "add-on" might have a bug ?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
February 25, 2016 at 11:07 am
I use the same credentials in SFTP Task with no connection issue. I was able to download and move files.
February 25, 2016 at 4:55 pm
I can't help much with C#, but... I am just wondering if the Connection Manager object needs to be "opened" before you try to connect using it. The error you are getting resembles the error you'd get if you wrote VB and were doing an ADO connection to a SQL Server. The connection object has to be open before you can make use of it. Not sure what C# code will be for that, but the similar ADO code would be:
cm.Open
Not sure if that will help or not... just a thought...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply