Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2003
    Posts
    123

    Question Unanswered: Converting DTS to Stored procedure

    Hi,

    I created a DTS package for importing a textfile, parse the data and insert it into some tables.

    I was planning to start this from a stored procedure so I could reach it from the outside... It turns out I don't have (and won't get) the permissions to do this...

    Now I have to convert the package into a stored procedure instead.
    SQL Querys are no problem but the importing of the textfile is.
    How do I do it?

    First step is to import only row 0 to a table, second step is to import row 1 -> n

    Any examples would be nice... :-)

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    bcp or bulk insert...

    what's row 0?

    Probably a header...

    Can you create a "work" table for your own use?

    How many rows are we talking about?

    Where is the data coming from (it's Access isn't...grrrrrr)

    What do you mean by parse (SUBSTRING)?

    Does your dog have fleas?

    Why is the sky blue?

    What's the name of your dba....(just kidding)

    Tell us what you're trying to do, what you can expect you can access to(not demanding to ask a dba for a work table), and I'm sure we can figure something out...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Jul 2003
    Posts
    123
    Whats bcp?

    The file contains a header in the first row and then tab separated data...

    I have a rawtable that it is imported to now by DTS.

    It's about 20000 rows, textfile...

    By parsing i mean several queries that translates the data from the rawtable into other tables.

    And no, my dog (Staffordshire Bullterrier, "Kim") does not have any fleas...

    The sky is blue beacuse God have his blue underwear on...

    My dba is named Sven.

    ----

    I have tried to use xp_cmdshell but I'm not allowed too...

    Seems like I have to do the import in my client.. written in C++.

  4. #4
    Join Date
    Nov 2003
    Location
    Singapore
    Posts
    3

    Post Re: Converting DTS to Stored procedure

    Hi,

    You could try using the stored proceedure below to execute yr DTS, if the DTS is within the same DB then it shd work fine .... Hope this helps.



    CREATE PROC dbo.DTSExecutePKG
    @Server varchar(255),
    @PkgName varchar(255), -- Package Name (Defaults to most recent version)
    @ServerPWD varchar(255) = Null, -- Server Password if using SQL Security to load Package (UID is SUSER_NAME())
    @IntSecurity bit = 0, -- 0 = SQL Server Security, 1 = Integrated Security
    @PkgPWD varchar(255) = '' -- Package Password
    AS
    SET NOCOUNT ON
    /*
    Return Values
    - 0 Successfull execution of Package
    - 1 OLE Error
    - 9 Failure of Package
    */
    DECLARE @hr int, @ret int, @oPKG int, @Cmd varchar(1000)
    -- Create a Pkg Object
    EXEC @hr = sp_OACreate 'DTS.Package', @oPKG OUTPUT

    IF @hr <> 0
    BEGIN
    PRINT '*** Create Package object failed'
    RETURN 1
    END

    -- Evaluate Security and Build LoadFromSQLServer Statement
    IF @IntSecurity = 0
    SET @Cmd = 'LoadFromSQLServer("' + @Server +'", "' + SUSER_SNAME() + '", "' + @ServerPWD + '", 0, "' + @PkgPWD + '", , , "' + @PkgName + '")'
    ELSE
    SET @Cmd = 'LoadFromSQLServer("' + @Server +'", "", "", 256, "' + @PkgPWD + '", , , "' + @PkgName + '")'

    EXEC @hr = sp_OAMethod @oPKG, @Cmd, NULL

    IF @hr <> 0
    BEGIN
    PRINT '*** LoadFromSQLServer failed'
    RETURN 1
    END

    -- Execute Pkg
    EXEC @hr = sp_OAMethod @oPKG, 'Execute'

    IF @hr <> 0
    BEGIN
    PRINT '*** Execute failed'
    RETURN 1
    END


    -- Unitialize the Pkg
    EXEC @hr = sp_OAMethod @oPKG, 'UnInitialize'

    IF @hr <> 0
    BEGIN
    PRINT '*** UnInitialize failed'
    RETURN 1
    END

    -- Clean Up
    EXEC @hr = sp_OADestroy @oPKG

    IF @hr <> 0
    BEGIN
    RETURN 1
    END

    GO

  5. #5
    Join Date
    Jul 2003
    Posts
    123
    Got a:

    Server: Msg 229, Level 14, State 5, Procedure sp_OACreate, Line 17
    EXECUTE permission denied on object 'sp_OACreate', database 'master', owner 'dbo'.
    *** Create Package object failed

    -----

    Seems like I have no permission to run sp_OACreate either...

    Whats the point of having a flashy database if you're not allowed to use all of its finesses? Grrr.

Posting Permissions

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