x

SSIS Remodelling 30 packages in one shot

I have a situation where I need to make same changes or redesign 30 SSIS dtsx packages. Is there a way to do all 30 at one shot?
more ▼

asked Apr 12, 2012 at 04:32 PM in Default

zainssisJunkie gravatar image

zainssisJunkie
0 1 1 1

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

Well, depending on what you are attempting to change, I'd say yes.

The packages themselves are just XML, so in theory, you could use a tool such as Notepad ++ or Powershell to locate what you want to change and then change it.

However, I've never done this so I would strongly advise that you test the changes thoroughly to ensure that everything is working as expected.

Hope this helps!
more ▼

answered Apr 12, 2012 at 04:44 PM

JohnM gravatar image

JohnM
6.9k 1 3 7

You mean, I can open the dtsx packages through Notepad++ or Powershell and make the changes?

Precisely, I am trying to change the design of the packages by putting a split in the Control flow by including sequence containers for each package.
Apr 12, 2012 at 05:04 PM zainssisJunkie
that might be a bit beyond the scope of an xml edit
Apr 12, 2012 at 05:12 PM KenJ

I would agree with @KenJ in that making that particular change is probably beyond the scope of what I suggested. Adding a split in the control flow is probably not something that I would try to script out.

@Shawn Melton also had a good suggestion on some third party tools. I, however, don't have any real knowledge on either of them.
Apr 12, 2012 at 05:19 PM JohnM

As mentioned several time here. It's easy to edit the XML. I'ts ideal for adding common variables to multiple packages, adding/removing/altering configuraionts, logging options etc. This you easily achieve by any Text/XML Editor. (vent in BIDS when you open the source instead of designer).

Othe othe other side, changes to the package structure especially affecting constraits is not so easy task for an editor. It would be possible tow rite a PowerShell script of a light .Net application which utilize SSIS assemblies for handlng the packages. Changes in the control flow are not so problematc and is should be realively easiy to add a sequence ad put control flow items into it. On the other side, altergin data flow transformations is much more complex :-).

Anyway for 30 packages probably the uqickest way will be to open them and add the sequence container manually.
Apr 13, 2012 at 05:30 AM Pavel Pawlowski
(comments are locked)
10|1200 characters needed characters left

With the changes you suggested in your comment it seems like changing the XML code on each file with a script is not really going to be an option. Trying to make that change in the XML code would be time consuming and probably not possible without screwing something up. Your safest bet is to just open each package up in BIDS and make your changes.

You might consider looking at SSIS 3rd party software that can help with this and future projects. Pragmatic Works has BIxPress that can create a template to create new packages or alter current ones. Another software package that I have heard of is [CozyRoc][2], but I don't know much about it.

[2]: http://www.cozyroc.com/
more ▼

answered Apr 12, 2012 at 05:16 PM

Shawn_Melton gravatar image

Shawn_Melton
5.3k 20 21 29

(comments are locked)
10|1200 characters needed characters left

I did have the same feeling. But, just wanted to check if there is any fast and safe available options. Since, I don't have enough time with me.

I think I have to open each of them and make the changes.

Thanks Guys!
more ▼

answered Apr 12, 2012 at 05:41 PM

zainssisJunkie gravatar image

zainssisJunkie
0 1 1 1

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x939

asked: Apr 12, 2012 at 04:32 PM

Seen: 605 times

Last Updated: Apr 13, 2012 at 05:30 AM