17

I created and executed a dtsx with SSMS corresponding wizard:

enter image description here 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:

enter image description here

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.

cnom
  • 3,071
  • 4
  • 30
  • 60
  • 2
    You need to open the package with VIsual Studio (SSDT), i don't think you can achieve this using SSMS – Yahfoufi Jan 22 '19 at 11:37
  • Here's the [SSDT download link](https://learn.microsoft.com/en-us/sql/ssdt/download-sql-server-data-tools-ssdt) needed to edit SSIS packages. You could also go through the wizard again to specify the desired mappings. – Dan Guzman Jan 22 '19 at 11:39
  • 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?? :( – cnom Jan 22 '19 at 11:51
  • 2
    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. – Nick.Mc Jan 23 '19 at 00:41
  • @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! – cnom Jan 23 '19 at 07:47
  • 1
    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. – Nick.Mc Jan 23 '19 at 07:57
  • 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. – Nick.Mc Jan 23 '19 at 07:58
  • The most practical solution for you is to go through the wizard again – Nick.Mc Jan 23 '19 at 07:59
  • @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. – Auspex Sep 09 '19 at 09:46
  • @Auspex 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. – Nick.Mc Sep 09 '19 at 12:43

3 Answers3

5

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

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.

Hadi
  • 36,233
  • 13
  • 65
  • 124
  • 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. – Zarepheth Sep 12 '22 at 18:29
4

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".

Guppy
  • 426
  • 5
  • 9
  • 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. – Ac Hybl Aug 25 '23 at 18:29
2

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

Tab Alleman
  • 31,483
  • 7
  • 36
  • 52