Page 1 of 2 12 LastLast
Results 1 to 15 of 17

Thread: DTS Help

  1. #1
    Sardinka Guest

    DTS Help

    I am transfering the data between the SQL tableand AS400 table. I am using the ActiveX script to transfer the data. The table in SQL have no primary key, but the table in AS400 have a primary key. I need the examples how to handle this. The basic rules are:
    If the primary key is exist->Update
    If the primary key is not found ->Insert.
    Please help me.
    Thanks

  2. #2
    WizKid Guest

    DTS Help (reply)

    Try DTS using Data Driven Query Task (DDQT).
    I'm not familiar with AS400, but if it is from SQL to SQL I would create a stored proc on my destination SQL server and use it in DDQT. If you have a similar facility to create a custom proc in AS400, you might be able to use it.

    good luck..


    ------------
    Sardinka at 10/30/01 10:46:02 AM

    I am transfering the data between the SQL tableand AS400 table. I am using the ActiveX script to transfer the data. The table in SQL have no primary key, but the table in AS400 have a primary key. I need the examples how to handle this. The basic rules are:
    If the primary key is exist->Update
    If the primary key is not found ->Insert.
    Please help me.
    Thanks

  3. #3
    sardinka Guest

    DTS Help (reply)

    Can you give me the examples.
    I created the new dts (DDQT) and I have no idea how to compare the fields and the correct way to write the update and insert based on the compare.
    Thanks in advance


    ------------
    WizKid at 10/31/01 11:40:24 AM

    Try DTS using Data Driven Query Task (DDQT).
    I'm not familiar with AS400, but if it is from SQL to SQL I would create a stored proc on my destination SQL server and use it in DDQT. If you have a similar facility to create a custom proc in AS400, you might be able to use it.

    good luck..


    ------------
    Sardinka at 10/30/01 10:46:02 AM

    I am transfering the data between the SQL tableand AS400 table. I am using the ActiveX script to transfer the data. The table in SQL have no primary key, but the table in AS400 have a primary key. I need the examples how to handle this. The basic rules are:
    If the primary key is exist->Update
    If the primary key is not found ->Insert.
    Please help me.
    Thanks

  4. #4
    WizKid Guest

    DTS Help (reply)

    It is a scenario of moving data beween two SQL server tables
    In a new DTS package:
    Create source and destination connection objects
    Click on "Data Driven Query Task" in the Task controls
    Specify you Source and Destination tables in the corresponding tab-pages. ** (see bottom)
    Tab over to transformation tab-page. (DTS will create an Activ-X script transformation combining all source columns and Dest columns)
    Double-Click on the Transformation Line. It willopenan Active-X script.
    Modity the line in the Script that says "Main = DTSTransformStat_InsertQuery" to "Main = DTSTransformStat_UserQuery" and save it.
    Tab over to Query Tab-Page.
    Select "User" in the "Query Type" drop-down.
    Type your Stored Proc in the Text-Box below. *** (see bottom)
    Click Parse/Show Parameters. This should list the parameters for the stored proc.
    Click OK and run the package.


    As an Example:
    I have two tables My_table1 and My_table2 with two columns in each col1 and col2
    I'm moving data from My_table1 to My_table2. If col1 exists in My_table2, update it; else insert.
    Here is my proc (This proc needs to be on the Destination) :
    create procedure My_proc
    (@col1 int, @col2 varchar(30) )
    as

    if exists ( select 'x' from my_table2 where col1 = @col1 )
    update my_table2 set col2 = @col2 where col1 = @col1
    else
    insert into my_table2 values ( @col1, @col2 )



    ** What you select in you source query depends on what your stored proc requires.
    "My_Proc" requires two parameters @col1 and @col2.
    So my Source query is Select col1, col2 from My_Table1

    ***The way to call stored-proc in the Query tab-page is "My_proc ?, ?" (This proc needs to be on the Destination)


    -- Hope it helps


    ------------
    sardinka at 10/31/01 4:53:56 PM

    Can you give me the examples.
    I created the new dts (DDQT) and I have no idea how to compare the fields and the correct way to write the update and insert based on the compare.
    Thanks in advance


    ------------
    WizKid at 10/31/01 11:40:24 AM

    Try DTS using Data Driven Query Task (DDQT).
    I'm not familiar with AS400, but if it is from SQL to SQL I would create a stored proc on my destination SQL server and use it in DDQT. If you have a similar facility to create a custom proc in AS400, you might be able to use it.

    good luck..


    ------------
    Sardinka at 10/30/01 10:46:02 AM

    I am transfering the data between the SQL tableand AS400 table. I am using the ActiveX script to transfer the data. The table in SQL have no primary key, but the table in AS400 have a primary key. I need the examples how to handle this. The basic rules are:
    If the primary key is exist->Update
    If the primary key is not found ->Insert.
    Please help me.
    Thanks

  5. #5
    sardinka Guest

    DTS Help (reply)

    Thank for all your help.
    I have one more question:
    Is it any way to do comparasing statement in ActiveX transformation script?
    I already try some,but it's not working so I am not sure if this correct.
    If so. Do you have some examples? Thanks again
    My Examples:
    Dim SQL
    Dim AS400
    SQL=DTSSourse("columnname1&#34
    Genelco="Select columnname from table where columnname1= '" & SQL & "' "
    ...
    If exists...
    Then
    update
    else
    ineert
    end if

  6. #6
    WizKid Guest

    DTS Help (reply)

    Wow!! You kept me thinking.. I'm a SQL DBA and not very good at VB Script. So I usually use stored procs. How ever, I tried to do it in Active X script and just got it to work..

    Here it is..
    Source : MY_Table1 (Col1 int primary key, col2 char(4), col3 char(4))
    Destination : My_Table2 (Col1 int primary key, col2 char(4))
    Transformation : If-Col1-Exists-Update-else-Insert
    My_Table1.Col1 = My_Table1.Col1
    My_Table1.Col2 = My_Table1.Col2 + My_Table1.Col3


    I used two data sources and a ODBC DSN. (I did NOT use Data Driven Query Task)

    Define transformation between the Source and Destination.
    Tab-over to "Transformations" tab page. Remove all default transformations.
    Highlight all source and destination columns
    From "New Transformation" drop-down, select "ActiveX Script" and click NEW.
    In AX Script Properties type your ActiveX code.(Below is the code for my example)

    Function Main()
    Set cmd = CreateObject("ADODB.Command&#34
    Set rs = CreateObject("adodb.recordset&#34
    rs.CursorLocation = 3
    cmd.ActiveConnection = "dsn=LocalServer;uid=sa;pwd=password;"
    cmd.CommandType = 1
    CMD_STR = "select count(*) my_count from My_table2 where col1 = " & DTSSource("col1&#34
    cmd.CommandText = CMD_STR
    rs.Open cmd, , 1,1

    If rs.fields("my_count&#34.value > 0 then
    rs.close
    CMD_STR = "update My_Table2 set col2 = '" & DTSSource("col2&#34 & DTSSource("col3&#34 &"' where col1 = " & DTSSource("col1&#34
    cmd.CommandText = CMD_STR
    rs.Open cmd, , 1,1
    Main = DTSTransformStat_SkipInsert
    else
    DTSDestination("col1&#34 = DTSSource("col1&#34
    DTSDestination("col2&#34 = DTSSource("col2&#34 & DTSSource("col3&#34
    Main = DTSTransformStat_OK
    end if
    End Function

    .. GOOD LUCK !!!



    ------------
    sardinka at 11/1/01 3:55:03 PM

    Thank for all your help.
    I have one more question:
    Is it any way to do comparasing statement in ActiveX transformation script?
    I already try some,but it's not working so I am not sure if this correct.
    If so. Do you have some examples? Thanks again
    My Examples:
    Dim SQL
    Dim AS400
    SQL=DTSSourse("columnname1&#34
    Genelco="Select columnname from table where columnname1= '" & SQL & "' "
    ...
    If exists...
    Then
    update
    else
    ineert
    end if

  7. #7
    sardinka Guest

    DTS Help (reply)

    Thanks. I'll try it today. I have one question nothing to do with DTS.
    How can I compare 2 table(with PK) not using the join.
    Basic idea like this:select distinct from table1, select distinct from table2
    Compare the Primary key on both tables.
    Thanks

  8. #8
    WizKid Guest

    DTS Help (reply)

    Sorry I lost you. I'm not sure what you are trying to do. If you have a comparable primary keys in both the tables, why not join them? Again, we might need an example here :-)


    ------------
    sardinka at 11/2/01 9:56:34 AM

    Thanks. I'll try it today. I have one question nothing to do with DTS.
    How can I compare 2 table(with PK) not using the join.
    Basic idea like this:select distinct from table1, select distinct from table2
    Compare the Primary key on both tables.
    Thanks

  9. #9
    sardinka Guest

    DTS Help (reply)

    I need to compare 2 tables. If record exist (based on primary key) in one table and not exist in the second table(based on primary key) delete from table. Both tables need to have the same primary key.Let me know if my explanation fine.
    Thanks

  10. #10
    WizKid Guest

    DTS Help (reply)

    delete from My_Table1 where My_Table_Key not in (Select My_Table_Key from My_Table2)
    OR
    delete from My_table1
    where not exists (select 'x' from My_table2 where My_Table1.My_Table_Key = My_table2.My_Table_Key)

    The former works for simple primary key only and the later works for composite keys as well. Repeat the same by interchanging the table names.

    ------------
    sardinka at 11/2/01 11:21:11 AM

    I need to compare 2 tables. If record exist (based on primary key) in one table and not exist in the second table(based on primary key) delete from table. Both tables need to have the same primary key.Let me know if my explanation fine.
    Thanks

  11. #11
    sardinka Guest

    DTS Help (reply)

    Hi...This is my query I am running:
    I am trying to print the row number and I can't see where is my err. Help me

    declare @row_count int
    SET @row_count = 0
    SET @row_count = @row_count + 1
    delete from table1 where not exists
    (select num,id
    from table2 where table1.num = table2.num and
    table1.id = table2.id
    print "row" + convert(varchar,@row_count))
    Thanks

  12. #12
    sardinka Guest

    DTS Help (reply)

    Just some more information.
    Each tables has around 2 million records.

  13. #13
    WizKid Guest

    DTS Help (reply)

    declare @row_count int
    delete from table1 where not exists
    (select num,id
    from table2 where table1.num = table2.num and
    table1.id = table2.id
    select @row_count = @@rowcount
    print "row" + convert(varchar(12),@row_count))


    ------------
    sardinka at 11/2/01 12:27:59 PM

    Hi...This is my query I am running:
    I am trying to print the row number and I can't see where is my err. Help me

    declare @row_count int
    SET @row_count = 0
    SET @row_count = @row_count + 1
    delete from table1 where not exists
    (select num,id
    from table2 where table1.num = table2.num and
    table1.id = table2.id
    print "row" + convert(varchar,@row_count))
    Thanks

  14. #14
    sardinka Guest

    DTS Help (reply)

    When I check the syntax I am getting this err:
    Server: Msg 156, Level 15, State 1, Line 6
    Incorrect syntax near the keyword 'select'.
    Server: Msg 170, Level 15, State 1, Line 7
    Line 7: Incorrect syntax near &#39'.

  15. #15
    WizKid Guest

    DTS Help (reply)

    OOPS!!

    declare @row_count int
    delete from table1 where not exists
    (select 'x'
    from table2 where table1.num = table2.num and
    table1.id = table2.id )
    select @row_count = @@rowcount
    print "row" + convert(varchar(12),@row_count)


    ------------
    sardinka at 11/2/01 1:56:10 PM

    When I check the syntax I am getting this err:
    Server: Msg 156, Level 15, State 1, Line 6
    Incorrect syntax near the keyword 'select'.
    Server: Msg 170, Level 15, State 1, Line 7
    Line 7: Incorrect syntax near &#39'.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •