Edit a dtsx through SSMS

Question

I created and executed a dtsx with SSMS corresponding wizard:

This was to import a flat file in an existing table.

At the end I saved the "package" as a .dtsx file

Now I need to modify the column mappings and re-execute this package.

Is there any way I can do it, using SQL Server Management Studio?

I tried opening the file, but it opens this dialog:

Where I cannot edit the mappings any more.

Update:

I understand that "editing" a dtsx is not a simple thing, yet is there a reason why the wizard could not be run again with the values already pre-set? Like opening the wizard in the last step and navigate "back" on the previous steps. This is existing functionality after all...

Is there any trick I could do this? From command line maybe? This would suit my need fine.

Ternopol

You need to open the package with VIsual Studio (SSDT), i don't think you can achieve this using SSMS – Idonah

Here's the SSDT download link needed to edit SSIS packages. You could also go through the wizard again to specify the desired mappings. – Pieplant

Thank you, but the thing is that I cannot currently install any tools/software in this machine, so I try to find a way to do the work with SSMS, which is currently available. I assumed that I would have the ability to re-run the wizard on a saved package... after all, it was made with SSMS, shouldn't it be edited with SSMS?? :( – Ternopol

Editing existing packages is far more complicated that what you could do with a wizard. Keep in mind that .dtsx is just XML so you could edit it in notepad if you really wanted. – Tessellation

@Nick.McDermaid I will try in notepad. Yet, I cannot understand that "more complicated". Why the wizard could not be run again with the values already pre-set? If some value was changed in the first steps, then the preset values on rest of the steps should be discarded. Just like going "back" on an existing wizard! – Ternopol

Because the package is not stored as wizard steps. It’s a structure that can contains all kinds of stuff far beyond anything the wizard does. You’d need to write code that reverse engineers those wizard steps from something that is not guaranteed to even contain anything that’s in the wizard. Any program that tried to do that would be complicated, buggy, and a nightmare to maintain. It doesn’t make sense to build something that would never work properly. – Tessellation

It’s really not practical to edit in notepad but at least then you’ll get an idea of how difficult it would be to reverse engineer that structures back to wizard steps. – Tessellation

The most practical solution for you is to go through the wizard again – Tessellation

@Nick.McDermaid I really don't believe that it can be "far more complicated" to edit the dstx file, at least with the same SSMS instance you used to create it. After all, you can run the wizard, go through all the configuration, and when you get to the "save file" option, you can back up and change everything! As verbose as the XML is, it's still pretty obvious what maps to which part of the wizard. Besides which, both SSMS and SSDT are built on the same Visual Studio base. I'd understand this behaviour far better if SSMS had never had the wizard in the first place. – Obscure

@Obscure Even commercial SSIS package generation tools like BIML can't get package generation right. Why don't you give it a crack, maybe you're right through sheer force of logic. – Tessellation

Answer 1

The only way to edit .dtsx packages is using SQL Server Data Tools for Visual Studio (Business Intelligence Development Studio in older versions)

• Download and install SQL Server Data Tools (SSDT) for Visual Studio

SQL Server Management Studio can manage and schedule package execution it is not where Packages are edited.

There are some configurations that you can edit in SSMS before executing the package like changing connections, passing parameters values, assign variables values and some other option only.

Feeder

For the graphical view, one must also install the Microsoft SSIS extension for Visual Studio. Otherwise, VS will only open the DTSX files as ordinary XML files. – Selfmade

Misleading. You can open a .dtsx file created by the Imp/Exp wizard in Notepad++ and edit the xml. Simple. – Allanson

@Steve The OP stated that Now I need to modify the column mappings and re-execute this package. Are you recommending opening a dtsx file using a text editor to change columns mapping? – Feeder

Answer 2

I managed to do a very small change simply by opening the file in a text editor. But really just a minor change in the Query used before transfer. I was lucky to find the row to edit by searching "WHERE".

Sarmatia

Thanks. I was able to replace a ConnectionManager element so that I can copy and use my test server's .dtsx file on my production server. Even the encrypted password worked for me when I transferred it this way. – Curhan

Answer 3

No, I'm afraid not. The only way to edit .dtsx files is with SSDT/Visual Studio.

Uriiah

?? You can open the .dtsx file in Notepad++ and edit the xml. Simple. – Allanson

Answer 4

You can open a .dtsx file created by the Imp/Exp wizard in Notepad++ and edit the xml. Simple.

Allanson