May 28, 2021 at 12:03 pm
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,
May 28, 2021 at 12:13 pm
How are you displaying the popup? You need to display it from Excel, not from within the package.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
May 28, 2021 at 12:26 pm
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,
May 28, 2021 at 1:26 pm
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.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
May 28, 2021 at 6:19 pm
Phil,
You provided a great idea that seems the best option for my next step. Thanks!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply