Select records from AS400 connection using SQL server table.

Discussion in 'microsoft.public.sqlserver.dts' started by VegaLA, May 10, 2010.

  1. VegaLA

    VegaLA Guest

    Hi all, been working on this problem for a while now but having little
    success finding a satisfactory solution.
    I have a DTS package which contains two connections. one for SQL, the other
    for AS4000. I would like to pull records from the AS400 table using Account
    numbers stored in a SQL table. Up untill now the step between connection 1
    (AS400) and Connection 2 (SQL) was hardcoded as such.

    select *
    from "System"."Libray"."table"
    where Account IN (10, 2000, 33000, 330089, 340077) AND theDate BETWEEN
    20100201 AND 20100301

    Which I then changed to use Global parameters for the Dats so the code now
    looksl ike this:

    select *
    from "System"."Libray"."table"
    where Account IN (10, 2000, 33000, 330089, 340077) AND theDate BETWEEN ? AND ?

    which works well, however, I am trying to do the same to Account. I have
    managed to strign together the Accounts to look like
    '10, 2000, 33000, 330089, 340077' and I have assigned that to a global
    parameter and it looks correct, howev er when i try to run the DTS it falls
    over at the last step with this error message:-

    multi-step ole db operation generated errors

    I suspect i'm going about this the wrong way but can anyone assist me in
    getting this task done as the Account numbers change every month and I don't
    want to have to type them into the DTS package each month when I know they
    exist in a SQL table.

    Thanks in advance,
    VegaLA, May 10, 2010
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.