December 22, 2009 at 7:00 am
Ive created an SSIS package that uses several OLE DB connections to connect access files to sql server. Hardcoding the connection details works fine when the package is run, but as soon as i try to use a config file I get errors with the OLE DB connection
The user name is 'Admin ' and there is no password, the connection test is OK when testing it in the connection wizard, but when i run the package using the config file, I get these errors
** NBD Co is the name of the access database
OnError,PSLW16792,B2B\wheelerm,Step02a Transfer Company into Feed,{13ad2124-9964-4b36-ba07-2bc67fa94ee6},{9C07E07A-D5F7-4E5F-B3A0-2B48084D9A2A},22/12/2009 12:27:33,22/12/2009 12:27:33,-1071611876,0x,SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "NBD Co" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
OnError,PSLW16792,B2B\wheelerm,Step02a Transfer Company into Feed,{13ad2124-9964-4b36-ba07-2bc67fa94ee6},{9C07E07A-D5F7-4E5F-B3A0-2B48084D9A2A},22/12/2009 12:27:33,22/12/2009 12:27:33,-1073450985,0x,component "OLE DB Source" (54) failed validation and returned error code 0xC020801C.
OnError,PSLW16792,B2B\wheelerm,Step02a Transfer Company into Feed,{13ad2124-9964-4b36-ba07-2bc67fa94ee6},{9C07E07A-D5F7-4E5F-B3A0-2B48084D9A2A},22/12/2009 12:27:33,22/12/2009 12:27:33,-1073450996,0x,One or more component failed validation.
OnError,PSLW16792,B2B\wheelerm,Step02a Transfer Company into Feed,{13ad2124-9964-4b36-ba07-2bc67fa94ee6},{9C07E07A-D5F7-4E5F-B3A0-2B48084D9A2A},22/12/2009 12:27:33,22/12/2009 12:27:33,-1073594105,0x,There were errors during task validation.
Ive added the username myself manually into the config file, but it makes no difference. Ive even added a password to the connection string, then to the config file, that didnt work either. Im now really stuck and cant move this forward. I know that adding passwords to config files is not recommended, but this operates in a secure environment and I just want to get the thing working with a config file. Can anyone help ?
December 22, 2009 at 7:38 am
[font="Comic Sans MS"]
Hello there -
unfortunately it's extremely difficult to identify which step you are doing wrong. Please refer the step by step procedure below and see if this still doesn't work.
1)http://www.nigelrivett.net/DTS/SSISConfigurationFile.html
OR
2)http://www.sqlis.com/post/Easy-Package-Configuration.aspx
Also - are you sure that you configuration file is being used properly - I mean the values are being populated in the variable window or the property window of the database connection?
[/font]
[font="Comic Sans MS"]--
Sabya[/font]
December 22, 2009 at 7:41 am
[font="Comic Sans MS"]
Also - it might be a good idea to use a handy plugin - BIDS helper that highlights variables and connection managers driven from configuration file.
[/font]
[font="Comic Sans MS"]--
Sabya[/font]
December 22, 2009 at 8:18 am
ok, thanks, ill give it a try
December 22, 2009 at 8:24 am
I had a similar problem with an ftp connection early in the process, adding the ftp password manually to the config file solved it, so i know that the config file is being read.
December 22, 2009 at 8:49 am
mwwheeler (12/22/2009)
I had a similar problem with an ftp connection early in the process, adding the ftp password manually to the config file solved it, so i know that the config file is being read.
This is the only way to do it, as SSIS doesnt like to save passwords into unsecured files, remeber though if you make changes in BIDS to the config file it will remove the password adn you need to re-enter it.
Ideally If you need to do this you should be creating an SQL user, with minimal (DataReader) rights on the source DB, you shouldnt be using an Admin credential (ever), mainly if someone looks at the config file they get full access to all DB's ( sorry im a little paranoid).
The only other way round this is to use integrated security, this assumes that the SQLAgent has full rights to the source Db, or you have set up Proxy credentials under which you are running the job.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
December 23, 2009 at 3:13 am
its only the source data in the access files that isnt password protected, the sql server stuff uses integrated security so passwords shouldnt be an issue there. I havent actually got to the sql server stage yet when using config files, im still trying to get into the access files
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply