Package.dtsx -> XML format description/manual request

Hi All,

As I see it is possible to view a package (dtsx) in XML form.

Is the XML stable and documented? Would it be good decision to

use the XML as source for third party development or we need to use

some API to get package's content ? Where I could get the package's XML

structure description/manual ?

Thanks,

Sergiy

I'd say the overall schema is stable, but it is not publically documented so subject to change I guess. What will change is the detailed sections within tasks or components that describe properties. Task or component developers can change that whenever they feel like, and may do. Whilst that may sound a bit dangerous, it is perfectly acceptable since they would be expected to write the appropriate code to upgrade between component versions, or even downgrade gracefully if required, it is possible.

The safest way would be to use the API/object model, but XML may be sufficient for what you need. Just think what will happen if a new element or attribute appears because a new version of a task comes out and it has a new property. Examples of this are some of my own developed tasks, or changes MS made in SP1.

Package xml config files used in child packages?

I have a package that executes 3 other packages in it. From the command line I pass in which config file to use (just contains db/seerver) does this setting get carried over into the child packages as well?

If you are using the Execute Package task to execute child packages it looks like you bumped into the current SSIS limitation. Take a look at the following posting:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2078371&SiteID=1

Regards,

Yitzhak

|||

Seems like that problem is to do with setting variables at the command line in child packages?

Im just curious if I use a config file that specifies a connection to the database to use, whats the best way to pass that connection on to the child packages?

|||

You can pass information through the parent package variable (PPV) configurations. However, PPVs are applied after all other configurations, so this can cause some problems. For example, if you are trying to pass the connect string to a database to use the SQL Server configurations, the PPV isn't applied until after the SQL Server configurations are already done.

Another way to accomplish this is to use an Execute Process task instead of the Execute Packge task. Then you can call DTEXEC, and pass along whatever connection strings you want to use.

Finally, you can use a Script Task to load the child package from the SSIS object model, set values in the package, and launch it from that.

Package xml config files used in child packages?

I have a package that executes 3 other packages in it. From the command line I pass in which config file to use (just contains db/seerver) does this setting get carried over into the child packages as well?

If you are using the Execute Package task to execute child packages it looks like you bumped into the current SSIS limitation. Take a look at the following posting:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2078371&SiteID=1

Regards,

Yitzhak

|||

Seems like that problem is to do with setting variables at the command line in child packages?

Im just curious if I use a config file that specifies a connection to the database to use, whats the best way to pass that connection on to the child packages?

|||

You can pass information through the parent package variable (PPV) configurations. However, PPVs are applied after all other configurations, so this can cause some problems. For example, if you are trying to pass the connect string to a database to use the SQL Server configurations, the PPV isn't applied until after the SQL Server configurations are already done.

Another way to accomplish this is to use an Execute Process task instead of the Execute Packge task. Then you can call DTEXEC, and pass along whatever connection strings you want to use.

Finally, you can use a Script Task to load the child package from the SSIS object model, set values in the package, and launch it from that.

PACKAGE ENCODING

Hi, a new (little..) problem with xml source.

I have to import large xml file in sql server and I use data transfer task and xml source.

The xml file are generated without specifing any encoding, and so I obtain many character error if i don't change the encoding.

When i put "by hand" (with a xml editor) in the xml file this encoding <?xml version="1.0" encoding="ISO8859-1"?>, the SSIS task works perfectly without any error.

So, i'm looking for a way to use this encoding without editing xml file (more than 500 mb...). the way that i can imagine are:

1) change the package encoding (but I haven't find this kind of settings)

2) change the xml source encoding (but I haven't find this kind of settings)

3) change the console chcp (normally i have 850, i have tested 1252 but without any success)

4) make a xml trasformation (but i don't know the best way); I've tried with XML task without any success...

could anybody help me?

thank you in advance

alessandro

Hi, an update.

I've found a little "workaround" using XML task - patch. In this way I can patch the original xml file with a diff xml file that contain only the encoding settings. The ouput is a sort of "merge" of the xml file and the new enconding.

the real problem is that this package fail because it parse the xml file before create the new one...and it find the illegal character (that are the reasons for wich i want to change the xml encoding...!!!).

I've seen that it works making a test (replacing the illegal caracther "by hand").

So i have founded another way to obtain the same problem....

Please..:! someone help me!

thank you in advance

Alessandro

|||

....Nobody help me?

Now i'm working on .net xml source with scripting...

lavez

Package configuration xml and lost password

Hi to all!.

I tell you my problem...

I have an DB2 source and SQL destination. I have 2 ConnectionManager. One for DB2 (IBM DB2) and other for SQL. Both are of the OleDB type.

Well. I setup my connections and it work fine. Now i want to change my ConnectionManager for others sources or destinations. And I don't want to have to change the connection in more than forty packages.

For this, i have 2 XML configuration file in package configurations. One for connection. In this XML i have the connectionstring, user and password. These files are obtained in all the packages.

Well. If i change the connectionstring in this XML file configuration, only work fine the OLEDB for SQL. The conection for DB2 return the fail: "The AcquireConnection method call to the connection manager "_packagename_" failed with error code 0xC0202009."

I have proven to modify protectionLevel of the package to DontSaveSensitive. SQL work fine. DB2 fails. I have changed to EncryptSensitiveWithPassword. The same.

Why the packages obtain the data of connection of the configuration file for SQL and it does not work or with the configuration file for DB2.

This is bug?

Thanks to all!.

XML based configuration should work the same way for DB2. Make sure you open the xml configuration file to add the password manually as BIDS won't put there for you.

ALso, make sure you check the progress tab (if using bids) for warnings about configurations not taking place.

|||

Thanks for you answer.

That already had proven it. In other machines it works with that configuration, so I will continue investigating.

Thanks again.

|||

I am also facing the same problem.

I am using one oledb connection for db2 (IBM oledb provider) and second oledb connection for sql server.

Its taking updated value for sql server but in case of DB2 giving the same error.

Have you got any solution.

Please share it with me also.

or is it a bug in SSIS?

Thanks in advance

Package configuration xml and lost password

Hi to all!.

I tell you my problem...

I have an DB2 source and SQL destination. I have 2 ConnectionManager. One for DB2 (IBM DB2) and other for SQL. Both are of the OleDB type.

Well. I setup my connections and it work fine. Now i want to change my ConnectionManager for others sources or destinations. And I don't want to have to change the connection in more than forty packages.

For this, i have 2 XML configuration file in package configurations. One for connection. In this XML i have the connectionstring, user and password. These files are obtained in all the packages.

Well. If i change the connectionstring in this XML file configuration, only work fine the OLEDB for SQL. The conection for DB2 return the fail: "The AcquireConnection method call to the connection manager "_packagename_" failed with error code 0xC0202009."

I have proven to modify protectionLevel of the package to DontSaveSensitive. SQL work fine. DB2 fails. I have changed to EncryptSensitiveWithPassword. The same.

Why the packages obtain the data of connection of the configuration file for SQL and it does not work or with the configuration file for DB2.

This is bug?

Thanks to all!.

XML based configuration should work the same way for DB2. Make sure you open the xml configuration file to add the password manually as BIDS won't put there for you.

ALso, make sure you check the progress tab (if using bids) for warnings about configurations not taking place.

|||

Thanks for you answer.

That already had proven it. In other machines it works with that configuration, so I will continue investigating.

Thanks again.

|||

I am also facing the same problem.

I am using one oledb connection for db2 (IBM oledb provider) and second oledb connection for sql server.

Its taking updated value for sql server but in case of DB2 giving the same error.

Have you got any solution.

Please share it with me also.

or is it a bug in SSIS?

Thanks in advance

Package Configuration Wizard Error

I am attempting to create an XML Configuration file and I am recieving the following error once I click finish in the wizard:

Could not complete wizard actions.

Additional Information:
Could not generate the configuration file. (Microsoft.Data.TransformationServices.Wizard)

Technical Details:
===================================

Could not complete wizard actions. (Microsoft Visual Studio)

===================================

Could not generate the configuration file. (Microsoft.DataTransformationServices.Wizards)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%u00ae+Visual+Studio%u00ae+2005&ProdVer=8.0.50727.42&EvtSrc=Microsoft.DataTransformationServices.Wizards.ConfigurationWizardPages.ConfigurationWizardSR&EvtID=CouldNotGenerateConfigurationFile&LinkId=20476

Program Location:

at Microsoft.DataTransformationServices.Wizards.ConfigurationWizardForm.GenerateXmlConfigurationFile(DesignTimeConfiguration designTimeConfiguration)
at Microsoft.DataTransformationServices.Wizards.ConfigurationWizardForm.OnFinish(CancelEventArgs e)

===================================

Failure exporting configuration file.
(eFreedomAS)

Program Location:

at Microsoft.SqlServer.Dts.Runtime.Package.ExportConfigurationFile(String str)
at Microsoft.DataTransformationServices.Wizards.ConfigurationWizardForm.GenerateXmlConfigurationFile(DesignTimeConfiguration designTimeConfiguration)

Name:
eFreedomAS2

Type:
Configuration File

New configuration file will be created.

File name:
C:\abc.xml

Properties:
\Package.Variables[User::SourcePath].Properties[Value]
\Package.Variables[User::SourcePath].Properties[Name]
\Package.Variables[User::Packages].Properties[Value]
\Package.Variables[User::Packages].Properties[Name]
\Package.Variables[User::PackageDefNumber].Properties[Value]
\Package.Variables[User::PackageDefNumber].Properties[Name]
\Package.Variables[User::Directory].Properties[Value]
\Package.Variables[User::Directory].Properties[Name]
\Package.Variables[User::DestinationPath].Properties[Value]
\Package.Variables[User::DestinationPath].Properties[Name]
\Package.Connections[ViewHelpTopicDef].Properties[Name]
\Package.Connections[ViewHelpTopicDef].Properties[ConnectionString]
\Package.Connections[ViewDef].Properties[Name]
\Package.Connections[ViewDef].Properties[ConnectionString]
\Package.Connections[ValidationTestDef].Properties[Name]
\Package.Connections[ValidationTestDef].Properties[ConnectionString]
\Package.Connections[ValidationRulePackageDef].Properties[Name]
\Package.Connections[ValidationRulePackageDef].Properties[ConnectionString]
\Package.Connections[ValidationRuleFileDef].Properties[Name]
\Package.Connections[ValidationRuleFileDef].Properties[ConnectionString]
\Package.Connections[ValidationRuleDef].Properties[Name]
\Package.Connections[ValidationRuleDef].Properties[ConnectionString]
\Package.Connections[ValidationMessageFileDef].Properties[Name]
\Package.Connections[ValidationMessageFileDef].Properties[ConnectionString]
\Package.Connections[ValidationMessageDef].Properties[Name]
\Package.Connections[ValidationMessageDef].Properties[ConnectionString]
\Package.Connections[ValidationIdentifierDependency].Properties[Name]
\Package.Connections[ValidationIdentifierDependency].Properties[ConnectionString]
\Package.Connections[ValidationCalcGraphEdge].Properties[Name]
\Package.Connections[ValidationCalcGraphEdge].Properties[ConnectionString]
\Package.Connections[UserGroupFeatureDef].Properties[Name]
\Package.Connections[UserGroupFeatureDef].Properties[ConnectionString]
\Package.Connections[UserGroupFeature].Properties[Name]
\Package.Connections[UserGroupFeature].Properties[ConnectionString]
\Package.Connections[UserGroupDef].Properties[Name]
\Package.Connections[UserGroupDef].Properties[ConnectionString]
\Package.Connections[UserGroup].Properties[Name]
\Package.Connections[UserGroup].Properties[ConnectionString]
\Package.Connections[TableDef].Properties[Name]
\Package.Connections[TableDef].Properties[ConnectionString]
\Package.Connections[Source].Properties[UserName]
\Package.Connections[Source].Properties[ServerName]
\Package.Connections[Source].Properties[Password]
\Package.Connections[Source].Properties[Name]
\Package.Connections[Source].Properties[InitialCatalog]
\Package.Connections[Source].Properties[ConnectionString]
\Package.Connections[SelectListItemDef].Properties[Name]
\Package.Connections[SelectListItemDef].Properties[ConnectionString]
\Package.Connections[SelectListDef].Properties[Name]
\Package.Connections[SelectListDef].Properties[ConnectionString]
\Package.Connections[ScriptAssemblyDef].Properties[Name]
\Package.Connections[ScriptAssemblyDef].Properties[ConnectionString]
\Package.Connections[PrintedPageFormulaScriptDef].Properties[Name]
\Package.Connections[PrintedPageFormulaScriptDef].Properties[ConnectionString]
\Package.Connections[PageDef].Properties[Name]
\Package.Connections[PageDef].Properties[ConnectionString]
\Package.Connections[PageCalcPathSubPath].Properties[Name]
\Package.Connections[PageCalcPathSubPath].Properties[ConnectionString]
\Package.Connections[PageCalcPath].Properties[Name]
\Package.Connections[PageCalcPath].Properties[ConnectionString]
\Package.Connections[PackageLinkDef].Properties[Name]
\Package.Connections[PackageLinkDef].Properties[ConnectionString]
\Package.Connections[PackageImportTypeDef].Properties[Name]
\Package.Connections[PackageImportTypeDef].Properties[ConnectionString]
\Package.Connections[PackageIdentifierGroupDef].Properties[Name]
\Package.Connections[PackageIdentifierGroupDef].Properties[ConnectionString]
\Package.Connections[PackageIdentifierDef].Properties[Name]
\Package.Connections[PackageIdentifierDef].Properties[ConnectionString]
\Package.Connections[PackageExportTypeDef].Properties[Name]
\Package.Connections[PackageExportTypeDef].Properties[ConnectionString]
\Package.Connections[PackageDef].Properties[Name]
\Package.Connections[PackageDef].Properties[ConnectionString]
\Package.Connections[PackageDataItemTextDef].Properties[Name]
\Package.Connections[PackageDataItemTextDef].Properties[ConnectionString]
\Package.Connections[OnlinePagePrintedPageDef].Properties[Name]
\Package.Connections[OnlinePagePrintedPageDef].Properties[ConnectionString]
\Package.Connections[OnlinePageDefDependency].Properties[Name]
\Package.Connections[OnlinePageDefDependency].Properties[ConnectionString]
\Package.Connections[OnlinePageDef].Properties[Name]
\Package.Connections[OnlinePageDef].Properties[ConnectionString]
\Package.Connections[MaterialityToleranceDef].Properties[Name]
\Package.Connections[MaterialityToleranceDef].Properties[ConnectionString]
\Package.Connections[LineDef].Properties[Name]
\Package.Connections[LineDef].Properties[ConnectionString]
\Package.Connections[ImportTypeDef].Properties[Name]
\Package.Connections[ImportTypeDef].Properties[ConnectionString]
\Package.Connections[ImportDef].Properties[Name]
\Package.Connections[ImportDef].Properties[ConnectionString]
\Package.Connections[IdentifierStateDef].Properties[Name]
\Package.Connections[IdentifierStateDef].Properties[ConnectionString]
\Package.Connections[IdentifierRelatedDef].Properties[Name]
\Package.Connections[IdentifierRelatedDef].Properties[ConnectionString]
\Package.Connections[IdentifierPrintReferenceDef].Properties[Name]
\Package.Connections[IdentifierPrintReferenceDef].Properties[ConnectionString]
\Package.Connections[IdentifierGroupUserGroupFeatureDef].Properties[Name]
\Package.Connections[IdentifierGroupUserGroupFeatureDef].Properties[ConnectionString]
\Package.Connections[IdentifierGroupUserGroupFeature].Properties[Name]
\Package.Connections[IdentifierGroupUserGroupFeature].Properties[ConnectionString]
\Package.Connections[IdentifierGroupIdentifierDef].Properties[Name]
\Package.Connections[IdentifierGroupIdentifierDef].Properties[ConnectionString]
\Package.Connections[IdentifierGroupIdentifier].Properties[Name]
\Package.Connections[IdentifierGroupIdentifier].Properties[ConnectionString]
\Package.Connections[IdentifierGroupDef].Properties[Name]
\Package.Connections[IdentifierGroupDef].Properties[ConnectionString]
\Package.Connections[IdentifierGroup].Properties[Name]
\Package.Connections[IdentifierGroup].Properties[ConnectionString]
\Package.Connections[IdentifierDef].Properties[Name]
\Package.Connections[IdentifierDef].Properties[ConnectionString]
\Package.Connections[IdentifierDataItemTextDef].Properties[Name]
\Package.Connections[IdentifierDataItemTextDef].Properties[ConnectionString]
\Package.Connections[FormulaScriptDef].Properties[Name]
\Package.Connections[FormulaScriptDef].Properties[ConnectionString]
\Package.Connections[FormulaDef].Properties[Name]
\Package.Connections[FormulaDef].Properties[ConnectionString]
\Package.Connections[FeatureGroupFeatureDef].Properties[Name]
\Package.Connections[FeatureGroupFeatureDef].Properties[ConnectionString]
\Package.Connections[FeatureGroupDef].Properties[Name]
\Package.Connections[FeatureGroupDef].Properties[ConnectionString]
\Package.Connections[FeatureDef].Properties[Name]
\Package.Connections[FeatureDef].Properties[ConnectionString]
\Package.Connections[ExportTypeRelatedDef].Properties[Name]
\Package.Connections[ExportTypeRelatedDef].Properties[ConnectionString]
\Package.Connections[ExportTypeFormulaScriptDef].Properties[Name]
\Package.Connections[ExportTypeFormulaScriptDef].Properties[ConnectionString]
\Package.Connections[ExportTypeExportDef].Properties[Name]
\Package.Connections[ExportTypeExportDef].Properties[ConnectionString]
\Package.Connections[ExportTypeDef].Properties[Name]
\Package.Connections[ExportTypeDef].Properties[ConnectionString]
\Package.Connections[ExportTypeBlobTypeDef].Properties[Name]
\Package.Connections[ExportTypeBlobTypeDef].Properties[ConnectionString]
\Package.Connections[ExportFormulaScriptDef].Properties[Name]
\Package.Connections[ExportFormulaScriptDef].Properties[ConnectionString]
\Package.Connections[ExportDef].Properties[Name]
\Package.Connections[ExportDef].Properties[ConnectionString]
\Package.Connections[ColumnHeaderDef].Properties[Name]
\Package.Connections[ColumnHeaderDef].Properties[ConnectionString]
\Package.Connections[ColumnDef].Properties[Name]
\Package.Connections[ColumnDef].Properties[ConnectionString]
\Package.Connections[CellTypeFileTypeDef].Properties[Name]
\Package.Connections[CellTypeFileTypeDef].Properties[ConnectionString]
\Package.Connections[CellTypeDef].Properties[Name]
\Package.Connections[CellTypeDef].Properties[ConnectionString]
\Package.Connections[CellRangeDef].Properties[Name]
\Package.Connections[CellRangeDef].Properties[ConnectionString]
\Package.Connections[CellRangeCellDef].Properties[Name]
\Package.Connections[CellRangeCellDef].Properties[ConnectionString]
\Package.Connections[CellOverrideDef].Properties[Name]
\Package.Connections[CellOverrideDef].Properties[ConnectionString]
\Package.Connections[CellDef].Properties[Name]
\Package.Connections[CellDef].Properties[ConnectionString]
\Package.Connections[CategoryDef].Properties[Name]
\Package.Connections[CategoryDef].Properties[ConnectionString]
\Package.Connections[CalcPathSubPath].Properties[Name]
\Package.Connections[CalcPathSubPath].Properties[ConnectionString]
\Package.Connections[CalcPath].Properties[Name]
\Package.Connections[CalcPath].Properties[ConnectionString]
\Package.Connections[CalcGraphEdge].Properties[Name]
\Package.Connections[CalcGraphEdge].Properties[ConnectionString]
\Package.Connections[AreaDef].Properties[Name]
\Package.Connections[AreaDef].Properties[ConnectionString]

Found the issue myself. One of the variables I had unintentionally set for the package configuration was of type Object. Rightfully so, you are unable to save a variable of type object to be saved in an xml configuration file.

Package Configuration with XML file

Hi,

I have about 40 packages in my job. One of the problems that I have encoutered is that I don't know how I could use same XML dtsconfig file for all of my files. Each SSIS calls different file.

The XML dtsconfig file is a replacment for my *.ini file in sql 2000. The dtsconfig file will hold paths to all different files. Each SSIS will take different file name at a runtime.

How can I have all the files paths in one dtsconfig file - and is it even possible to do that in SSIS?

Any help greatly appreciated....Thank you,

Maria

Yes, that is very possible. Have you tried doing it? What problems are you having?

-Jamie

|||

Hi Jamie, for my first ssis package I created new XML dtsconfig file and lets say called it "common.dtsconfig". That all works fine. Then I created my second ssis and when I was doing Package configuration for it I selected "common.dtsconfig" file, it asked me if I want to re-use it and I said "YES". But by doing that I am not getting the path in the common.dtsconfig for my second ssis. So I deleted that connection within Package Configuration and added new with new xml file name. then I took this portion "<Configuration ConfiguredType="Property" Path="\Package.Connections[CustomerMasterTmpCurrency].Properties[ConnectionString]" ValueType="String">
<ConfiguredValue>C:\Inetpub\ftproot\customers.CSV</ConfiguredValue>
</Configuration>"

and copied to the common.dtsconfig, and went back to my second package configuration-> deleted that connection-> and re-added new connection that points to the common.dtsconfig file. That seems to be working fine.

However, when I deployed my 2 ssis they both got errors that the connection xxxx cannot be find. Each of them has a different connection. I went back to each of the package configuration deleted the old connection and re-added it........I still cannot edit as it gives me an error.................

There must be a better way to handle it and I hope you can help me with it.....Maria

Package Configuration using xml-file fails

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

|||

Donald Farmer wrote:

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

|||

ray_newbie_SSIS wrote:

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

Package Configuration using xml-file fails

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

|||

Donald Farmer wrote:

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

|||

ray_newbie_SSIS wrote:

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

Package Configuration in Dev enviornment

Am I missing something, I can't find any way to specific the XML configuration file when I try and run my package in Debug of BIDS. My OLE DB connections are all failing due to a lack of a password. I can set the password in BIDS but its forgotten as soon as I start Debug or build the solution. The only way I seem to be able to run my package (now that I have enabled configurations) is to de-select the enable configurations box.

I've tried putting a configuartion file in the Deployment folder, the bin folder and the project root folder all to the same result.

Unless you are using Indirect Configurations (http://blogs.conchango.com/jamiethomson/archive/2005/11/02/2342.aspx) the path to the .dtsconfig file is stored in your package so you have to make sure that that is where the file resides.

Its got nothing to do with the Deployment, bin or project root folder.

-Jamie

|||

The Indirect Configurations look handy I think I might try and change my package to use those.

So I finally figured out what was going on, this was driving me crazy. So when using the Package configuration wizard, in the specify configuration settings directly, I had typed a filename. I had clicked on the Browse button to see that it was looking into the project directory for the default directory on the file dialog. Apparently I never used the Save button while trying to set this up. I just assumed that was the directory and thus I never had the fully qualified path to the file. I had redone this while searching for a solution and even then, with the filename in the wizard text box I had clicked Browse, when I saw it was the directory where the file actually was I think I clicked Cancel.

So now I'm able to read the values. One error left and my first package is deployed, this might be a good friday after all! (edit: *no pun was intended*)

Package configuration help !

hi,

I have a package that executes nicely.
When I enable "Package Configuration", create an XML config file and only select the Connection Managers (2 file connection managers and 1 data source), I get the following error (when executing the package in debug mode from designer):
Information: 0x40016041 at MyPackage: The package is attempting to configure from the XML file "C:\src\myconfig.dtsConfig".
SSIS package "MyPackage.dtsx" starting.
Information: 0x4004300A at Data Flow Task - Extract my_file_src, DTS.Pipeline: Validation phase is beginning
Error: 0xC0202009 at MyPackage, Connection manager "MyConnMgr": An OLE DB error has occurred. Error code: 0x80040E4D
An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80040E4D Description: "Login failed for user 'sa'."
Error: 0xC020801C at Data Flow Task - Extract my_file_src, Lookup - column1 from table1 [6012]: The AcquireConnection method call to the connection manager "MyConnMgr" failed with error code 0xC0202009.
Error: 0xC0047017 at Data Flow Task - Extract my_file_src, DTS.Pipeline: component "Lookup - column1 from table1" (6012) failed validation and returned error code 0xC020801C.
Error: 0xC004700C at Data Flow Task - Extract my_file_src, DTS.Pipeline: One or more component failed validation.
Error: 0xC0024107 at Data Flow Task - Extract my_file_src: There were errors during task validation.
SSIS package "MyPackage.dtsx" finished: Failure.
The program '[2136] MyPackage.dtsx: DTS' has exited with code 0 (0x0).
I guess I am making some basic mistake.
Somebody please throw some light.

Thanks in advance.
NiteshYou might open up the package configuration file (C:\src\myconfig.dtsConfig"), and see if it has an entry for username "sa" in it -- the error you posted suggests that it has an incorrect password for that account.
|||Also, Note that the configuration message is simply an informational message that let's you know that the package is getting configured. It's not an error or warning.
Look at the open package and verify that the password is correct for the connections.
K

package configuration for generic pathnames and specific filenames

It's very easy to make a generic xml configuration file for the connection to the database for all packages my project contains. The connectionstring is the same for all these packages

Now I want to do the same for all flat file sources I have in my project where the connectionstring is not the same for all packages.

For example I use the following flat files in my project:

c:\AAA.txt

c:\BBB.txt

c:\CCC.txt

If I move the packages to the production database the flat file sources are located in another directory.

So in fact for the flat file sources there is a generic part for all files (in this case 'c:\ ') and a specific part ('AAA.txt', 'BBB.txt' and 'CCC.txt').

Can I indicate this in the package configurations sections? And how?

Thanks,

John

Create two variables in your package. One to hold the "generic" part of the path, the other to hold the specific part of the path. Include these two variables in your configuration file so that you can alter them.

Then using expressions on the flat file source, you can concatenate the two variables to form the full path, which is dynamically adjusted based on the configuration file.|||

Using expressions in combination with the variables solved my problem.

Thanks a lot Phil

Package Configuration - XML

Type:
Configuration File

New configuration file will be created.

File name:
C:\Test.dtsconfig

Properties:
\Package.Connections[Flat File Connection Manager].Properties[ConnectionString]

So lets say, I modify the connectionstring for the Flat File connection manager by editing the xml file. Next time, I run the package, it does not grab the new value. Am I misunderstanding the concept of Package Configuration?

No, you're not misunderstanding. This should be working. if the package uses a configuration file then messages will be output to the Output window indicating this. My best guess is tat something is configured incorrectly.

Also, are you using checkpoint files? If so, make sure that no checkpoint file is present to make sure that the package starts from scratch.

-Jamie

|||

Well what I did to get it working was the following:

On my config file i'm putting the values of variables, and i mapped the expressions to the variables. Thus, when I edit the xml file, I can change the value of lets say username to Bob and in return since i have it mapped, it automatically takes that value and applies it according to the connection.

|||The thing that usually gets me with configuration files is case sensitivity. A Source connection is not the same as a source connection. So if you have SuppressConfigurationWarnings attribute in your package set to True, the configuration file property is not used and you're not even aware you have a problem. If I had a dollar for every time...

Package Configuration - XML

Type:
Configuration File

New configuration file will be created.

File name:
C:\Test.dtsconfig

Properties:
\Package.Connections[Flat File Connection Manager].Properties[ConnectionString]

So lets say, I modify the connectionstring for the Flat File connection manager by editing the xml file. Next time, I run the package, it does not grab the new value. Am I misunderstanding the concept of Package Configuration?

No, you're not misunderstanding. This should be working. if the package uses a configuration file then messages will be output to the Output window indicating this. My best guess is tat something is configured incorrectly.

Also, are you using checkpoint files? If so, make sure that no checkpoint file is present to make sure that the package starts from scratch.

-Jamie

|||

Well what I did to get it working was the following:

On my config file i'm putting the values of variables, and i mapped the expressions to the variables. Thus, when I edit the xml file, I can change the value of lets say username to Bob and in return since i have it mapped, it automatically takes that value and applies it according to the connection.

|||The thing that usually gets me with configuration files is case sensitivity. A Source connection is not the same as a source connection. So if you have SuppressConfigurationWarnings attribute in your package set to True, the configuration file property is not used and you're not even aware you have a problem. If I had a dollar for every time...

package configuration

Does it matter if the configuration is by xml,sql server,parent package variable,environment variable etc.

I use xml configuration which i kind of feel easy as i can change the connection directly by typing.Not used with others much..

Is there any security issue here .

Please let me know

Hi,

I use XML too and I pass it from environment variable which gives more portability.

For security, yes, this is a concern. my XML config holds the user name and password in the connection string itself.

You have to secure the folder where you store the config file.

A server admin will always have the possibility to look into it though.

Regards,

Philippe

|||

SQL Server table has the added benefit that you can add as many configurations as you want and you won't see any warning if they all are not used in a package (not to mention you go to a single place where a change is required). Obviously, you need to store the connection string to that table somewhere else, and likely you would use the 'indirect' method or a second (XML, env. variable, etc).

The Environment variable and Registry key types, require a high level of permission at the OS level; which in some scenarios can not be an option (security policies, etc).

Parent variable type has the limitation that you don't have control over the order on which they

take place. They always get configured in the last place.

As you can see there is no perfect method, but yet you have a several options.

|||

When I store configuration information on sql server, is it safe to assume that I just the need the connection for that sqlserver/table and I'm done?

Also is it possible to directly insert/update/delete data to that table?

Thanks

|||

Yes (I think, depends on exactly what you are asking), and yes.

overvue

i want to build an xml based, driven web site.......... with sql......... in c#.

However i looked into visual web developer over a couple of weeks and

liked some of the display tools......then when i went back into c# i noticed

some of the display tools werent available and vis versa.

Also in vis web dev there does not seem to be the "under the bonnet"

dataset...person binding source etc.

The question is which way should I go?

Can I build and refine a sql in c# and then drop it into vis web dev or am I

just making a lot of work for no reason.

I just want to start correctly and not end up in some sort of swamp.

I don��t know if I got your ight, but the drag and drop functionality is mainly available through the GUI designer of the webforms / controls. After dragging and dorpping the controls on the form the code behind will automatically produced and can be changed afterwards.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Jens.... thanks again

have included this time the exceptions .....is this any help.