Problem with SSIS scheduling JOB using SQL 2005 - Please Help

[azziuhc]

Problem Or BUG in SSIS package scheduling a JOB using SQL 2005:

I have been trying to create SSIS package in SQL 2005 but it seems extremely
difficult to deploy a new job schedule.
I tried all security features, read about every forum still not successfull.
even Microsoft admit that scheduling SSIS package has its difficulties.
What is shocking is that when:
Running SSIS package from BIDS, the package words ok
Running from export data from SSM studio it works ok.
Trying to schedule it seems the problem

This is what I am doing
- Logged in as sa (administrator).
- i created it a simple view to extract one record
(To make sure I am the owner)


Next I used Export/import Wizard wihin sql Server Management Studio:
- i right click on the database
- tasks
- Export data
- Select SQL data source
- Select Server name
- entered sa login + password (via use Sql Server Authentication)
- select database
- Next

- Select output destinattion (Flat File)
- Entered destination folder & filename
- Next

- Copy data form one or more tables or view
-Next

- Select Source table or view
- Next

- checked Execute immediately
- Checked Save SSIS Pakage
- Select SQL Server
- Next

-Do not save sensitive data
- OK

-Name: New Pakage
- Description: SSIS Pakage Description
- Server name: (selected server)
- use Sql Server Authetication: (entered sa + Password)
Next
Finish
-Executed perfectly

Now I go to Sql Server Agent which is running
- Right Click
- New
- Job
Under general
- name: PackageJob1
- owner: sa
- category: {uncategorized (local)
- Enabled is checked

Click on steps
- New
step name: step1
type: SQl Server Integration Service package
Run As: SQL Agent Service account
pakage Source: SQL Server
Server: select server
Log on to the server:
- Use SQl Server Authentication: sa + password
Pakage: clicked on ...
- Select New pakage (which was created)
OK
OK
OK
-----
Now I see the job listed
I highlight the job
right click
start job
gives me this error:
---------------------------------------------------------------
Source: New Package Connection manager "SourceConnectionOLEDB"
Description: SSIS Error Code DTS_E_OLEDBERROR.
An OLE DB error has occurred. Error code: 0x80040E4D.
An OLE DB record is available.
Source: "Microsoft SQL Native Client"
Hresult: 0x80040E4D Description: "Login failed for user 'sa'.".
End Error Error: 2010-06-10 13:28:41.75
Code: 0xC020801C
Source: Data Flow Task Source - vw_Pakage[1]
Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.
The AcquireConnection method call to the connection manager
"SourceConnectionOLEDB" failed with error code 0xC0202009.
There may be error message...
The package execution fa...
The step failed.
------------------------------------------------------------
Please HELP - HELP - HELP
Thank you
dre

 

[SQLBill]

This could be your issue:

-Do not save sensitive data
- OK

What you are doing is dropping any login/password or other sensitive info and need to resubmit it every time you run the package.

Try this setting instead:

Rely on server storage and roles for access control.

If that doesn't work, are you running the package on the same server that the work is being done on? For example, is the package/job on ServerA, but all the connections are for ServerB?

If that is the situation and one server is 64 bit and the other is 32 bit, that can cause issues. In that case, try creating a job and running this command: (change servername to the real server name and SSIS Package Name to the real package name.

DTSRun /S "Servername" /E /N "SSIS Package Name"

-SQLBill


The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[azziuhc]

Thank you for your reply.
I tried on the same server login as sa but still no success.
I tried to have a password encrypted as well on the package still No success
I even tried serverstorage protection still not working.

Please advice
Thank you
dre