copy a data flow task to another package

Question

copy a data flow task to another package

sqlfriend

SSC Guru

Points: 52794
More actions
September 2, 2011 at 1:48 pm

#244255

I have created a ssis pacakge that has a data flow task.
Now I would like to copy this to another packages that has many other data flows.
Can I just copy it and paste to the target pacakges?

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

kramaswamy SSCoach Points: 18181 More actions · Answer 1

Short answer, "yes with a but"

You can copy any task from a package to another package. The problem comes when that task is using variables or connections that exist in one package but don't exist in the other.

For example, let's say you had a data flow task which had an OLE DB source and an OLE DB destination, which used connection managers to handle the connection to the database.

When you copy the data flow task to a new package, the connection manager ID needs to be the same. If they are, then it will work. If they aren't, then you'll get an error saying something along the lines of "connection manager with ID #### was not found".

That's not really a problem - all you need to do is open the OLE DB source, and change the connection manager to point to whatever connection manager exists in your new package. Same with the OLE DB destination.

Likewise, if you are using variables that exist in one package but not in the other, you'll just need to create those variables in the new package.

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 2

Just an FYI, Microsoft announced last week that they are aligning themselves with ODBC.

OLE DB is being deprecated and Denali will be the last version in which OLE DB is supported.

Microsoft is encouraging adopting ODBC in any future development.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

sqlfriend SSC Guru Points: 52794 More actions · Answer 3

Thanks.

My new and exiting package all have the same connections with the same name.

But maybe the ID are different, does it matter?

I read the article http://denglishbi.wordpress.com/2011/03/25/ssis-package-copypaste-new-guid-id-fix/

It seems it gets more complicated about both pacakge ID and objects ID in it.

And I'm not using SQL server 2008 R2, so this makes me thinking copy and paste could cause problems, but recreate consumes more time.

THanks

kramaswamy SSCoach Points: 18181 More actions · Answer 4

I regularly copy-paste objects from SSIS projects, and haven't had any problems with it. Best way is to just test it and see what happens.

sqlfriend SSC Guru Points: 52794 More actions · Answer 5

Thanks, good to know, and feel better now to copy and paste tasks.

kramaswamy SSCoach Points: 18181 More actions · Answer 6

sorry - just re-read your previous post and noticed the comment regarding SQL Server 2008 R2.

Are you trying to copy a data flow task from SSIS 2005 to SSIS 2008? If so, I can't guarantee that will work, since I've never tried to do that. My post was specifically directed towards copying objects from two different SSIS projects.

sqlfriend SSC Guru Points: 52794 More actions · Answer 7

I'm copying and paste data flow in different package but on the same version of sql server 2008

copy a data flow task to another package

Cookies on SQLServerCentral