0

SSDT has the Script Task that allows developing custom code in C# that's able to process data inside SSIS streams.

But this solution is very ugly, because every time we need to edit that code a Visual Studio instance is opened, and it must be closed so we can execute the dtsx. And then when it's executed inside SSDT, a VS instance is opened again, and when the execution finishes it's closed.

Is there a way to, instead of opening VS from SSDT, to develop code directly in VS, referencing SSIS API, and then compile it into a dll and embed it in SSDT?

Or, at least to keep VS opened and "bound" to SSDT, so we can develop and use its debugger?

Hikari
  • 3,797
  • 12
  • 47
  • 77

1 Answers1

1

If you only care about separation of coding and SSIS you can just try to have it as an application(.exe) and have it executed using Execute Process Task.

In that way you separate coding from SSDT, also you can now have an option to store it in a git repo to track changes and just do deployment on the specified path of your .exe that you set in the Execute Process Task.

In debugging it, I think VS has a capability to use Attached to Process then when your SSIS hits your app it will trigger breakpoint. But take note that you must point your Execute Process Task to .exe generated by debug mode.

Hope that helps.

Jeric Cruz
  • 1,899
  • 1
  • 14
  • 29
  • 1
    wow thanks a lot!! I didn't know about this Execute Process Task. That brought me another idea, of doing the opposite. .net starts running, verifies what needs to be done, then uses a MSSQL command to start dtsx execution from MSSQL Agent, waits it to finish, then continues. – Hikari Sep 27 '17 at 18:29
  • yep, that depends on your complexity. SSIS is very flexible. good luck on your project. – Jeric Cruz Sep 27 '17 at 23:19