March 3, 2011 at 12:58 pm
If I have a project with three different packages under it, and I need to add the same three steps to each control flow (enhancing the current packages, which are different, so I don't want to copy and paste the entire package) is there a way to copy just one task or a group of tasks from the one package I've completed this in, and then paste into the other packages? The added steps are the same, but the overall packages are not.
Thanks,
Jon
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
March 3, 2011 at 1:49 pm
I'm not sure if you can, but I have to ask why you would.
Couldn't you have those three steps in one package, and have each of the three call that package in their context?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 3, 2011 at 2:17 pm
um, 'cause I didn't think of that? 😛
Still wonder why I can't just copy/paste, but that works easier.
Thanks Gus!
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
March 3, 2011 at 2:21 pm
jcrawf02 (3/3/2011)
um, 'cause I didn't think of that? 😛Still wonder why I can't just copy/paste, but that works easier.
Thanks Gus!
You can, it just gets a little grumpy about connection names and a number of things. I usually only copy and paste in the same package.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
March 3, 2011 at 9:00 pm
Yes, absolutely.
BUT... before you do, consider expressions, variables and other hidden "gotchas" that you've attached to those tasks that will cause the task to fail once you've cut-n-pasted it to the other package.
I've done it many times before and gotten caught by that variable or expression that is hidden somewhere you never think to look. Then the package starts complaining about corruption because it can't find X goody and then you spend more time fighting the package than you would have if you just recreated the task from scratch.
I've even copied connection managers over from package to package. Variables don't seem to work, though. Just variable names.
March 3, 2011 at 10:17 pm
Heh... or just do it all in T-SQL stored procedures. :-D:-P;-)
--Jeff Moden
Change is inevitable... Change for the better is not.
March 3, 2011 at 11:34 pm
You can copy paste, but as Brandie said, watch out for "hidden" things like expressions and variables.
You are most likely to reconfigure all the connection managers, as SSIS keeps an ID for each connection managers that changes between packages.
So when you copy paste a task with a connection manager, it will not recognize the connection manager anymore, even if you have the exact same one in the other package.
Copy paste from 2008 to 2005 (or vice versa) can have some issues.
And copy pasting screws up your layout. Badly...
Annotations cannot be copy pasted. (When will they fix that?)
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 4, 2011 at 4:23 am
Koen Verbeeck (3/3/2011)
So when you copy paste a task with a connection manager, it will not recognize the connection manager anymore, even if you have the exact same one in the other package.
Not necessarily true. I've only had a problem with connection managers when the variable / expression issue comes up. Otherwise, SSIS recognizes the connection manager just fine.
For me, it does, anyway.
March 4, 2011 at 4:51 am
Brandie Tarvin (3/4/2011)
Koen Verbeeck (3/3/2011)
So when you copy paste a task with a connection manager, it will not recognize the connection manager anymore, even if you have the exact same one in the other package.Not necessarily true. I've only had a problem with connection managers when the variable / expression issue comes up. Otherwise, SSIS recognizes the connection manager just fine.
For me, it does, anyway.
When I copy an Execute SQL Task from one package to another, the connection manager in the destination suddenly becomes some sort of GUID, and I have to choose the correct connection manager again. Maybe it is because in the destination package the connection manager doesn't exist yet, so SSIS doesn't know what to do with it.
Or maybe SSIS just likes you better 🙂
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 4, 2011 at 4:54 am
Koen Verbeeck (3/4/2011)
Brandie Tarvin (3/4/2011)
Koen Verbeeck (3/3/2011)
So when you copy paste a task with a connection manager, it will not recognize the connection manager anymore, even if you have the exact same one in the other package.Not necessarily true. I've only had a problem with connection managers when the variable / expression issue comes up. Otherwise, SSIS recognizes the connection manager just fine.
For me, it does, anyway.
When I copy an Execute SQL Task from one package to another, the connection manager in the destination suddenly becomes some sort of GUID, and I have to choose the correct connection manager again. Maybe it is because in the destination package the connection manager doesn't exist yet, so SSIS doesn't know what to do with it.
Oh, I thought you were talking about copying & pasting connection managers themselves. Not tasks that had connection manager issues. You're correct about the later.
EDIT: Apparently it's too early in the morning. I missed half your sentence.
March 4, 2011 at 6:26 am
Ok, so aside from the alternatives (sub-package/sp's), and I know you're all used to folks just asking questions without trying first, but I actually TRIED to copy/paste, and it won't let me. It throws an error, tries to send Microsoft an error report, and tells me that "SSIS Designer could not serialize the SSIS runtime object". Is that a problem with my setup somehow that I just plain can't do it? I don't even get to the point of pasting it in, it throws that error when I copy.
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
March 4, 2011 at 6:28 am
Really? That's odd.
What type of objects are you trying to copy? And how are you trying to copy them? Across packages in different solutions or across packages in the same solution?
March 4, 2011 at 6:29 am
Jeff Moden (3/3/2011)
Heh... or just do it all in T-SQL stored procedures. :-D:-P;-)
How do I decide when to use subpackages vs stored procedures vs straight SQL tasks? I would have assumed (had using a sub-package occurred to me) that keeping things within the same package would run smoother than jumping to a different one, but I don't suppose a SQL task would be any different than executing a stored procedure.
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
March 4, 2011 at 6:42 am
Jeff Moden (3/3/2011)
Heh... or just do it all in T-SQL stored procedures. :-D:-P;-)
That doesn't work as well when you're processing some types of data.
For example, I built an SSIS solution that had to pull data out of batches of text files, process them against a third-party DLL (address validation and clean-up), process them against a web service (change-of-address registry), run them through a postal sort (another external DLL), and then only put ones that fit certain criteria into a table.
Possible to do through T-SQL? Only because I could have used sp_OACreateObject and its related pieces. Proof-of-concept work was done on that, and was slow, buggy, and required rebooting the server every 6 hours or so to clean up memory leaks. The SSIS and CLR version was 10 times faster, cleaner, easier to maintain/refactor, more stable, and caused no downtime.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 4, 2011 at 6:42 am
jcrawf02 (3/4/2011)
Jeff Moden (3/3/2011)
Heh... or just do it all in T-SQL stored procedures. :-D:-P;-)How do I decide when to use subpackages vs stored procedures vs straight SQL tasks? I would have assumed (had using a sub-package occurred to me) that keeping things within the same package would run smoother than jumping to a different one, but I don't suppose a SQL task would be any different than executing a stored procedure.
Jeff's a T-SQL evangelist who doesn't generally enjoy his exploits with SSIS, so expect a heavy bias in anything you read that's written by him 🙂 Personally, I think he's filled his brain with so much T-SQL it can't accommodate SSIS too 😀
There are no rules about when to use what, unfortunately and there are usually several ways of achieving the same outcome, some more elegant and efficient than others.
There are obvious portability attractions in keeping your package self-contained and that is worth striving for. SP's can improve performance if there is a lot of processing to be done. Sub packages come in useful if something has to be executed repeatedly, under the control of a master package. Just dive in and keep asking questions and you'll soon gain the requisite experience.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply