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... :-)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...|||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++.|||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|||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.