September 9, 2010 at 6:58 am
Hi,
I am a novice with SSIS but managed to create a package to extract data from a SQL Server 2005 view using ole db source component, store the data into a flat file on a local drive folder then upload the file to an ftp server using a script task.
If I run the package using the SQL Server BID (Visual Studio) then it runs ok, file on the local drive folder has data and also the one gets uploaded to server.
However, if I setup a SQL Server agent job and schedule the package or simply "start job at step" by right clicking the job title, it executes ok but bizarrely the file becomes empty (i.e. headers but no data) for both the one on the local drive and server.
Any ideas or suggestions?
Thanks in advance
September 9, 2010 at 7:25 am
When you run the package in BIDS, it uses your windows account and permissions.
When you run the SQL Agent job, by default it runs in the context of the SQL Agent service account, or a proxy account if you have set one up.
It sounds like the windows account for the SQL Agent does not have enough permissions for the task. One way to troubleshoot this is to log onto that server with the service account login and see what you can access, or run the package in BIDS and see what errors might be generated.
September 17, 2010 at 3:54 am
Hi,
Thanks for your response.
All applications are on the same server that I normally access using the master username and create the packages. I basically access the server using remote desktop, create the package after launching the BID, and create the server agent job by launching the SQL Server Management Studio.
I am only using the same network credentials for all applications since access is done using Remote Desktop!!
Thanks.
September 17, 2010 at 7:06 am
When you say you are using the "master" username to log in with Remote Desktop, is that the same account that the SQL Agent service uses when it starts up?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply