May 8, 2014 at 11:35 am
I am developing a SSIS 2012 package that basically works with a bunch of incoming Excel spreadsheets and does ETL on them to get the dates into a more highly desired Excel schema via an Excel template file.
I understand that I can go the route of using an XML configuration file to allow numerous variables to have their values changed via easy edits of the XML configuration file once moved to the production location where the package will run.
The a bit confused over how to use an INDIRECT XML configuration option to allow the path and name of the configuration file to be dynamically recognized and work appropriately in both my development environment and the production environment.
What I think I understand is that if you don't want to have to do additional work to have the different environments know dynamically where the XML configuration files are located via creation and usage of an Environmental Variable, the developer can simply cost to put the XML configuration file in the same location where all of the SSIS package files are located and that the process will find the XML configuration File in that location. Otherwise, if you want the XML config file to reside in a completely different location, you must create both a variable within SSIS as well as an Environmental Variable and somehow utilize both of those variables.
Anything that can be communicated to me to make this a little clearer to me is much appreciated. I think I'm on the general right track with my understanding but I know I may be off a little. Examples and/or useful links any of you have found to related articles/tutorials that have helped you with this are appreciated, too, if you have any to share.
Thanks in advance!
May 8, 2014 at 12:10 pm
As you're using 2012, can I assume that you are using the project deployment model? If not, can you explain why?
The configuration methods you mentioned are superseded in 2012. You now have a shiny new SSISDB database which can contain 'Environments' to handle this config.
Please have a read of this as an introduction and let us know whether it makes sense.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
May 8, 2014 at 12:32 pm
Correct... using SSIS 2012. I'm familiar with SQL Server and have worked with it on/off for 15+ years, but this project is my first new stab at SSIS specifically.
I'll read the link you provided and will reply back if I have additional questions.
Many thanks for your time in responding to me and providing the link I'm about to read.
May 8, 2014 at 3:00 pm
I must say, the Project Deployment Model as opposed to the Package Deployment Model seems overwhelming as far as the differences, the length of time it appears to take to go through all of the steps in creating the Integration Services Catalog, and all of the other major changes that seem to exist.
I do see where there are numerous additional features with the Package Deployment Model that aren't available with the Package Deployment Model, as well as streamlining and simplification of several areas, but it's still a bit overwhelming from what I'm reading.
I'm about 95% of the way there with the existing Package Deployment Model configurations I've been working on, whereas if I switch to the Project Deployment Model, it seems like there's a considerable amt. of work I'd have to do to do the conversion, setup everything in SQL Server and make additional changes.
Is there any compelling reason that I should absolutely move to the Project Deployment Model or is it still "safe" to stay with the Package Deployment Model since I'm almost there?
With the Project Deployment Model, I do understand that I'd have to create the Integration Services Catalog in SQL Server 2012 in my development environment, but would I *also* have to do this same Integration Services Catalog creation in SQL Server 2012 on the *production* location where the SSIS project & packages will ultimately be installed?
May 10, 2014 at 8:10 am
brad.mccollum (5/8/2014)
I must say, the Project Deployment Model as opposed to the Package Deployment Model seems overwhelming as far as the differences, the length of time it appears to take to go through all of the steps in creating the Integration Services Catalog, and all of the other major changes that seem to exist.I do see where there are numerous additional features with the Package Deployment Model that aren't available with the Package Deployment Model, as well as streamlining and simplification of several areas, but it's still a bit overwhelming from what I'm reading.
I'm about 95% of the way there with the existing Package Deployment Model configurations I've been working on, whereas if I switch to the Project Deployment Model, it seems like there's a considerable amt. of work I'd have to do to do the conversion, setup everything in SQL Server and make additional changes.
Is there any compelling reason that I should absolutely move to the Project Deployment Model or is it still "safe" to stay with the Package Deployment Model since I'm almost there?
With the Project Deployment Model, I do understand that I'd have to create the Integration Services Catalog in SQL Server 2012 in my development environment, but would I *also* have to do this same Integration Services Catalog creation in SQL Server 2012 on the *production* location where the SSIS project & packages will ultimately be installed?
It's an interesting question. If it were me, I would bite the bullet and change, but maybe that's because I'm familiar with the tech.
Creating an SSISDB takes just a few seconds - don't worry about that. But DO worry about the default settings within the catalog if you decide to go down this road.
Actually, your dev environment is the one place where you probably don't need the catalog - we don't have one there. Our developers tend to unit-test their packages in Visual Studio and then, when they're happy, the project gets deployed to QA and onwards where we do system testing.
One substantial difference is in package deployment. Entire SSIS projects (which often comprise many packages) get deployed at the same time. Individual packages cannot be deployed in isolation (unless you have one project per package - and don't do that!). This used to concern me, but now I'm used to it, I prefer it.
I haven't taken the time to look for links to justify using the project model, which you have no doubt done already. If you have any specific questions as a result of your investigations, feel free to post again and someone here will help, I'm sure.
One final justification: having project-level parameters and connections is worth the upgrade effort alone. Your connections are defined once at project level and are then available to all packages in the project.
--edit: fixed typo
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply