SQL Server : Scheduled DTS Package failing

Kai

Hi,

I have a DTS package setup which drops and recreates a number of tables and then imports Data from a seperate DB into the newly created tables.

Now when i run the Package myself in EM it executes without problem, but when i schedule the job it fails every time.

It gives error messages like

DTSRun:  Loading...
DTSRun:  Executing...
DTSRun OnStart:  Drop table [Mike].[dbo].[Mike_Sheet_Answers] Step
DTSRun OnStart:  Drop table [Mike].[dbo].[Mike_Sheet_Headers] Step
DTSRun OnStart:  Drop table [Mike].[dbo].[Mike_Sheet_Questions] Step
DTSRun OnStart:  Drop table [Mike].[dbo].[Mike_Sheet_Sections] Step
DTSRun OnFinish:  Drop table [Mike].[dbo].[Mike_Sheet_Answers] Step
DTSRun OnStart:  Create Table [Mike].[dbo].[Mike_Sheet_Answers] Step
DTSRun OnFinish:  Drop table [Mike].[dbo].[Mike_Sheet_Headers] Step
DTSRun OnStart:  Create Table [Mike].[dbo].[Mike_Sheet_Headers] Step
DTSRun OnFinish:  Drop table [Mike].[dbo].[Mike_Sheet_Questions] Step
DTSRun OnStart:  Create Table [Mike].[dbo].[Mike_Sheet_Questions] Step
DTSRun OnFinish:  Drop table [Mike].[dbo].[Mike_Sheet_Sections] Step
DTSRun OnStart:  Create Table [Mike].[dbo].[Mike_Sheet_Sections] Step
DTSRun OnFinish:  Create Table [Mike].[dbo].[Mike_Sheet_Answers] Step
DTSRun OnStart:  Copy Data from Mike_Sheet_Answers to [Mike].[dbo].[Mike_Sheet_Answers] Step
DTSRun OnError:  Copy Data from Mike_Sheet_Answers to [Mike].[dbo].[Mike_Sheet_Answers] Step, Error = -2147467259 (80004005)
   Error string:  [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
   Error source:  Microsoft OLE DB Provider for ODBC Drivers

Anyone have any ideas ?

dan_pretty_boy

Hi,

It seems that the DTS manager is unable to find the DSN.
Check windows/settings/control panel/administrative tools/Data Sources (ODBC)

Check that there is the DSN in there, because thats were the DTS Manager looks for the DataSource.

What type of databases are you importing with the DTS.


Regards
Danny

bonkey

Most probably, the DSN (ODBC Data Source Name) you chose is one thats defined under "User DSNs" as opposed to under "System DSNs" in the ODBC control panel.

User DSNs are only available to the logged in user....so when it runs as a scheduled task, it can't find it.

So...make sure you use a system- or file-based DSN instead.

jc

Kai

bonkey wrote:

Most probably, the DSN (ODBC Data Source Name) you chose is one thats defined under "User DSNs" as opposed to under "System DSNs" in the ODBC control panel.

User DSNs are only available to the logged in user....so when it runs as a scheduled task, it can't find it.

So...make sure you use a system- or file-based DSN instead.

jc

Bingo !

Thanks for that.

bonkey

No problem.

jc