April 10, 2012 at 7:06 am
Hello all,
i want to know how do i use connection manager dynamically (flatfile, excel, oledb.) . That is when i want to reuse existing package by changing required filelds in connection manager without touching the package.
April 10, 2012 at 10:38 am
I recommend using SSIS Package Configurations. Here is a video demonstrating how to get started:
SQL Share > SSIS Configurations
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 10, 2012 at 6:01 pm
The easy way to switch it at run-time is through variables:
1.) Statically build the connection manager you require (OLE DB, FlatFile, Whatever)
2.) Build package-level variables to hold things like ServerName and InitialCatalog (for OLE DB).
3.) Highlight the static Connection Manager you built, and go to the "Properties" window; find "Expression" and hit the "..."
4.) In the Property Expressions Editor, map your variables to the different Properties (i.e. - Property "Server Name" Expression @[User::ServerName])
All done. Now whenever you execute the package, just supply values to your variables, and the package dynamically points the Connection Manager.
There are other ways to do this, like the SSIS API in .NET, but that would be WAY TOO MUCH work just to change connection settings.
Josh Lewis
April 10, 2012 at 8:23 pm
thank you that was really helpful
April 10, 2012 at 9:09 pm
Glad I could help...most of the properties of almost EVERYTHING can be configured by variable or expression in SSIS Packages
Josh Lewis
April 11, 2012 at 9:04 am
Using variables, expressions and command line parameters is overkill in a sense. For the record though, you can set the property of anything in the package directly using command line parameters...no need to setup variables and expressions on top of that. For example, let's say I have a Flat File Connection in my package named MyDataFile and I want the file to be written to a different place in my development environment than in my production environment...a common scenario. If I want the file to be written as D:\Data\SomeData.txt I would call the package this way:
DTExec /F "D:\SsisPackages\Package1.dtsx" /Set \Package.Connections[MyDataFile].Properties[ConnectionString];D:\Data\SomeData.txt
This is simpler than setting up variables and expressions to abstract the properties. Setting up variables and expressions may be a preference for you that adds flexibility though, so your mileage may vary.
At the end of the day, whether using Package Configuration files, variables & expressions, command-line-sets or some other method (there are many others), as long as you arrive at a place where you can move an SSIS package to another environment and have it behave as needed by only changing configuration data, i.e. without changing the package itself, you have won.
EDIT: fix wrapping on command line example
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 11, 2012 at 12:32 pm
I totally agree opc.three; I guess it depends on why you need to change the Connection. If your looking at it from an infrastructure perspective, and it's to make the connections update automatically as a package moves through Dev, QA, UAT, Prod, package configs are the way to go. At that point, I even prefer to use Registry on the SSIS servers to hold the config, so I don't have to worry about tweaking a config file when I deploy.
If you have the package, and you need to run it against 20 different servers regularly, using expressions inside the package to tweak makes more sense; Build a server list, wrap your package in a parent package that iterates the list providing the different Connection config on each iteration.
Just my two cents
Josh Lewis
April 11, 2012 at 12:42 pm
DeusExDatum (4/11/2012)
I totally agree opc.three; I guess it depends on why you need to change the Connection. If your looking at it from an infrastructure perspective, and it's to make the connections update automatically as a package moves through Dev, QA, UAT, Prod, package configs are the way to go. At that point, I even prefer to use Registry on the SSIS servers to hold the config, so I don't have to worry about tweaking a config file when I deploy.If you have the package, and you need to run it against 20 different servers regularly, using expressions inside the package to tweak makes more sense; Build a server list, wrap your package in a parent package that iterates the list providing the different Connection config on each iteration.
Just my two cents
Agreed, it depends on what you're doing. Re-reading the question, it's a bit vague as to the intent. I think you got closer than I did, but for future readers I thought it important to point out additional aspects of making something dynamic, and more specifically that variables and expressions are optional depending on the usage.
On another note, I was taken aback by your Registry comment. You are the first person I know of who has admitted (joking) to using the Registry entry for SSIS configurations! I assume it works as advertised otherwise you would not be using it. I suppose a .reg file can be checked into a version control system just as an XML file could, however not deployed using xcopy (maybe that's a good thing). Use of the Registry comes with well-known baggage, so I am curious, what made you start there rather than using an XML file?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 11, 2012 at 1:12 pm
Well, I guess it all depends on how well the infrastructure is defined. Let's say I have 3 app specific DB and three environments:
App1
Dev - Server "DevServer", DB "App1DBDev"
QA - Server "QAServer", DB "App1DBQA"
Prod - Server "ProdServer", DB "App1DBProd"
App2
Dev - Server "DevServer", DB "App2DBDev"
QA - Server "QAServer", DB "App2DBQA"
Prod - Server "ProdServer", DB "App2DBProd"
App3
Dev - Server "DevServer", DB "App3DBDev"
QA - Server "QAServer", DB "App3DBQA"
Prod - Server "ProdServer", DB "App3DBProd"
Now when I set up my SSIS server (And I'm a strong believer in Standalone SSIS servers), depending on which environment I'm intending it be allocated to, I set up three reg configs for App1, App2, and App3 with the connection settings for that environment. When ever I develop a package, as long as I adhere to the Naming Convention I used for the registry settings, the package is always pointed to the correct server / DB as soon as it is deployed.
The main reason I find it handy is because I'm not touching it every time I deploy a package; It gets setup once based on my knowledge of the infrastructure, and then I modify it in a more global sense as I expand / change. Also, access to the registry is limited to the SysAdmins and the SSIS Service account, so I'm less likely to run into un-authorized modification.
I guess I think of the connection manager itself in the package to be the "Code Artifact" that I would want source controlled with all the properties set that assure it works with the Connection Type it is built for, but as far as how that connection endpoint is configured (Server/DB or Path) is more of a "Network Configration" to me (Like a mapped drive).
Again, this is just my personal preference when the environments are well documented; And you could still accomplish the same thing with shared config files (XML). 🙂
Josh Lewis
April 11, 2012 at 4:42 pm
Now when I set up my SSIS server (And I'm a strong believer in Standalone SSIS servers)
Agreed! SQL 2012 makes it much easier to have them too, although I have not yet had the pleasure to work with it in a production capacity.
The main reason I find it handy is because I'm not touching it every time I deploy a package;
My XML configuration files do not change often. In keeping with a standard build-and-deploy method I can overwrite the config files as many times as I want, with no impact since nothing has changed...I guess I trust that my version control repo is correct so I do not worry about that. How do you manage other configuration data besides just the app and db servers? or do you keep everything in the registry?
It gets setup once based on my knowledge of the infrastructure, and then I modify it in a more global sense as I expand / change. Also, access to the registry is limited to the SysAdmins and the SSIS Service account, so I'm less likely to run into un-authorized modification.
It would depend on the environment as to the likelihood of unauthorized modifications occurring. Depending on the division of responsibilities one environment's strength is another's weakness.
I guess I think of the connection manager itself in the package to be the "Code Artifact" that I would want source controlled with all the properties set that assure it works with the Connection Type it is built for, but as far as how that connection endpoint is configured (Server/DB or Path) is more of a "Network Configration" to me (Like a mapped drive).
Again, this is just my personal preference when the environments are well documented; And you could still accomplish the same thing with shared config files (XML). 🙂
Thanks for sharing your experience. It's true we can accomplish the task in many ways. As technicians, we must know the options. As technologists though, the fundamentals should be constant from one implementation to another. You have definitely thought through your environment config and I respect your points, but I must disagree with the parallel you drew between a mapped drive and a configuration driving the behavior of an SSIS package. I do not see them in the same light. The specific idea of a mapped drive aside because I avoid them at all costs in favor of UNC paths where the host name is abstracted, a system configuration is different from an application configuration which is different from a database configuration, and we should be capable of managing all three independently. That is not to say that a system configuration cannot be scripted and versioned, but I see that as being outside the purview of an application developer. I look at a network resource as a presented interface, a contract if you will between the system admins and the application developers, the same way that a database instance and database name is a contract between a database admin and an application developer. The application is granted the right to assume those resources will be there, and if they are not it falls to someone other than the application developer to make them available again or provide an alternate. It is also the right of the system admin or database admin to change the hardware behind that interface as needed (e.g. system admin replaces file server but supports same UNC paths; or database cluster fails over). That said, I want to be able to change my apps when my interfaces change and I want that versioned in a config file, tucked in close to my code.
I think in general the registry has long since fallen out of favor as a place to store configuration data for portable applications...mainly because it is not very portable. I guess that could be viewed as a strength as well, as usual, it depends.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply