| Author |
Topic |
|
kkmurthy
Starting Member
41 Posts |
 Posted - 2010-06-07 : 15:55:36
|
| I have developed SSIS Package using BI studio with MS access databse in a mapped drive as the source and the SQl server database as the destination. When I execute this package in BI studio it works perfectly fine.I deployed this package to the SQL server and created the Job and scheduled it. It does not work . I did review various articles and took care of precautions such as : In the mapped folder under security I have added my windows username whichhas full control. I have also used windows authentication instead of SQl server authentication while creating connection manager. None of the other applications are running while executing this package in server. I have also ensured that the the Protection Level is selected as EncryptSensitiveWithPassword. I am still getting the follwoing error:MessageExecuted as user: DEVSERV\SYSTEM. ... 9.00.4035.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 3:32:37 PM Error: 2010-06-07 15:32:37.79 Code: 0xC0202009 Source: QA_Data_Dev Connection manager "QA_Data" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "'T:\QA Database 20080819 1.mdb' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.". End Error Error: 2010-06-07 15:32:37.79 Code: 0xC020801C Source: QA Database QA_Database Access Source [1] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "QA_Data" failed with error code 0xC0202009. There may be erro... The package execution fa... The step failed.Can some one help me please to resolve this issueKKmurthy |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
kkmurthy
Starting Member
41 Posts |
Posted - 2010-06-08 : 10:23:01
|
| Thank you Tara for the tip. Unfortunately it is still not working.I created the package with the connection managers using windows authentication ( On the SQL database side) and with Microsoft Jet 4.0 OLEDB Provider on the access sideI have recreated the mapping in W explorer with the same windows user/pwdI logged into the SQL server Management studio with windows authentication created the job and executed the job in the SQL server. When I execute the package using access database with in the same server using local drives it gets executed well. I am sure it is the mapping which is crerating this issueIs there any other thing I am missing here. Regards,KKMurthyI am still getting the same error as above |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
kkmurthy
Starting Member
41 Posts |
Posted - 2010-06-08 : 13:27:01
|
| I did RDP into DB server with the same windows acct which I uused to log into SQlserver (with windows authentication) whihc ia same for the mapping. Am I missing some thing here? KKMurthy |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
kkmurthy
Starting Member
41 Posts |
Posted - 2010-06-08 : 15:30:15
|
| These are the steps I I did.I have created the packages with connection manager acct as: murthI deployed the Package with the SQL server auth with the same username MurthI logged into the SQL server with the same uname: murth and pwdCreated the job with the SQL server auth with the same user murthI created the mapped drive with the same user murthI executed it and the same problem.The result is same |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
kkmurthy
Starting Member
41 Posts |
Posted - 2010-06-09 : 08:39:28
|
| Thank you Tara for spending time on my issue. I greatly appreciate this. However I still have the same problem.You are right. The services were running on system account. This is what I did. I changed the services to run on my windows network accountI have created the packages with connection manager acct same as windows network account( login created in SQL server)I deployed the Package with the SQL server authentication with the same windows netwrok account I logged into the SQL server with the same uname as windows network account ( which I created in SQL server logins)Created the job with the SQL server auth with the same windows network account( login created in SQL server)I created the mapped drive with the same windows network accountI executed it and the same problem.The result is sameKKmurthy |
|
|
|
sue3959
Starting Member
1 Post |
Posted - 2010-06-28 : 11:31:41
|
I have same problem. Just wonder if you have solved yours. If so, please tell me how you solved it. Thanks! Suequote:
Originally posted by kkmurthy
Thank you Tara for spending time on my issue. I greatly appreciate this. However I still have the same problem.You are right. The services were running on system account. This is what I did. I changed the services to run on my windows network accountI have created the packages with connection manager acct same as windows network account( login created in SQL server)I deployed the Package with the SQL server authentication with the same windows netwrok account I logged into the SQL server with the same uname as windows network account ( which I created in SQL server logins)Created the job with the SQL server auth with the same windows network account( login created in SQL server)I created the mapped drive with the same windows network accountI executed it and the same problem.The result is sameKKmurthy
|
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
SSIS Connection failed