Tuesday, March 27, 2012

Configuration Packages are still in Beta

I would just like to give my feedback on the configuration packages implementation in SSIS:

THIS STUFF SHOULD BE A NO BRAINER AS IT IS A COMMON REQUIREMENT ON ETL PROJECTS.

I have trolled through these forums and found that I am not alone on this issue. Note for SSIS Team: Please bring out a HOWTO on this subject that details all the little work arounds because intuitively used it does not work.

Consider this:

I develop in BIDS on my client and everything runs. I create a config package and I choose only the Name and ConnectionString Properties for each Connection Manager. (Thanks to Jamie for that tip)

I build the app and a deployment utility is created. I navigate to the deployment directory, and deploy. I point to a mapped share on the production server, change all the properties for production and continue until finished.

On the production server I run a script containing:

dtexec /FILE D:\SSIS\Source\code\Source.dtsx >> D:\SSIS\Source\logs\Source.log

I get an error: Cannot find the configuration file: source.dtsConfig

I tried putting the following in the script:

dtexec /FILE D:\SSIS\Source\code\Source.dtsx /CONFIGFILE D:\SSIS\Source\code\source.dtsconfig >> D:\SSIS\Source\logs\Source.log

Still: Cannot find the configuration file: source.dtsConfig

I have to MANUALLY edit the dtsx file to put the path in from the the name of the config file and THEN it works.

Thinking that it might not like a mapped share from desktop to deploy to, I copIED the contents of the depLoyMENT directory on my desktop to a folder on the production server and deploy from there. It should pick up the proper path from there. NOT!!!!

So it was back to editing the dtsx file.

So there you have it. I await all comments with great interest.

I hope this is all fixed in SP2, Because I don't want to waste anymore time on what should be a simple task.

Michael Morrissey.
ETL and BI Developer.

if i'm not mistaken, your issue cries out for an indirect configurations solution: http://dotnetjunkies.com/WebLog/appeng/archive/2006/05/30/indirectconfigpackagessis.aspx|||

...one year later

I run on a similar problem - I created xml configuration file where I put the destination server's connection string, planning to use another sql server configuration table where it would be convinient to change some package properties before running.

And as I understud, with the /Conf tag I could change the path of the xml conf file, but I could not, I dont know if I am wrong somewere but the /Conf tag for me just did not works.

Later I realized that the deploying utility changes the path in the package itself, so maybe that tag is not needed (better, less characters Smile)

So I overcomed that question and resumed with that combination, it is just great for me like that.

I tryed with environment variable and with registry, but because of less privilegies on production I just could not make them work.

Conclusion: there are a lot of catches about SSIS, but it is interesting, it's foundations are good.

|||

Berzat,

Package configurations are working, and work well for most scenarios.

If you searchyou will find tons of working examples. Here is mine http://rafael-salas.blogspot.com/2007/01/ssis-package-configurations-using-sql.html

Notice that it uses an env. Variable; but you could use an XML file instead and get the same functionality.

|||

Thanks Rafael, indirect configuration is something else.

The /conf option for direct configuration does not working as it is pointed in the ssis documentation and nobody wants to confirm that or to prove the oposite...

|||

Bertzat,

You could prove it then; You just have to provide a 'repro'; a series of steps that everyone can follow and get the same results. Then post it in the connect site; the SQL server team will appreciate it.

|||

:-) I don't know if you are kidding me or not, but there are tens of articles where people are wasting time trying to work out their solutions with the /conf option (like Michael Morrissey on the top of this article).

Isn't that enough?!

|||

Berzat wrote:

:-) I don't know if you are kidding me or not, but there are tens of articles where people are wasting time trying to work out their solutions with the /conf option (like Michael Morrissey on the top of this article).

Isn't that enough?!

I believe Michael was using relative file paths to point to his configuration file. Absolute paths are a requirement inside SSIS. And then when deploying, you need to ensure that the file path exists and the config file resides there. So no, it's not enough. If you are having a different issue, then please post your steps necessary to recreate your issue.

|||Phil, I agree with your points, but I did want to mention that absolute paths are not required - though they are a good practice and head off a lot of troubleshooting issues. If you are extremely careful, you can use relative paths. However, I really don't recommend it as general practice.

sqlsql

No comments:

Post a Comment