none
delete data from sql table based on collection data RRS feed

  • Question

  • I am from DBA background and trying to use c# programming and SSIS for data transfer.

    I am trying to delete some data from a table A in X database based on some values from Table B of Y database.

    I do not want to use stored procedure because both databases are on different servers with no connection to each other.

    I am trying to achive this by using SSIS script task which is basically c# coding. I am fetching a resultset with data and storing it in an Object variable and then using this object variable as condition in the delete query. But this is throwing me error. below is the code that I am using. "Dts.Variables["dset"].Value" is the variable with resultset and is of type object.

    Can someone help me on how to get this result with c# code.

    System.Data.SqlClient.SqlConnection sqlConnection1 =
                new System.Data.SqlClient.SqlConnection("Data Source= QQQ;" + "Initial Catalog=Roger;Integrated Security=SSPI");

                System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
                cmd.CommandType = System.Data.CommandType.Text;
                cmd.CommandText = "DELETE from xxx WHERE id in("+  Dts.Variables["dset"].Value + ")";
                cmd.Connection = sqlConnection1;

                sqlConnection1.Open();
                cmd.ExecuteNonQuery();
                sqlConnection1.Close();

    • Moved by CoolDadTx Monday, October 5, 2015 2:09 PM SSIS related
    Saturday, October 3, 2015 6:11 PM

All replies

  • It's throwing you an error. And what might the error be?

     
    Saturday, October 3, 2015 7:13 PM
  • You need to use parameters in your command. 

    https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.parameters(v=vs.110).aspx


    A flower cannot blossom without sunshine, and man cannot live without love.

    Sunday, October 4, 2015 11:10 AM
  • I believe Dts.Variables["dset"].Value of type Object here doesn't work, try convert it to list, like:

    SELECT column_name(s)
    FROM table_name
    WHERE column_name IN (value1,value2,...);



    Join apps request, talk about startup ideas.

    Sunday, October 4, 2015 12:14 PM
  • Hi rosaviakosmos,

    [cmd.CommandText = "DELETE from xxx WHERE id in("+  Dts.Variables["dset"].Value + ")";]

    What is the type of xxx and did you debug your code to see the value of Dts.Variables["dset"].Value?

    Best Regards,
    Li Wang

    Monday, October 5, 2015 7:02 AM