Problem trying to run dts using T-sql automation

[Crowley16]

Hi everyone.

I've got a dts, which first drops/creates a set of tables, and then imports data into these tables from an access database.

Everything works fine if I run it manually from EM, however I want to script this out so have been trying to execute the package using t-sql automation objects...

here's the code:

DECLARE @HR INT, @PACKAGE INT

--create a ole object of type dts package
EXECUTE @HR = sp_OACreate 'DTS.Package', @PACKAGE OUT
IF @HR <> 0 BEGIN	--check if package created successfully
	RAISERROR('Failed to create DTS Package Object', 15, 1)
	RETURN
END

--run the DTS package into the created object
--DTSSQLServerStorageFlags: DTSSQLStgFlag_Default = 0, DTSSQLStgFlag_UseTrustedConnection = 256 
EXEC @HR = sp_OAMethod 
	@PACKAGE, 
	'LoadFromSQLServer',
	NULL, 
	@ServerName='SEXPLT', 
	@PackageName='SOL_EX_IMPORT',
	@Flags=256
IF @HR <> 0 BEGIN	--check if package has been loaded correctly
	RAISERROR('Failed to load DTS Package', 15, 1)
	RETURN
END

--execute the package
EXEC @HR = sp_OAMethod @PACKAGE, 'Execute'

--cleaning up
EXEC @HR = sp_OADestroy @PACKAGE
IF @HR <> 0 BEGIN
	RAISERROR('Failed to destroy DTS Package', 15, 1)
	RETURN
END
GO

when I run this, everything seems to be fine, and I don't get any error messages, however none of the import tasks have run because all my tables are empty.
The drop/create table tasks do run though because when I run this, data in the tables do all get cleared.

any ideas where I'm going wrong?

Thanks

--------------------
Procrastinate Now!

 

[Crowley16]

I've had a closer look and from the logs, it seems that when running the dts using sp_OAMethod, the system can't seem to open the access file, saying either the database is opened exclusively, or I don't have permission...

so, why is this happening when I'm running it from code, but not happening when I'm running it from EM?

and how do I fix it?

--------------------
Procrastinate Now!

 

[Crowley16]

Ok, it seems like that this is a permissions across the network issue, because when I copy the access file to the local computer, then the thing runs fine...

so, this is ok as a work around, however, does anyone know how to get this to work with the file on the network?

--------------------
Procrastinate Now!

 

[AlexCuse]

First thing I would try is running it from T-SQL using your login and password (rather than a trusted connection). I have never run a package from T-SQL (neat trick, by the way) but I have run into problems like that when trying to use trusted connection or windows authentication, because some people using the app I was calling it from did not have access to the drive needed.

I am a complete novice when it comes to the security side of things though, so I am not sure if that was REALLY the problem (but it has worked OK so far -- touch wood ;-) )

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[Crowley16]

problem is, if I use the network login and password, then that doesn't have access to the server...

I really need to be able to specify both sets of accounts, the sql server account and the windows account.

I have now added the account used to run sql server onto the network folder and it runs fine.

so it seems that em uses the local windows login to run the dts whereas oa uses the account used to run sql server.

--------------------
Procrastinate Now!

 

[AlexCuse]

so it seems that em uses the local windows login to run the dts whereas oa uses the account used to run sql server.

Precisely. There are often problems like this when trying to schedule DTS packages as jobs as well, because of permissions on the SQL Server Agent account.

Glad you got it working

Alex

Ignorance of certain subjects is a great part of wisdom