SSIS

DBA2010
DBA2010 used Ask the Experts™
on
I am trying to schadule a job on sql managment studio 2008.
the job will read from SQL and insert into SQL databas.
I setup an admin username "BIAdmin".
I deploy the package as a file system.dtsx when i double click on the system file its work perfect but when i schadule as a job on SSMS i goyt an error
 Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80040E4D  Description: "Login failed for user 'biadmin'.".  End Error  Error: 2011-08-04 13:14:22.25     Code: 0xC020801C

any help?

Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Ryan McCauleySenior Data Architect

Commented:
Where did you set up the BIAdmin user? When you double-click on the package to run it, it will execute as your current windows user, which is likely a local administrator and a member of the sysadmins SQL role. When you schedule the job, it runs as whatever use you specify.

If you have a copy of SQL Profiler handy - it's one of the "Advanced" client tools in the installation - you can monitor the server for failed logins, and it will show you exactly what's happening so you can make the change in your SSIS job.
Select Job name Right click Property.
In right side you can able see Owner Text box select Appropriate Login name and name do changes in category text box also

Incase any queries Let me know.

Author

Commented:
I know that, and i am using biadmin there
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

What are all componet your using ,can you share screenshot.

Author

Commented:
screen shot for what?
Can you give me package Screen Shot.

Author

Commented:
Problem Solved,
I dont know why but when I loged into diffrent name the Pckg lose the Username and password.
I tried to trace it and its work fine now.

Thanks
Senior Data Architect
Commented:
The username should be persisted, but depending on how you save the package, sensitive information (like passwords) are not kept. You can either set up those passwords when you schedule the package in the SQL Agent, or you can save the package with a password, which will keep the passwords. If you save the package without requiring a package password, it will lose all the connection passwords and you'll to add those again once it's configured on the server.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial