none
Using SSIS Script task to populate data from one table to another RRS feed

  • Question

  • I have two tables Table A and Table B. Need to insert data into Table B from Table A. First, I truncate Table B and then insert. Few columns in Table A and Table are similar. However, I need to join with other tables Table C, Table D etc with Table B in order to populate the columns in Table B. Could someone please provide the code snippet to achieve this.


    Monday, March 30, 2015 8:28 PM

All replies

  • Truncate table TableB

    insert into TableB(col1, col2)

    select Col1, Col2 from TableA

    The second point is bit confusing, you are saying that you need to join TableB with TableC and TableD

    if you join you will be getting any data because tableB dont have data yet.. becuase we are still inserting data.

    another way of doing is after the above 2 statements. try like below

    insert into TableB(Col1, Col2)

    select Col1, Col2 from TableB

    join TableC <Join Clause>

    Join TableD <JoinClause>


    Thanks & Regards Prasad DVR

    Monday, March 30, 2015 8:50 PM
  • Why do you need a SSIS script task to do this? also, why are you doing this through SSIS? in case if you still need to do in SSIS, assuming all the tables in the same server/database, create a connection manager and connect to exact SQL database and then design your Control-flow like:

    1) Exceute SQL task - To Truncate Table B

    2) connect to Data Flow task 

          2a) inside Data flow task: use a ADO .NET source and connect to the database and Data access mode choose 'SQL Command' and then write the SQL query to join Table A/B and so on..

         2b) connect it to ADO.NET destination to transfer the results to Table B

    Monday, March 30, 2015 9:10 PM
  • Thanks for your response. The join was with Table A not Table B. Sorry, that was a typo.

    I'm looking for code snippet to perform this in SSIS script task. would you be able to assist me?


    Tuesday, March 31, 2015 1:04 PM
  • Hello,

    As Vinay has mentioned in his post you should be able to achieve the results using standard SSIS Data flow components as well why use a script task?

    Here's an example that shows how to use SQL Commands in a script task. you should be able to tweak the command as required.

    SSIS Script Task Example


    Regards, Dinesh

    Tuesday, March 31, 2015 1:34 PM
  • //add below 2 lines at the top of the code in Script Component using System.Data.SqlClient; using System.Data; //you need to write you code in the Script component //you will find a line "Add your code here" below that you need to write below code string connetionString = null; SqlConnection cnn ; connetionString = "Data Source=ServerName;Initial Catalog=DatabaseName;User ID=UserName;Password=Password" cnn = new SqlConnection(connetionString); cnn.Open(); //now you have connected to your database string sql;

    sql = "your insert statment"

    SqlCommand cmd = new SqlCommand();

    cmd.CommandType = System.Data.CommandType.Text;
    cmd.CommandText = sql;
    cmd.Connection = cnn;
    cmd.ExecuteNonQuery();
    cnn.Close();


    
    

    you need to put try catch block for exception handling etc.

    insert into TableB(Col1, Col2)

    Select col1, col2 from TableA

    join TableC <join clause>

    join TableD <Join Clause>

    please let me know if dont know how to put joins, you have to give me the table structures so that I can help you to write complete insert clause.

    
    
    
    

    Thanks & Regards Prasad DVR

    Tuesday, March 31, 2015 1:44 PM
  • Appreciate your quick response Prasad. Below is the code I'm using to achieve.

    Public Sub Main()

            Dim connEOCDB As SqlClient.SqlConnection = GetDBProdConnection(Me)

            Dim trans As SqlClient.SqlTransaction = connEOCDB.BeginTransaction()

            Try
                Dim TableA As New DataTable
                Dim TableB As New DataTable

                Dim DataadapterTableA As New SqlClient.SqlDataAdapter()
                DataadapterTableA.SelectCommand = New SqlClient.SqlCommand("select * from TableA", connEOCDB, trans)
                DataadapterTableA.Fill(TableA)

                Dim DataAdapterTableB As New SqlClient.SqlDataAdapter()
                DataAdapterTableB.DeleteCommand = New SqlClient.SqlCommand("Truncate table dbo.TableB", connEOCDB, trans)

                DataAdapterEmpExtdT.InsertCommand = New SqlClient.SqlCommand("insert into dbo.TableB (Col1,Col2,Col3,.....) values (@Col1,@Col2,@Col3...)", connEOCDB, trans)

    Could you please let me know how I can assign values to @Col1, @Col2 etc from DataadapterTableA.Fill(TableA). Also, as mentioned earlier, I need to use joins to populate @Col3 etc as well.

    Please advise.

    Tuesday, March 31, 2015 2:18 PM
  • It should be DataAdapterTableB instead of DataAdapterEmpExtdT in the last line of code mentioned above.

    DataAdapterTableB.InsertCommand = New SqlClient.SqlCommand("insert into dbo.TableB (Col1,Col2,Col3,.....) values (@Col1,@Col2,@Col3...)", connEOCDB, trans)

    Tuesday, March 31, 2015 2:20 PM
  • Hello,

    This script is helps!

    is there a way I can write something like?

    DataAdapterTableB.InsertCommand = New SqlClient.SqlCommand("insert into dbo.TableB select * from TABLEB", connEOCDB, trans)

    my requirement is to, read data from Oracle (select * from table a join table b on (a.col1 = b.col2)) and destination is SQL Server table b. count of columns are same.

    Friday, September 13, 2019 2:41 PM
  • Hi Reach4TheSky,

    There is no need SSIS Script Task for what you are trying to achieve.

    SSIS has Data Flow Task. Inside of it Oracle Source Adapter => OLEDB Destination Adapter on the SQL Server side.



    Friday, September 13, 2019 4:45 PM