Find and Replace SSIS Package

Question

Find and Replace SSIS Package

Welsh Corgi

SSC Guru

Points: 116520
More actions
March 19, 2012 at 12:02 pm

#253304

I have three package that load 69 tables from an AS400.
I need to load another 69 tables with exactly the same table names but a different schema.
Is there a way to find and replace in SSIS?
Can't you view the XML and change that?
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/

Viewing 12 posts - 1 through 11 (of 11 total)

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

Elliott Whitlow SSC Guru Points: 102296 More actions · Answer 1

You could probably edit the XML directly but don't do a replace all, I'd do a replace and look at each one just to be sure but it would still be less painful to do it that way than search and type and search and type.

CEWII

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

Dumb question but what what is the best way to edit the XML?

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/

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 3

Little known usage of SSMS, but it'll open XML quite happily. Eithe ruse File|Open or simply have SSMS open, go to Windows Explorer and click|drag the file into SSMS.

Steve.

GRussell31 SSCrazy Points: 2116 More actions · Answer 4

Could you use a broker package that would reuse the existing packages that are there?

Each package could load in the details of the source and destination depending on a number passed down to it (which could be specified by the broker).

So to give an example...

Broker package has a for loop which would call a SQL query that would return 2 rows from a table, these two rows would contain an ID for each use of the package (so 1 and 2).

This variable would be passed down to the package that does the work.

In the worker package it would execute a SQL task which would get the SQL for the source and destination and load the queries into a variable which the data flow task would use.

Hope that helps?

Another option would be to create a template package (http://support.microsoft.com/kb/908018) although I've not tried that myself.

Graham

Andy Hyslop SSCrazy Eights Points: 9198 More actions · Answer 5

Another option would be to create a template package (http://support.microsoft.com/kb/908018) although I've not tried that myself.

I use template packages quite a bit (one for each of my data warehouses) and I find they work quite well 😀

==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe

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

stevefromOZ (3/19/2012)
Little known usage of SSMS, but it'll open XML quite happily. Eithe ruse File|Open or simply have SSMS open, go to Windows Explorer and click|drag the file into SSMS.

The File open did not work for me. I can edit in Notepad.

If you drag the file into SSMS how do you save the file?

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/

sam.dahl Hall of Fame Points: 3112 More actions · Answer 7

You can of course just do your work in notepad and save it as per any file.

SSMS shouldn't be any different.

P.S. In my experience, whilst template packages work well their usefullness is negated if the package you intend to create is more closely matched to an existing package than your template. Simply copy the existing package and modify the connections etc.

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

sam.dahl (3/20/2012)
You can of course just do your work in notepad and save it as per any file.
SSMS shouldn't be any different.
P.S. In my experience, whilst template packages work well their usefullness is negated if the package you intend to create is more closely matched to an existing package than your template. Simply copy the existing package and modify the connections etc.

All that I need to change is the schema and the descriptions for each task, etc to reflect another schema.

Notepad it is.:-)

Thanks.

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/

Elliott Whitlow SSC Guru Points: 102296 More actions · Answer 9

sam.dahl (3/20/2012)
You can of course just do your work in notepad and save it as per any file.
SSMS shouldn't be any different.
P.S. In my experience, whilst template packages work well their usefullness is negated if the package you intend to create is more closely matched to an existing package than your template. Simply copy the existing package and modify the connections etc.

I would generally agree, however I usually go a step further and also make sure that I review, major and minor version and reset build version to 0 (zero). I will also sometimes change the creation properties stored to reflect what I am doing.

CEWII

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 10

Welsh Corgi (3/20/2012)
stevefromOZ (3/19/2012)
Little known usage of SSMS, but it'll open XML quite happily. Eithe ruse File|Open or simply have SSMS open, go to Windows Explorer and click|drag the file into SSMS.
The File open did not work for me. I can edit in Notepad.
If you drag the file into SSMS how do you save the file?

I run this query (have it saved as a snippet) and then click on the XML in the resultset to open the XML editor.

SELECT CONVERT(XML, '<root/>');

From here you can copy/paste new XML into the window.

File>Open (Ctrl+O) to open new XML, even when starting in a SQL Query Window should work fine though. If not, check the encoding of your saved XML file. SSMS tends to play games with files saved as Unicode with no signature at the beginning of the file (e.g. 0xFF 0xFE).

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

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

Thats pretty slick. I revised the 3 SSIS Packages on Friday.

Thanks again.

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/