Running an SSIS package in SQL 2008 or 2005
Posted on 2011-10-23
I am trying to create a job to run an SSIS Package . I am getting an Error Authenticvatin proxy Linda-PC\Linda unknown User Name or bad Password.
I first logged in as SA and I created a new Login called JobExecuter. On Server Roles I have SysAdmin and Public checked. On User mapping: I selected the Database I am reading Data from and gave it DataReader and public. On the database I am writing to, I selected db_datareader and db_datawriter as well as public. On msdb I have SQLAgentOperatorRole, SQLAgentReaderRole and SQLAgentOperatorRole and public checked.
When I created a Credential called SSISJobExecuter the Identity is Linda-PC\Linda with a password. I think this is where my problem lies. I wanted it to be Linda-PC\JobExecuter, but that would not take. I am getting the User or Built in Security Principal cannot be found.
Then I created a Proxy called JobExecute and tied it to subsystem SQL Server Integration ServicesPackage.
Then I created the SSIS package and made sure that JobExecuter was the userID and gave a password for the destinationConnection OLDB and the sourceConnectionOLDB. I tested the package in Visual Studio and it works.
I then created a new job and in step1 I said the type was SQL Server Integration Service Package and run as JobExecute. The package source is FileSystem and I selected the location of the dtsx package file.
If anyone can straighten me out I would appreciate it. Once I get this fixed
I need to create another copy on SQL Server 2005. Is there anything else
that is different from 2008?
Thanks in advance.