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

    Domanda

  • 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

    giovedì 12 aprile 2018 21:38

Risposte

  • 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]

    • Proposto come risposta Visakh16MVP venerdì 13 aprile 2018 08:36
    • Contrassegnato come risposta MicroSup mercoledì 18 aprile 2018 14:01
    venerdì 13 aprile 2018 07:46
  • 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 ? 

    • Contrassegnato come risposta MicroSup mercoledì 25 aprile 2018 11:28
    lunedì 16 aprile 2018 17:56

Tutte le risposte

  • 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]

    • Proposto come risposta Visakh16MVP venerdì 13 aprile 2018 08:36
    • Contrassegnato come risposta MicroSup mercoledì 18 aprile 2018 14:01
    venerdì 13 aprile 2018 07:46
  • 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 ? 

    • Contrassegnato come risposta MicroSup mercoledì 25 aprile 2018 11:28
    lunedì 16 aprile 2018 17:56