Hallo,
I use SSIS Version 9.00.1399.00 and keep getting problems trying to use package configuration.
I choose xml configuration file as type and browse a path on our LAN to create te configuration file .
Then I select the properties of a OLEDB communication manager as Properties to Export
Doing so I obtain this configuration:Name:
JACBE_IF_CONFIG
Type:
Configuration File
New configuration file will be created.
File name:
L:\Projects\Vinci\SSIS\DVL\FMC loader Import\FMC Loader Import\FMC Loader Import\JACBE_IF_CONFIG.xml
Properties:
\Package.Connections[JACBE_IF].Properties[UserName]
\Package.Connections[JACBE_IF].Properties[ServerName]
\Package.Connections[JACBE_IF].Properties[RetainSameConnection]
\Package.Connections[JACBE_IF].Properties[ProtectionLevel]
\Package.Connections[JACBE_IF].Properties[Password]
\Package.Connections[JACBE_IF].Properties[Name]
\Package.Connections[JACBE_IF].Properties[InitialCatalog]
\Package.Connections[JACBE_IF].Properties[Description]
\Package.Connections[JACBE_IF].Properties[ConnectionString]
The system creates a XML file but when I run the package I get the following error in the output pane.
Information: 0x40016041 at FMC_People: The package is attempting to configure from the XML file "L:\Projects\Vinci\SSIS\DVL\FMC loader Import\FMC Loader Import\FMC Loader Import\JACBE_IF_CONFIG.xml".
SSIS package "FMC_People.dtsx" starting.
Information: 0x4004300A at Dataprocessing_PEOPLE, DTS.Pipeline: Validation phase is beginning.
Error: 0xC0202009 at FMC_People, Connection manager "JACBE_IF": An OLE DB error has occurred. Error code: 0x80040E21.
An OLE DB record is available.Source: "Microsoft OLE DB Service Components"Hresult: 0x80040E21Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".
Error: 0xC020801C at Dataprocessing_PEOPLE, FMC_ARE_PRESENT_destination 1 [22338]: The AcquireConnection method call to the connection manager "JACBE_IF" failed with error code 0xC0202009.
Error: 0xC0047017 at Dataprocessing_PEOPLE, DTS.Pipeline: component "FMC_ARE_PRESENT_destination 1" (22338) failed validation and returned error code 0xC020801C.
Error: 0xC004700C at Dataprocessing_PEOPLE, DTS.Pipeline: One or more component failed validation.
Error: 0xC0024107 at Dataprocessing_PEOPLE: There were errors during task validation.
SSIS package "FMC_People.dtsx" finished: Failure.
I don't get it. Where do I go wrong?
I tried the same with a DtsConfig file instead of an XML but to no avail. the way of working as described in BOL and in the book professional SQL SERVER 2005 Integration service seems to me perfectly similar.
Any ideas anyone? I would be most gratefull.
Kind regards,
Paul Baudouin
It won't store the password for you - you have to add it in manually. Check the contents of the file to see if the password is there (if you need it there).
By the way, you only need to store the ConnectionString property. Take out all the rest and give it a go!
-Jamie
|||
Dear Jamie,
You are a real help!
Your solution , only adding the connectionstring to the configuration file, worked when I manually added ';Password = @.@.@.@.@.' to the ConnectionString in the configurationfile.
(For security reasons I put @.@.@.@.@. instead of the real pasword.)
It would be nice if I could put an encrypted password in the configuration file instead of the plain string. I guess this is possible in SSIS?
Greetings,
Paul
|||Paul Baudouin wrote:
It would be nice if I could put an encrypted password in the configuration file instead of the plain string. I guess this is possible in SSIS?
Unfortunately not! Encrypted configuration files is soething I have suggested and I believe it is being considered for future development.
If you want it - ask for it at Microsoft Connect. if you don't ask you don't get!
-Jamie
|||
If this was enabled, the config would most likely need to be encrypted with a certificate. Would that be acceptable?
We don't want to protect the config with a password, because then where do you store that password, and so on ...
|||Paul,
how did you add it manually, this is how my connection string in the configuration file looks like
\Package.Connections[mydatabase].Properties[Password]
thanks,
kushpaw
|||If this was enabled, the config would most likely need to be encrypted with a certificate. Would that be acceptable?
We don't want to protect the config with a password, because then where do you store that password, and so on ...
Absolutely. that makes sense to me Donald.
|||
Kushpaw,
As I said above, you're probably best storing the ConenctioNStrig property rather than the password. Thereafter it will be obvious where you put the password.
-Jamie
|||
Dont mean to reopen this question but ..I have a package whose config is stored in an SQL Table. We r still in deplyment phase and what puzzles me is how will the package know first time when it runs where to look for a config file as prod environment will have a diff sql server. IS there a way to have an xml file which will tell the package which db to look in for rest of the config file. Do I make sense?
My present config which is in sql table has config vals for FlatFile CS, two SQL DB CS I am presuming one db CS is for the SQL Destination component and the other one is to tell the package where to look for the SSIS Configurations table.
Can I remove this last Db CS store in an XML file so that when we move to production we can edit this one XML file and go on....
|||Dont mean to reopen this question but ..I have a package whose config is stored in an SQL Table. We r still in deplyment phase and what puzzles me is how will the package know first time when it runs where to look for a config file as prod environment will have a diff sql server. IS there a way to have an xml file which will tell the package which db to look in for rest of the config file. Do I make sense?
Yes that makes sense. The way to do this is to use Indirect Configurations:
Indirect configurations ROCK!
(http://blogs.conchango.com/jamiethomson/archive/2005/11/02/2342.aspx)
Your other option is to populate the properties that can change between environments using the /SET option of dtexec. I wouldn't recommend this though - configurations are generally the way to go.
-Jamie
No comments:
Post a Comment