Professional Web Applications Themes

Executing DTS based on the results of a query. - Microsoft SQL / MS SQL Server

I am a newbie to SQL 2000 and am running into a problem. We have files that we import every month. I want to automate the importing of these files. Previously, this was done via Access, but I have been asked that I do the same thing directly through SQL 2000. I know how to import a file via a DTS package, but I do not want to import a file twice by mistake. In Access this was prevented by keeping a log of imported files in a table. Before an import was done, the table would be checked to ...

  1. #1

    Default Executing DTS based on the results of a query.

    I am a newbie to SQL 2000 and am running into a problem. We have
    files that we import every month. I want to automate the importing of
    these files. Previously, this was done via Access, but I have been
    asked that I do the same thing directly through SQL 2000. I know how
    to import a file via a DTS package, but I do not want to import a file
    twice by mistake. In Access this was prevented by keeping a log of
    imported files in a table. Before an import was done, the table would
    be checked to see if an entry existed for that file already. If so,
    the system would give the user the opportunity to either have it
    delete the old data or to quit the import all together. I thought to
    do the same thing in SQL 2000 but since the only workflow options for
    the package are success, failure, and completion, the only way I have
    found to stop an import based on a query result would be to raise an
    error and therefore create a failure. Is there a better way?

    Also, can you call a DTS package from a stored procedure or Query?

    Thanks,
    Ryan
    Ryan Guest

  2. #2

    Default Re: Executing DTS based on the results of a query.

    Ryan
    Yes, you can run DTS in stored procedure

    declare packagename varchar(255) --package name, gets most recent version
    declare userpwd varchar(255) --login pwd
    declare intsecurity bit --use non-zero to indicate integrated security
    declare pkgPwd varchar(255) --package password
    declare hr int
    declare object int

    set packagename='workers'
    set userpwd=null
    set pkgPwd=''
    set intsecurity=0

    --create a package object
    EXEC hr = sp_OACreate 'DTS.Package', object OUTPUT
    if hr <> 0
    Begin
    EXEC sp_displayoaerrorinfo object --, hr
    RETURN
    end


    declare svr varchar(15)
    declare login varchar(15)
    select login = SUSER_NAME()
    select svr = servername
    declare flag int
    select flag = 256


    EXEC hr = sp_OAMethod object,
    'LoadFromSqlServer',NULL, ServerName=svr, ServerUserName=login,
    PackageName=packagename, Flags=flag, PackagePassword = pkgPwd

    EXEC hr = sp_OAMethod object, 'Execute'
    IF hr <> 0
    BEGIN
    print 'Execute failed'
    EXEC sp_displayoaerrorinfo object --, hr
    RETURN
    END


    EXEC hr = sp_OADestroy object
    IF hr <> 0
    BEGIN
    PRINT '*** Destroy Package failed'
    EXEC sp_displayoaerrorinfo object, hr
    RETURN
    END
    GO

    "Ryan" <org> wrote in message
    news:google.com... 


    Uri Guest

  3. #3

    Default Re: Executing DTS based on the results of a query.

    Thank you! I tried using the code as written changing packagename to
    the name of a simple package I wrote: test2. I saved the procedure as
    sp_test. I then typed

    execute sp_test

    in the Queryyzer to test and did not meet with success:

    The package "test2" I implemented in two different ways to test:

    The first was a simple ActiveX script:

    msgbox("hello")
    Main = DTSTaskExecResult_Success

    The second attempt:

    A transfer of a text file to a table.

    In Case1 - The result was that the query just ran and ran
    In Case2 - The query said it completed successfully but did do the
    data transfer

    If I deleted test2 altogether I got the error message:

    could not find sp_displayoaerror or somthing similar.

    Do you what I might be doing wrong?

    Note: Both implementations of test2 worked when executed directly.

    Thanks again,
    Ryan
    "Uri Dimant" <co.il> wrote in message news:<phx.gbl>... [/ref]
    Ryan Guest

  4. #4

    Default Re: Executing DTS based on the results of a query.

    Thank you! I tried using the code as written above changing packagename to
    the name of a simple package I wrote: test2. I saved the procedure as
    sp_test. I then typed

    execute sp_test

    in the Queryyzer to test and did not meet with success:

    The package "test2" I implemented in two different ways to test:

    The first was a simple ActiveX script:

    msgbox("hello")
    Main = DTSTaskExecResult_Success

    The second attempt:

    A transfer of a text file to a table.

    In Case1 - The result was that the query just ran and ran
    In Case2 - The query said it completed successfully but did do the
    data transfer

    If I deleted test2 altogether I got the error message:

    could not find sp_displayoaerror or somthing similar.

    Do you what I might be doing wrong?

    Note: Both implementations of test2 worked when executed directly.

    Thanks again,
    Ryan
    Ryan Guest

Similar Threads

  1. Complex join = no results (for a query that shouldreturn results)
    By jchapman16 in forum Coldfusion Database Access
    Replies: 4
    Last Post: August 23rd, 10:49 PM
  2. Query results don't display properly in results table.IGNORE PREVIOUS
    By JoyRose in forum Coldfusion - Advanced Techniques
    Replies: 1
    Last Post: March 24th, 07:28 PM
  3. Query results don't display properly in results table.
    By JoyRose in forum Coldfusion - Advanced Techniques
    Replies: 0
    Last Post: March 24th, 07:17 PM
  4. Replies: 3
    Last Post: July 17th, 11:58 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139