0
#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.IO;
using System.Data.SqlClient;
#endregion

namespace ST_564774fbfa9f46
{
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {

        public void Main()
        {
           try
            {
                int  TenantID ;
                TenantID = (int)Dts.Variables["User::TenantID"].Value;
                string LogFolder = Dts.Variables["User::LogFolder"].Value.ToString();
                // Database Connection

                MessageBox.Show(TenantID.ToString());

                SqlConnection myADONETConnection = new SqlConnection(); 
                myADONETConnection = (SqlConnection)
                (Dts.Connections["DB"].AcquireConnection(Dts.Transaction) as SqlConnection);


                string query = "Insert into Clever.tenant VALUES(" + TenantID + ",'N','K','1','2','3','4','5')";


                SqlCommand myCommand1 = new SqlCommand(query, myADONETConnection);
                myCommand1.ExecuteNonQuery();


                MessageBox.Show("Hello World");

                Dts.TaskResult = (int)ScriptResults.Success;

            }
            catch (Exception exception)
            {

                MessageBox.Show(exception.ToString());

                using (StreamWriter sw = File.CreateText(Dts.Variables["User::LogFolder"].Value.ToString()
                    + "\\" + "ErrorLog_.log"))
                {
                    sw.WriteLine(exception.ToString());
                    Dts.TaskResult = (int)ScriptResults.Failure;
                }
            }

        }
    }
}

Error Log On Execution:

System.InvalidOperationException: ExecuteNonQuery: Connection property has not been initialized.
  at System.Data.SqlClient.SqlCommand.ValidateCommand(String method, Boolean async)
  at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
  at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
  at ST_564774fbfa9f46929e46bf3851fa1d83.ScriptMain.Main()

TenantID is loading from "Foreach Loop Contanier"

Thomas Fritsch
  • 9,639
  • 33
  • 37
  • 49
Kishore
  • 1
  • 1
  • `SqlConnection myADONETConnection = new SqlConnection();` - you haven't provided a connection string; *where* is it meant to be connecting? I would usually expect `new SqlConnection(someConnectionString)`, followed by a call to `theConnection.Open()`. – Marc Gravell Mar 26 '18 at 16:14
  • 2
    You may have a look at https://stackoverflow.com/help/how-to-ask For instance, try to be a bit descriptive instead of dropping a lot of code and an error stack. – nicolallias Mar 26 '18 at 16:16
  • Check if myADONETConnection is null. – M Bakardzhiev Mar 26 '18 at 16:41
  • You may also error out on TenantID not being converted to string the way you are using it. Add TenantID.ToString() – KeithL Mar 26 '18 at 16:59

0 Answers0