Data Output Tool Order of Operations

I have a macro that I am using to load a table in a database. I want to Truncate a staging table using Pre Create Sql Statement, then Append the data from the data input stream into the staging table. When that is complete execute a stored procedure in the Post Create Sql Statement that will actually load my database table (it does a bunch of other work as well).

In my Preprod gallery environment this works fine, but when I moved it to production gallery, it appears that the Post Create SQL Statement is executing prior to the "Append Existing" into the staging table is complete.

The big difference I see in Preprod and Prod is the volume of data that is being processed. In Preprod it is 1M - 2M rows, but 40+M in Prod.

I have also verified that the user has permissions to execute the procedure.

Should the Post Create SQL Statement wait for the Append Existing data to complete before executing?

Is there a way to do further work after the Data Output tool?

Output

Batch Macro

Accepted answers

oyes2704

Hi @tfranz

agree with @jrgo on ignoring the message sequence. You can look through several approaches on waiting for operations to complete listed on this ticket: Solved: Pause/Wait/Hold Tool - Alteryx Community

Also another great collection here - see the Macro approach, great solution!: Solved: Use Containers in a sequential way - Alteryx Community

Best,

Oly

All comments

jrgo

From my experience when using the Post SQL, it does wait for the append operation to complete before the post executes. I wouldn't put too much weight on what you see in the message log since the message saying how many records it wrote and it starting to execute the post script probably happen fractions of a second apart.

You don't mention in your post, but are you getting any errors or is the target table not reflecting what you'd expect to see?

oyes2704

Hi @tfranz

Also another great collection here - see the Macro approach, great solution!: Solved: Use Containers in a sequential way - Alteryx Community

Best,

Oly

tfranz

There are no errors, but the stored procedure is not seeming to be running. I can run it manually after the workflow finishes and it does execute and process as expected.

jrgo

Only thing that I can think of is that your default namespace (database / schema) may be getting set to a different environment or that the connection string within your macro is targeting your PROD SQL server.

In other words... make sure it's not executing the SP on your preprod server.

If this isn't the case, you may have to work with your DB admin to see if they're able to locate any logs to see what's happening with the execute request.

Quick Links

This months top contributors

AkimasaKajitani 387

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335