none
Could not open the connection when using SSIS Script Task in control flow

    Question

  • Hi,

    I'm trying to execute a package that runs a Script Task in a control flow of a SSIS BI project with the following code (the Script Task output is connected to the input of a Data Flow Task), but it's not working, it doesn't open the connection:

    -----------------------------------------------------------------

    using System;
    using System.Data;
    using Microsoft.SqlServer.Dts.Runtime;
    using System.Windows.Forms;
    #endregion
    namespace ST_7e5ba488e75e4b9ca4a3f2e3fa831dbd
    {
     
     [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
     public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
     {
     
      public void Main()
      {
                System.Data.OleDb.OleDbConnection oleDbConn;
                System.Data.OleDb.OleDbCommand oleDbCommand;
                String sql;
                String formattedDate = System.DateTime.Now.ToString("yyyy-MM-dd");
                String connString = "Data Source=AFDB02;Initial Catalog = AdventureWorks2014; Provider = SQLNCLI11.1; Integrated Security = SSPI; Auto Translate = False; ";
                oleDbConn = new System.Data.OleDb.OleDbConnection(connString);
                try
                {
                    oleDbConn.Open();
                    sql = "SELECT MAX(LastModifiedDate) as lastModifiedDate from dbo.CustomerDetails";
                oleDbCommand = new System.Data.OleDb.OleDbCommand(sql, oleDbConn);
                    Object date = oleDbCommand.ExecuteScalar();
                    if (date == null || date.ToString() == "")
                    {
                        Dts.TaskResult = (int)ScriptResults.Success;
                    }
                    else
                    {
                        DateTime ResultDate = Convert.ToDateTime(date);
                        String formattedResultDate = ResultDate.ToString("yyyy-MM-dd");
                        if (formattedDate != formattedResultDate)
                        {
                            Dts.TaskResult = (int)ScriptResults.Success;
                        }
                        else
                        {
                            MessageBox.Show("Data already inserted for the day");
                            Dts.TaskResult = (int)ScriptResults.Failure;
                        }
                    }
                }
                catch
                {
                    MessageBox.Show("Could not open connection!");
                }
                Dts.TaskResult = (int)ScriptResults.Success;
      }
            #region ScriptResults declaration
         
            enum ScriptResults
            {
                Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
                Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
            };
       }
    }

    -----------------------------------------------------------------

    I'm using Visual Studio Community 2015 and SQL Server for developer edition.

    I'm getting the message "Could not open connection!" but I don't see why... is there something wrong with the provider in the connection string ? 

    Can somebody help ?

    Thanks

    Thursday, April 12, 2018 9:38 PM

Answers

  • is there something wrong with the provider in the connection string ? 

    Yes, that's a Connection string for SqlClient, while you use OleDB (why?), see https://www.connectionstrings.com/sql-server-native-client-9-0-oledb-provider/

    Add an exception handling to get the full error message.

                 catch (Exception ex)
                 {
                     MessageBox.Show(ex.Message);
                 }


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Proposed as answer by Visakh16MVP Friday, April 13, 2018 8:36 AM
    • Marked as answer by MicroSup Wednesday, April 18, 2018 2:01 PM
    Friday, April 13, 2018 7:46 AM
  • I added the exception handling to get the error message and here is below what it says : 

    ----------------------------------------

    Login timeout expired
    A network-related or instance-specific error occurered while establishing a connection to SQL Server.
    Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections.
    For more information see SQL Server Books Online.
    Named Pipes Provider: Could not open a connection to SQL Server [53].

    ----------------------------------------

    I checked SQL Server is configured to allow remote connections. 

    I checked "Provider = SQLNCLI11" is OleDb connection type. 

    I'm using SQL Server 2016 Developer edition Version 13.0.1742.0. and it's running on the same machine O/S Windows 10 Pro than the SSIS package I'm trying to run. I tried to change the syntax of the connection string to : 

    String connString = "Provider = SQLNCLI11; Server=localhost;Database=AFDB02; Integrated Security = SSPI; "; 

    but I'm getting the same error message with only difference [2] instead of [53]. 

    Any idea ? 

    • Marked as answer by MicroSup Wednesday, April 25, 2018 11:28 AM
    Monday, April 16, 2018 5:56 PM

All replies

  • is there something wrong with the provider in the connection string ? 

    Yes, that's a Connection string for SqlClient, while you use OleDB (why?), see https://www.connectionstrings.com/sql-server-native-client-9-0-oledb-provider/

    Add an exception handling to get the full error message.

                 catch (Exception ex)
                 {
                     MessageBox.Show(ex.Message);
                 }


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Proposed as answer by Visakh16MVP Friday, April 13, 2018 8:36 AM
    • Marked as answer by MicroSup Wednesday, April 18, 2018 2:01 PM
    Friday, April 13, 2018 7:46 AM
  • I added the exception handling to get the error message and here is below what it says : 

    ----------------------------------------

    Login timeout expired
    A network-related or instance-specific error occurered while establishing a connection to SQL Server.
    Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections.
    For more information see SQL Server Books Online.
    Named Pipes Provider: Could not open a connection to SQL Server [53].

    ----------------------------------------

    I checked SQL Server is configured to allow remote connections. 

    I checked "Provider = SQLNCLI11" is OleDb connection type. 

    I'm using SQL Server 2016 Developer edition Version 13.0.1742.0. and it's running on the same machine O/S Windows 10 Pro than the SSIS package I'm trying to run. I tried to change the syntax of the connection string to : 

    String connString = "Provider = SQLNCLI11; Server=localhost;Database=AFDB02; Integrated Security = SSPI; "; 

    but I'm getting the same error message with only difference [2] instead of [53]. 

    Any idea ? 

    • Marked as answer by MicroSup Wednesday, April 25, 2018 11:28 AM
    Monday, April 16, 2018 5:56 PM