I have a SSIS package that runs fine in Visual Studio. But if I put it in a scheduled job in SQL Server agent, it won't run. Both VS and SA use the same account to run. The error I get in the logs is an ODBC error. The package does have an ODBC connection in it. The connection uses a SYSTEM DSN connection to get data from another source on another machine. I am simply using Windows authentication and VS is running under account "x". the SQL Server Agent service is also set up to run under account "x". Yet the error message says the driver cannot be loaded. Is there perhaps and environment variable problem??? I believe the actual ODBC driver for this is on a network drive. Perhaps the network drive is not "mapped" within the SQL Server Agent service???? Anyone have any suggestions? Thanks
Further update. It is almost definitely a network drive mapping issue. I added a "step" to the job that ran an operating system command to map the necessary drive prior to running the main package and it worked. Don't know why I need to map the drive, as it is normally mapped for that computer under that account. But at least it worked. Dennis "Irvine, Dennis" wrote in message news:eMd%23zQu1IHA.3920@TK2MSFTNGP02.phx.gbl... >I have a SSIS package that runs fine in Visual Studio. But if I put it in >a scheduled job in SQL Server agent, it won't run. > > Both VS and SA use the same account to run. > > The error I get in the logs is an ODBC error. > The package does have an ODBC connection in it. > The connection uses a SYSTEM DSN connection to get data from another > source on another machine. > I am simply using Windows authentication and VS is running under account > "x". > the SQL Server Agent service is also set up to run under account "x". > > Yet the error message says the driver cannot be loaded. > > Is there perhaps and environment variable problem??? > I believe the actual ODBC driver for this is on a network drive. Perhaps > the network drive is not "mapped" within the SQL Server Agent service???? > > Anyone have any suggestions? > Thanks > >
Further update. The package will run if I log into the server under the given account and right click on the job and chose "run". But it won't run at the scheduled time, by itself. Any ideas? Dennis "Irvine, Dennis" wrote in message news:%23QanAVx1IHA.5832@TK2MSFTNGP02.phx.gbl... > Further update. > > It is almost definitely a network drive mapping issue. > I added a "step" to the job that ran an operating system command to map > the necessary drive prior to running the main package and it worked. > > Don't know why I need to map the drive, as it is normally mapped for that > computer under that account. > But at least it worked. > > Dennis > > "Irvine, Dennis" wrote in message > news:eMd%23zQu1IHA.3920@TK2MSFTNGP02.phx.gbl... >>I have a SSIS package that runs fine in Visual Studio. But if I put it >>in a scheduled job in SQL Server agent, it won't run. >> >> Both VS and SA use the same account to run. >> >> The error I get in the logs is an ODBC error. >> The package does have an ODBC connection in it. >> The connection uses a SYSTEM DSN connection to get data from another >> source on another machine. >> I am simply using Windows authentication and VS is running under account >> "x". >> the SQL Server Agent service is also set up to run under account "x". >> >> Yet the error message says the driver cannot be loaded. >> >> Is there perhaps and environment variable problem??? >> I believe the actual ODBC driver for this is on a network drive. Perhaps >> the network drive is not "mapped" within the SQL Server Agent service???? >> >> Anyone have any suggestions? >> Thanks >> >> >
