Post reply

i am trying to insert data into sql table using c# ssis script task +very soon +asap

  • January 30, 2015 at 10:53 am

    #314196

    i am trying to insert data into sql table using c# ssis script task .. I am pasting below querry.

    It is not working I am getting connection issue . any help on this how to insert data. I have question how to write insert script if have a local variable ..

    please check my querry below.

    private void InsertIntoTable(string manifest_inBoundfile, string sender, string receiver, string workstream, string manifest_hashid, int manifest_count, string inBouind_hashid, int inBound_count, byte flag)

    {

    String inBound_file_name = manifest_inBoundfile;

    String Sender = sender;

    String Receiver = receiver;

    String WorkStream = workstream;

    String manifest_hash_id = manifest_hashid;

    String inbound_hash_id = inBouind_hashid;

    int manifest_row_Count = manifest_count;

    int inBound_row_Count = inBound_count;

    DateTime getdate = DateTime.Now;

    byte Flag = flag;

    //string strConnectionString = ConnectionStrings["SqlConnectionString"].ConnectionString;

    //SqlConnection myConnection = new SqlConnection();

    //myConnection.ConnectionString = strConnectionString;

    // string strCommandText = ("INSERT INTO dbo.InBound_File_Audit(" + " AsOfDate, InBound_FileName, Manifest_Sender, Manifest_Reciever, Manifest_Workstream, Manifest_MD5Hash, Manifest_RowCount, InBound_MD5Hash, InBound_FileCount, Validation_Flag" + ") VALUES (" + getdate, inBound_file_name, Sender, Receiver, WorkStream, manifest_hash_id, manifest_row_Count, inbound_hash_id, inBound_row_Count, flag + ")", cn);

    // SqlCommand cmd = new SqlCommand(strCommandText, myConnection);

    //SqlConnection cn = new SqlConnection("Data Source=dsqlmerger;initial catalog=dCTLFW; User ID=FHLBDM\\smaganti;Password=Federal01!;Integrated Security=SSPI;");

    // //SqlCommand cmd = new SqlCommand("INSERT INTO dbo.InBound_File_Audit(" + " AsOfDate, InBound_FileName, Manifest_Sender, Manifest_Reciever, WorkStream, Manifest_MD5Hash, Manifest_RowCount, InBound_MD5Hash, InBound_FileCount, Validation_Flag" + ") VALUES (" + getdate, inBound_file_name, Sender, Receiver, manifest_hash_id, manifest_row_Count, inbound_hash_id, inBound_row_Count, flag + ")", cn);

    // cn.Open();

    // cmd.ExecuteNonQuery();

    // cn.Close();

    using (SqlConnection connection = (SqlConnection)(Dts.Connections["ADO.NET Connection"].AcquireConnection(Dts.Transaction) as SqlConnection))

    {

    using (SqlCommand command = connection.CreateCommand())

    {

    string insertString = @" insert into Categories (AsOfDate, InBound_FileName, Manifest_Sender, Manifest_Reciever, Manifest_Workstream, Manifest_MD5Hash, Manifest_RowCount, InBound_MD5Hash, InBound_FileCount, Validation_Flag)

    values ( getdate, inBound_file_name, Sender, Receiver, WorkStream, manifest_hash_id, manifest_row_Count, inbound_hash_id, inBound_row_Count, Flag )";

    command.CommandText = insertString;

    command.CommandType = CommandType.Text;

    command.ExecuteNonQuery();

    }

    this.Connections.Connection.ReleaseConnection(connection);

    }

    Dts.TaskResult = (int)ScriptResults.Success;

    throw new NotImplementedException();

    }

  • January 30, 2015 at 11:07 am

    #1774423

    anyone interested please help me ASAP

  • January 30, 2015 at 11:18 am

    #1774431

    Quick question, why are you using Script Task instead of ExecuteSQL for this if you have the values in local variables?

    Second question, is the ADO.NET Connection correctly configured?

    😎

  • January 30, 2015 at 11:20 am

    #1774433

    my logic needed to inset using script task

  • January 30, 2015 at 11:22 am

    #1774434

    I am not sure how to configures ado.net., but I add.net connections string in ssis . but I am not sure that is going to help me.

  • January 30, 2015 at 11:29 am

    #1774439

    Next question, can you somehow test if this connection works outside the script task as if it doesn't then obviously the script task code will not work?

    😎

  • January 30, 2015 at 11:33 am

    #1774441

    ok sure

    I am working on that, or let me know if you have any idea I will change my code. just I need a code how to insert into database

  • January 30, 2015 at 11:42 am

    #1774442

    My suggestion would be to use a Stored Procedure on the server, a SqlCommand object, set each parameter and exec.

    😎

Viewing 8 posts - 1 through 7 (of 7 total)

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