We, unfortunately, don't have the notion of a file data source which it sounds like you're looking for. Basically, a way to create a connection that you can share across multiple packages.
You'll need to create a new connection per package.
K|||Kirk, not sure if I explained correctly. I'm trying to have 1 master package that executes child packages all on the same "ETL server". So I want 1 connection to be the "ETL Server" such that it can be changed in 1 place (actually, I would just have a runtime setting change it based on a config) when moving from DEV (each developer would have their own instance) to QA to PROD.
The reason I want many packages is so that multiple ETL developers can work on a component and build it without having to wait. Additionally, if everything was in 1 package, it would be huge.
Is the only way to accomplish this without having to change each package's sql server location is to reference it by its file location?|||OK, a few questions to clarify.
What kind of connection do you want the one connection to be SQL, file?
You have one package that you want to call multiple subpackages?
You want the subpackages to use the one connection?
I'm not sure what you mean by "1 connection to be the 'ETL Server' "
"Is the only way to accomplish this without having to change each package's sql server location is to reference it by its file location?"
I don't understand why you need to change the package's sql server location. Which package's SQL Server location? The parent package or the sub-packages?
I think what's confusing me here is that your description has a lot of moving parts. Can you simplify the problem into two or three root issues. I have a simple mind and can't deal with all the complexity. :)
|||Let me offer a scenario and you can tell me how it doesn't meet your needs.
You can create a parent package. It can call all the other sub-packages. When the subpackages execute, the parent packages connection is available to them. However, not at design time. I think this is the issue you're having. The sub-package doesn't have areference to the parent package at design time, so it doesn't have the connections either.
What I'd suggest is that you create a Data source per machine and have all the connections reference it. You'll still need to create a connection manager in each package, but they will all reference the same data source. Moving to different machines is no problem because you can have a "machine resident" data source file that properly points to your "ETL Server".
Is this what you're after?|||see responses below, thanks for taking the time to respond.
Q. What kind of connection do you want the one connection to be SQL, file?
A. The connection of the SSIS server where the subpackages reside.
Q. You have one package that you want to call multiple subpackages?
A. YES
Q. You want the subpackages to use the one connection?
A. YES
Q. I'm not sure what you mean by "1 connection to be the 'ETL Server' "
A. If I store all the subpackages on SQL Server, I'll bound to have different servers for different environments, etc.
Q.I don't understand why you need to change the package's sql server location. Which package's SQL Server location? The parent package or the sub-packages?
A. sub-packages
I'm not after the connections inside the subpackage from the parent packaeg, nor the connections in the parent package from the subpackage.
What I am looking for is to create 1 connection and tie it to where the subpackages are stored since it needs to be dynamic without having to spin through every execute package task in the master package and change the sql server it resides on at runtime.
Alternatively, I could create a new connection for every execute child package in my master package all pointing to the same SQL Server that is storing them, but its extremely redundant and would only be useful if I'm pointing to (LOCAL) and the user and password never changes.
Why can't the EXECUTE PACKAGE task just use one of the connections available in the "connections managers" collection? I thought that was the reason for the "connection manager" abstraction layer, so that a physical connection can be changed in one place, and all logical connections are automatically redirected?
Any chance of implementing this? Alternatively, implementing a FOR EACH connection enumerator (although, this would be hokey)?
Followup question would be this: If I'm executing a package from another package, what is the performance difference (if any) of executing a package in general stored in the file system VS. stored in sql server? I'm assuming that if its not stored in sql server, it needs to be brought in from the file system and compiled on its 1st use?
My goal is to create an ETL where multiple developers can work on different components at the same time. The easiest way to do this would be to create 1 package per table. A master package would call all the child packages, but the master package needs to know which ETL server all the child packages are stored in, which changes from environment to environment.
|||
"Why can't the EXECUTE PACKAGE task just use one of the connections available in the "connections managers" collection?"
It can, If all your execute package tasks are in the same package, yes, they can all use the same connection manager. Is this not working for you?
"what is the performance difference (if any) of executing a package in general stored in the file system VS. stored in sql server"
No performance difference between the two storage methods once loaded. Packages aren't compiled. They're read into the object model.
"The easiest way to do this would be to create 1 package per table. "
Really? Why is this easiest. Why limit it to one package per table. Why not just have developers develop package files and check them into sourcesafe? Then when they're done developing them, deploy to SQL Server? Not sure I follow this logic.
Please keep the threads cogent by only asking only one question per thread.
K
|||Hi,I'm coming into this late and I think I may be misunderstanding it all but let me have a punt anyway.
You have 1 master package which is intended to call multiple child packages.
You want 1 connection manager in the master package to be used to call all child packages.
You want to cycle through a list of all the child packages and execute them using 1 Execute Package Task
If that is all correct then I think what you want to do is possible using property expressions.
Have I understood correctly?
-Jamie|||"Why can't the EXECUTE PACKAGE task just use one of the connections available in the "connections managers" collection?"
It can, If all your execute package tasks are in the same package, yes, they can all use the same connection manager. Is this not working for you?
Whew, thats all i need, maybe its a bug - using June CTP. I create a new execute package task and then create a new connection for it. Then I create a 2nd execute package task, and click on the connection dropdown, but all that is available is New Connection.
|||
Ah! You're using SQL Connections. Dah!
Yes, this is a bug!
Thanks, great find. Please file a bug.
K
No comments:
Post a Comment