Invoking .dtsx file via Excel - different behavior

Question

Post reply

Invoking .dtsx file via Excel - different behavior

redoak

SSC Enthusiast

Points: 117
More actions
May 28, 2021 at 12:03 pm

#3889174

Hello All,
I am fairly new to SSDT/SSIS. The below is executed on a windows 10 machine. The SSDT version is 15.9.34
I created a workflow where a flat file (csv) is imported to a SQL database table. Thereafter, a stored procedure kicks off and populates a second SQL database table. Finally, a message box pops up, stating the task is completed. This will be executed outside of SSDT, from Excel VBA using dtexec, as shown below.
Dim Command As String
Command = "dtexec /f ""C:\Users\.....\xxxxx.dtsx"""

The problem is, when I hit start within SSDT, the execution goes as planned but when I invoke it the package via Excel, there is no pop up box stating the task is done. This is an important step, as it will allow the user to know when the SQL database table is populated. Any ideas why the message box fails to pop up?
Thanks,

Viewing 5 posts - 1 through 4 (of 4 total)

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

Phil Parkin SSC Guru Points: 246987 More actions · Answer 1

How are you displaying the popup? You need to display it from Excel, not from within the package.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

redoak SSC Enthusiast Points: 117 More actions · Answer 2

Hi,

I am displaying the message via Script Task, in SSDT, using the standard pop up window code,

string message = "Task is completed.";

string caption = "ATTENTION";

DialogResult button = MessageBox.Show(message, caption, MessageBoxButtons.OK, MessageBoxIcon.Information);

Dts.TaskResult = (int)ScriptResults.Success;

This is occurs at the end of workflow, which is occurs successfully.

The million dollar question is, how would I get Excel to know when the .dtsx package has completed?

So far, I haven't been able to have Excel communicate with SSDT, other than kicking off a the .dtsx file.

Thanks,

Phil Parkin SSC Guru Points: 246987 More actions · Answer 3

As packages usually run within the context of an unattended process, message boxes within them should be avoided (unless developing or debugging).

I notice you mentioned users running this. For them to be able to run DTExec means that they will need some components installed – I'm not 100% sure here, but think that means SSDT with SSIS. Most companies would choose not to do that.

There is another way. As long as you have the package deployed to SSISDB, you may execute it using a SQL Server stored procedure (which can, of course, accept runtime parameters). When the stored proc kicks off the package, you can choose to execute synchronously (await completion) or asynchronously (fire and forget).

So your Excel VBA code could kick off a proc, await the result and display a completion (or error) message to your users.

The benefit of doing this is that users can execute packages without any need for additional tooling.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

redoak SSC Enthusiast Points: 117 More actions · Answer 4

Phil,

You provided a great idea that seems the best option for my next step. Thanks!