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
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