dbTalk Databases Forums  

Help Newbie - SSIS run in SQL server agent job

Go Back dbTalk Databases Forums Databases microsoft.public.sqlserver.dts Help Newbie - SSIS run in SQL server agent job

microsoft.public.sqlserver.dts microsoft.public.sqlserver.dts


Discuss Help Newbie - SSIS run in SQL server agent job in the microsoft.public.sqlserver.dts forum.



Reply
Page 3 of 3 < 1 2 3
 
Thread Tools Display Modes
  #21  
Old   
Irvine, Dennis
 
Posts: n/a

Default Re: Help Newbie - SSIS run in SQL server agent job - 07-07-2008 , 04:51 PM





Update. It appears to have been a "permissions" issue after all.

I clicked on the "database server" instance and went to properties. Under
"permissions" I highlighted the special account I had created and then
checked the box for special permissions titled "Alter connections"
when I did this,, my package ran just fine.

I assumed, since my special account had Domain Admin priveliges and
Sysadmin privleges, that it could do anything. It appears, to me
anyway, that there are some privileges that have to be specifically granted.

Hope that helps someone else.

Dennis


"Irvine, Dennis" <dennis.irvine (AT) plumasnt (DOT) com> wrote

Quote:
I have a SSIS package that works fine when run inside Visual Studio.
It runs fine when run using the DTEXEC command in a cmd window.
It runs fine even when run in a powershell window using DTEXEC

It will NOT run as a job in SQL Server agent. I get this mess shown
below. As you can see I get ODBC errors and validation errors. None of
those show up anywhere else.

Can someone give me a hint here. I have been fooling around with this
for over a week trying to get this package to run in SQL server agent job
mode.
I have tried running it as an Integration package job and also as a
operating system job. They both end up as shown below.
The ODBC errors come from a third party proprietery ODBC driver that
accesses a proprietary database. But it works everywhere except as an
SQL agent job.

Thanks for any suggestions
Dennis


Microsoft (R) SQL Server Execute Package Utility
Version 9.00.3042.00 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.

Started: 2:00:15 PM
Info: 2008-07-03 14:00:19.00
Code: 0x4004300A
Source: COPY BOX DATA DTS.Pipeline
Description: Validation phase is beginning.
End Info
Error: 2008-07-03 14:00:19.14
Code: 0xC0047062
Source: COPY BOX DATA COREDIR BOX DATA [1]
Description: System.Data.Odbc.OdbcException: ERROR [S1000] [TOD][ODBC
Driver]Invalid connect syntax (uid/pwd/datasource)
ERROR [01000] [Microsoft][ODBC Driver Manager] The driver doesn't support
the version of ODBC behavior that the application requested (see
SQLSetEnvAttr).
at System.Data.Odbc.OdbcConnection.HandleError(OdbcHa ndle hrHandle,
RetCode retcode)
at System.Data.Odbc.OdbcConnectionHandle..ctor(OdbcCo nnection
connection, OdbcConnectionString constr, OdbcEnvironmentHandle
environmentHandle)
at
System.Data.Odbc.OdbcConnectionFactory.CreateConne ction(DbConnectionOptions
options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection
owningObject)
at
System.Data.ProviderBase.DbConnectionFactory.Creat eNonPooledConnection(DbConnection
owningConnection, DbConnectionPoolGroup poolGroup)
at
System.Data.ProviderBase.DbConnectionFactory.GetCo nnection(DbConnection
owningConnection)
at
System.Data.ProviderBase.DbConnectionClosed.OpenCo nnection(DbConnection
outerConnection, DbConnectionFactory connectionFactory)
at System.Data.Odbc.OdbcConnection.Open()
at
Microsoft.SqlServer.Dts.Runtime.ManagedHelper.GetM anagedConnection(String
assemblyQualifiedName, String connStr, Object transaction)
at
Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnec tionManager90.AcquireConnection(Object
pTransaction)
at
Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceA dapter.AcquireConnections(Object
transaction)
at
Microsoft.SqlServer.Dts.Pipeline.ManagedComponentH ost.HostAcquireConnections(IDTSManagedComponentWra pper90
wrapper, Object transaction)
End Error
Error: 2008-07-03 14:00:19.14
Code: 0xC0047017
Source: COPY BOX DATA DTS.Pipeline
Description: component "COREDIR BOX DATA" (1) failed validation and
returned error code 0x80131937.
End Error
Error: 2008-07-03 14:00:19.14
Code: 0xC004700C
Source: COPY BOX DATA DTS.Pipeline
Description: One or more component failed validation.
End Error
Error: 2008-07-03 14:00:19.14
Code: 0xC0024107
Source: COPY BOX DATA
Description: There were errors during task validation.
End Error
DTExec: The package execution returned DTSER_FAILURE (1).
Started: 2:00:15 PM
Finished: 2:00:19 PM
Elapsed: 3.25 seconds


Reply With Quote
  #22  
Old   
Irvine, Dennis
 
Posts: n/a

Default Re: Help Newbie - SSIS run in SQL server agent job - 07-07-2008 , 04:51 PM





Update. It appears to have been a "permissions" issue after all.

I clicked on the "database server" instance and went to properties. Under
"permissions" I highlighted the special account I had created and then
checked the box for special permissions titled "Alter connections"
when I did this,, my package ran just fine.

I assumed, since my special account had Domain Admin priveliges and
Sysadmin privleges, that it could do anything. It appears, to me
anyway, that there are some privileges that have to be specifically granted.

Hope that helps someone else.

Dennis


"Irvine, Dennis" <dennis.irvine (AT) plumasnt (DOT) com> wrote

Quote:
I have a SSIS package that works fine when run inside Visual Studio.
It runs fine when run using the DTEXEC command in a cmd window.
It runs fine even when run in a powershell window using DTEXEC

It will NOT run as a job in SQL Server agent. I get this mess shown
below. As you can see I get ODBC errors and validation errors. None of
those show up anywhere else.

Can someone give me a hint here. I have been fooling around with this
for over a week trying to get this package to run in SQL server agent job
mode.
I have tried running it as an Integration package job and also as a
operating system job. They both end up as shown below.
The ODBC errors come from a third party proprietery ODBC driver that
accesses a proprietary database. But it works everywhere except as an
SQL agent job.

Thanks for any suggestions
Dennis


Microsoft (R) SQL Server Execute Package Utility
Version 9.00.3042.00 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.

Started: 2:00:15 PM
Info: 2008-07-03 14:00:19.00
Code: 0x4004300A
Source: COPY BOX DATA DTS.Pipeline
Description: Validation phase is beginning.
End Info
Error: 2008-07-03 14:00:19.14
Code: 0xC0047062
Source: COPY BOX DATA COREDIR BOX DATA [1]
Description: System.Data.Odbc.OdbcException: ERROR [S1000] [TOD][ODBC
Driver]Invalid connect syntax (uid/pwd/datasource)
ERROR [01000] [Microsoft][ODBC Driver Manager] The driver doesn't support
the version of ODBC behavior that the application requested (see
SQLSetEnvAttr).
at System.Data.Odbc.OdbcConnection.HandleError(OdbcHa ndle hrHandle,
RetCode retcode)
at System.Data.Odbc.OdbcConnectionHandle..ctor(OdbcCo nnection
connection, OdbcConnectionString constr, OdbcEnvironmentHandle
environmentHandle)
at
System.Data.Odbc.OdbcConnectionFactory.CreateConne ction(DbConnectionOptions
options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection
owningObject)
at
System.Data.ProviderBase.DbConnectionFactory.Creat eNonPooledConnection(DbConnection
owningConnection, DbConnectionPoolGroup poolGroup)
at
System.Data.ProviderBase.DbConnectionFactory.GetCo nnection(DbConnection
owningConnection)
at
System.Data.ProviderBase.DbConnectionClosed.OpenCo nnection(DbConnection
outerConnection, DbConnectionFactory connectionFactory)
at System.Data.Odbc.OdbcConnection.Open()
at
Microsoft.SqlServer.Dts.Runtime.ManagedHelper.GetM anagedConnection(String
assemblyQualifiedName, String connStr, Object transaction)
at
Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnec tionManager90.AcquireConnection(Object
pTransaction)
at
Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceA dapter.AcquireConnections(Object
transaction)
at
Microsoft.SqlServer.Dts.Pipeline.ManagedComponentH ost.HostAcquireConnections(IDTSManagedComponentWra pper90
wrapper, Object transaction)
End Error
Error: 2008-07-03 14:00:19.14
Code: 0xC0047017
Source: COPY BOX DATA DTS.Pipeline
Description: component "COREDIR BOX DATA" (1) failed validation and
returned error code 0x80131937.
End Error
Error: 2008-07-03 14:00:19.14
Code: 0xC004700C
Source: COPY BOX DATA DTS.Pipeline
Description: One or more component failed validation.
End Error
Error: 2008-07-03 14:00:19.14
Code: 0xC0024107
Source: COPY BOX DATA
Description: There were errors during task validation.
End Error
DTExec: The package execution returned DTSER_FAILURE (1).
Started: 2:00:15 PM
Finished: 2:00:19 PM
Elapsed: 3.25 seconds


Reply With Quote
  #23  
Old   
Irvine, Dennis
 
Posts: n/a

Default Re: Help Newbie - SSIS run in SQL server agent job - 07-07-2008 , 04:51 PM


Update. It appears to have been a "permissions" issue after all.

I clicked on the "database server" instance and went to properties. Under
"permissions" I highlighted the special account I had created and then
checked the box for special permissions titled "Alter connections"
when I did this,, my package ran just fine.

I assumed, since my special account had Domain Admin priveliges and
Sysadmin privleges, that it could do anything. It appears, to me
anyway, that there are some privileges that have to be specifically granted.

Hope that helps someone else.

Dennis


"Irvine, Dennis" <dennis.irvine (AT) plumasnt (DOT) com> wrote

Quote:
I have a SSIS package that works fine when run inside Visual Studio.
It runs fine when run using the DTEXEC command in a cmd window.
It runs fine even when run in a powershell window using DTEXEC

It will NOT run as a job in SQL Server agent. I get this mess shown
below. As you can see I get ODBC errors and validation errors. None of
those show up anywhere else.

Can someone give me a hint here. I have been fooling around with this
for over a week trying to get this package to run in SQL server agent job
mode.
I have tried running it as an Integration package job and also as a
operating system job. They both end up as shown below.
The ODBC errors come from a third party proprietery ODBC driver that
accesses a proprietary database. But it works everywhere except as an
SQL agent job.

Thanks for any suggestions
Dennis


Microsoft (R) SQL Server Execute Package Utility
Version 9.00.3042.00 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.

Started: 2:00:15 PM
Info: 2008-07-03 14:00:19.00
Code: 0x4004300A
Source: COPY BOX DATA DTS.Pipeline
Description: Validation phase is beginning.
End Info
Error: 2008-07-03 14:00:19.14
Code: 0xC0047062
Source: COPY BOX DATA COREDIR BOX DATA [1]
Description: System.Data.Odbc.OdbcException: ERROR [S1000] [TOD][ODBC
Driver]Invalid connect syntax (uid/pwd/datasource)
ERROR [01000] [Microsoft][ODBC Driver Manager] The driver doesn't support
the version of ODBC behavior that the application requested (see
SQLSetEnvAttr).
at System.Data.Odbc.OdbcConnection.HandleError(OdbcHa ndle hrHandle,
RetCode retcode)
at System.Data.Odbc.OdbcConnectionHandle..ctor(OdbcCo nnection
connection, OdbcConnectionString constr, OdbcEnvironmentHandle
environmentHandle)
at
System.Data.Odbc.OdbcConnectionFactory.CreateConne ction(DbConnectionOptions
options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection
owningObject)
at
System.Data.ProviderBase.DbConnectionFactory.Creat eNonPooledConnection(DbConnection
owningConnection, DbConnectionPoolGroup poolGroup)
at
System.Data.ProviderBase.DbConnectionFactory.GetCo nnection(DbConnection
owningConnection)
at
System.Data.ProviderBase.DbConnectionClosed.OpenCo nnection(DbConnection
outerConnection, DbConnectionFactory connectionFactory)
at System.Data.Odbc.OdbcConnection.Open()
at
Microsoft.SqlServer.Dts.Runtime.ManagedHelper.GetM anagedConnection(String
assemblyQualifiedName, String connStr, Object transaction)
at
Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnec tionManager90.AcquireConnection(Object
pTransaction)
at
Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceA dapter.AcquireConnections(Object
transaction)
at
Microsoft.SqlServer.Dts.Pipeline.ManagedComponentH ost.HostAcquireConnections(IDTSManagedComponentWra pper90
wrapper, Object transaction)
End Error
Error: 2008-07-03 14:00:19.14
Code: 0xC0047017
Source: COPY BOX DATA DTS.Pipeline
Description: component "COREDIR BOX DATA" (1) failed validation and
returned error code 0x80131937.
End Error
Error: 2008-07-03 14:00:19.14
Code: 0xC004700C
Source: COPY BOX DATA DTS.Pipeline
Description: One or more component failed validation.
End Error
Error: 2008-07-03 14:00:19.14
Code: 0xC0024107
Source: COPY BOX DATA
Description: There were errors during task validation.
End Error
DTExec: The package execution returned DTSER_FAILURE (1).
Started: 2:00:15 PM
Finished: 2:00:19 PM
Elapsed: 3.25 seconds


Reply With Quote
  #24  
Old   
Irvine, Dennis
 
Posts: n/a

Default Re: Help Newbie - SSIS run in SQL server agent job - 07-07-2008 , 04:51 PM


Update. It appears to have been a "permissions" issue after all.

I clicked on the "database server" instance and went to properties. Under
"permissions" I highlighted the special account I had created and then
checked the box for special permissions titled "Alter connections"
when I did this,, my package ran just fine.

I assumed, since my special account had Domain Admin priveliges and
Sysadmin privleges, that it could do anything. It appears, to me
anyway, that there are some privileges that have to be specifically granted.

Hope that helps someone else.

Dennis


"Irvine, Dennis" <dennis.irvine (AT) plumasnt (DOT) com> wrote

Quote:
I have a SSIS package that works fine when run inside Visual Studio.
It runs fine when run using the DTEXEC command in a cmd window.
It runs fine even when run in a powershell window using DTEXEC

It will NOT run as a job in SQL Server agent. I get this mess shown
below. As you can see I get ODBC errors and validation errors. None of
those show up anywhere else.

Can someone give me a hint here. I have been fooling around with this
for over a week trying to get this package to run in SQL server agent job
mode.
I have tried running it as an Integration package job and also as a
operating system job. They both end up as shown below.
The ODBC errors come from a third party proprietery ODBC driver that
accesses a proprietary database. But it works everywhere except as an
SQL agent job.

Thanks for any suggestions
Dennis


Microsoft (R) SQL Server Execute Package Utility
Version 9.00.3042.00 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.

Started: 2:00:15 PM
Info: 2008-07-03 14:00:19.00
Code: 0x4004300A
Source: COPY BOX DATA DTS.Pipeline
Description: Validation phase is beginning.
End Info
Error: 2008-07-03 14:00:19.14
Code: 0xC0047062
Source: COPY BOX DATA COREDIR BOX DATA [1]
Description: System.Data.Odbc.OdbcException: ERROR [S1000] [TOD][ODBC
Driver]Invalid connect syntax (uid/pwd/datasource)
ERROR [01000] [Microsoft][ODBC Driver Manager] The driver doesn't support
the version of ODBC behavior that the application requested (see
SQLSetEnvAttr).
at System.Data.Odbc.OdbcConnection.HandleError(OdbcHa ndle hrHandle,
RetCode retcode)
at System.Data.Odbc.OdbcConnectionHandle..ctor(OdbcCo nnection
connection, OdbcConnectionString constr, OdbcEnvironmentHandle
environmentHandle)
at
System.Data.Odbc.OdbcConnectionFactory.CreateConne ction(DbConnectionOptions
options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection
owningObject)
at
System.Data.ProviderBase.DbConnectionFactory.Creat eNonPooledConnection(DbConnection
owningConnection, DbConnectionPoolGroup poolGroup)
at
System.Data.ProviderBase.DbConnectionFactory.GetCo nnection(DbConnection
owningConnection)
at
System.Data.ProviderBase.DbConnectionClosed.OpenCo nnection(DbConnection
outerConnection, DbConnectionFactory connectionFactory)
at System.Data.Odbc.OdbcConnection.Open()
at
Microsoft.SqlServer.Dts.Runtime.ManagedHelper.GetM anagedConnection(String
assemblyQualifiedName, String connStr, Object transaction)
at
Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnec tionManager90.AcquireConnection(Object
pTransaction)
at
Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceA dapter.AcquireConnections(Object
transaction)
at
Microsoft.SqlServer.Dts.Pipeline.ManagedComponentH ost.HostAcquireConnections(IDTSManagedComponentWra pper90
wrapper, Object transaction)
End Error
Error: 2008-07-03 14:00:19.14
Code: 0xC0047017
Source: COPY BOX DATA DTS.Pipeline
Description: component "COREDIR BOX DATA" (1) failed validation and
returned error code 0x80131937.
End Error
Error: 2008-07-03 14:00:19.14
Code: 0xC004700C
Source: COPY BOX DATA DTS.Pipeline
Description: One or more component failed validation.
End Error
Error: 2008-07-03 14:00:19.14
Code: 0xC0024107
Source: COPY BOX DATA
Description: There were errors during task validation.
End Error
DTExec: The package execution returned DTSER_FAILURE (1).
Started: 2:00:15 PM
Finished: 2:00:19 PM
Elapsed: 3.25 seconds


Reply With Quote
  #25  
Old   
Irvine, Dennis
 
Posts: n/a

Default Re: Help Newbie - SSIS run in SQL server agent job - 07-07-2008 , 04:51 PM


Update. It appears to have been a "permissions" issue after all.

I clicked on the "database server" instance and went to properties. Under
"permissions" I highlighted the special account I had created and then
checked the box for special permissions titled "Alter connections"
when I did this,, my package ran just fine.

I assumed, since my special account had Domain Admin priveliges and
Sysadmin privleges, that it could do anything. It appears, to me
anyway, that there are some privileges that have to be specifically granted.

Hope that helps someone else.

Dennis


"Irvine, Dennis" <dennis.irvine (AT) plumasnt (DOT) com> wrote

Quote:
I have a SSIS package that works fine when run inside Visual Studio.
It runs fine when run using the DTEXEC command in a cmd window.
It runs fine even when run in a powershell window using DTEXEC

It will NOT run as a job in SQL Server agent. I get this mess shown
below. As you can see I get ODBC errors and validation errors. None of
those show up anywhere else.

Can someone give me a hint here. I have been fooling around with this
for over a week trying to get this package to run in SQL server agent job
mode.
I have tried running it as an Integration package job and also as a
operating system job. They both end up as shown below.
The ODBC errors come from a third party proprietery ODBC driver that
accesses a proprietary database. But it works everywhere except as an
SQL agent job.

Thanks for any suggestions
Dennis


Microsoft (R) SQL Server Execute Package Utility
Version 9.00.3042.00 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.

Started: 2:00:15 PM
Info: 2008-07-03 14:00:19.00
Code: 0x4004300A
Source: COPY BOX DATA DTS.Pipeline
Description: Validation phase is beginning.
End Info
Error: 2008-07-03 14:00:19.14
Code: 0xC0047062
Source: COPY BOX DATA COREDIR BOX DATA [1]
Description: System.Data.Odbc.OdbcException: ERROR [S1000] [TOD][ODBC
Driver]Invalid connect syntax (uid/pwd/datasource)
ERROR [01000] [Microsoft][ODBC Driver Manager] The driver doesn't support
the version of ODBC behavior that the application requested (see
SQLSetEnvAttr).
at System.Data.Odbc.OdbcConnection.HandleError(OdbcHa ndle hrHandle,
RetCode retcode)
at System.Data.Odbc.OdbcConnectionHandle..ctor(OdbcCo nnection
connection, OdbcConnectionString constr, OdbcEnvironmentHandle
environmentHandle)
at
System.Data.Odbc.OdbcConnectionFactory.CreateConne ction(DbConnectionOptions
options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection
owningObject)
at
System.Data.ProviderBase.DbConnectionFactory.Creat eNonPooledConnection(DbConnection
owningConnection, DbConnectionPoolGroup poolGroup)
at
System.Data.ProviderBase.DbConnectionFactory.GetCo nnection(DbConnection
owningConnection)
at
System.Data.ProviderBase.DbConnectionClosed.OpenCo nnection(DbConnection
outerConnection, DbConnectionFactory connectionFactory)
at System.Data.Odbc.OdbcConnection.Open()
at
Microsoft.SqlServer.Dts.Runtime.ManagedHelper.GetM anagedConnection(String
assemblyQualifiedName, String connStr, Object transaction)
at
Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnec tionManager90.AcquireConnection(Object
pTransaction)
at
Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceA dapter.AcquireConnections(Object
transaction)
at
Microsoft.SqlServer.Dts.Pipeline.ManagedComponentH ost.HostAcquireConnections(IDTSManagedComponentWra pper90
wrapper, Object transaction)
End Error
Error: 2008-07-03 14:00:19.14
Code: 0xC0047017
Source: COPY BOX DATA DTS.Pipeline
Description: component "COREDIR BOX DATA" (1) failed validation and
returned error code 0x80131937.
End Error
Error: 2008-07-03 14:00:19.14
Code: 0xC004700C
Source: COPY BOX DATA DTS.Pipeline
Description: One or more component failed validation.
End Error
Error: 2008-07-03 14:00:19.14
Code: 0xC0024107
Source: COPY BOX DATA
Description: There were errors during task validation.
End Error
DTExec: The package execution returned DTSER_FAILURE (1).
Started: 2:00:15 PM
Finished: 2:00:19 PM
Elapsed: 3.25 seconds


Reply With Quote
Reply
Page 3 of 3 < 1 2 3

« Previous Thread | Next Thread »



Thread Tools
Display Modes
Linear Mode Linear Mode

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
vB code is On
Smilies are On
[IMG] code is On
HTML code is Off



Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.


Contact Us - dbTalk Databases Forums - Archive - Top