Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Ali.M.Habib
Yak Posting Veteran
54 Posts |
 Posted - 2010-03-16 : 08:10:16
|
| Dear All,I use the technique be found in http://www.databasejournal.com/features/mssql/article.php/10894_1459181_1/Data-Transformation-Services-DTS.htm to Execute DTS using stored procedurebut the steps DTSDataPumpTask & DTSStep_DTSActiveScriptTask always fail I use the following code EXEC spExecutePKGGlobalVariables @Server='MyServer', @PkgName='MyPackage', @IntSecurity=1my backage consist of the following steps:- delete from table x --> fail- import data from text file to table x -> success - execute vbscript1 --> fail--execute vbscript2 --> fail -- transferdata to table y --> fail any suggestion please |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-03-16 : 11:53:25
|
| modify the package to log to a text file. this will give you verbose error messaging. then post the error message |
 |
|
|
Ali.M.Habib
Yak Posting Veteran
54 Posts |
Posted - 2010-03-17 : 05:48:25
|
quote:
Originally posted by russell
modify the package to log to a text file. this will give you verbose error messaging. then post the error message
Executed as user: FNS\SYSTEM. ...un OnStart: DTSStep_DTSExecuteSQLTask_1 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart: DTSStep_DTSDataPumpTask_2 DTSRun OnError: DTSStep_DTSDataPumpTask_2, Error = -2147467259 (80004005) Error string: Error opening datafile: Access is denied. Error source: Microsoft Data Transformation Services Flat File Rowset Provider Help file: DTSFFile.hlp Help context: 0 Error Detail Records: Error: 5 (5); Provider Error: 5 (5) Error string: Error opening datafile: Access is denied. Error source: Microsoft Data Transformation Services Flat File Rowset Provider Help file: DTSFFile.hlp Help context: 0 DTSRun OnFinish: DTSStep_DTSDataPumpTask_2 Error: -2147220440 (80040428); Provider Error: 0 (0) Error string: Package failed because Step 'DTSStep_DTSDataPumpTask_2' failed. Error source: Microsoft Data Transformation Services (DTS) Pac... Process Exit Code 1. The step failed.how can I execute DTS under certain user |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-03-17 : 09:35:36
|
| You need to give permission on the file to SQL Server service account, since you're using sp_OACreate to do this |
|
|
|
Ali.M.Habib
Yak Posting Veteran
54 Posts |
Posted - 2010-03-18 : 04:46:52
|
quote:
Originally posted by russell
You need to give permission on the file to SQL Server service account, since you're using sp_OACreate to do this
could u give me more deatails about that please |
|
|
|
|
|
|
Failed DTSStep_DTSDataPumpTask & DTSStep_DTSActive