Transfer value between two steps in SQL Server Agent job

Question

I hope that this is the right forum for this question. I was looking for a forum with "DBA" as I believe that this question boarders between DBAs and Developers remits.

Is there any way that I can transfer a value between two steps in a SQL Server Agent job. Here is my scenario.

1. In step one I want to have a query which establishes the rowcount in a table from a server called “MySourceServer” (for example). In an imaginary setting I would put that rowcount value in variable @LatestLoadRowCount.
2. In step two I have another table in a different server called “MyDestinationServer”. What I want is to get the average count of the table in the “MyDestinationServer” server and put that value in a variable called @AverageInsertsPast7Days. The crux of the whole matter then is that if the value in @LatestLoadRowCount variable is less than a certain specified threshold the job should fail. For example – as depicted by this formula

IF (SELECT (@LatestLoadRowCount - @AverageInsertsPast7Days)/@AverageInsertsPast7Days * 100) < 10

       BEGIN

              RAISERROR ( 'Loaded data row count not valid!', 16, 1 )

       END

This will not be a problem if two tables were in the same server even though different databases. My biggest predicament here is that these tables are in separate servers. How do I handle that in SQL Server Agent? I hope my question is clear.

Please note that I’m aware that I can easily accomplish this in Integration Services but my manager is not keen that we do this through SSIS.

---

Mpumelelo

Answer 1

Hi,

If you don't want to use SSIS, then you can use linked servers and run queries against both your local and remote servers.

SELECT COUNT(*) FROM [ServerRemote\InstanceRemote].DatabaseRemote.TableRemote for your remote table

SELECT COUNT(*) FROM Database.Table for your local server.

In both cases, you'll have to define how to connect to your linked server (via SQL logins or via Windows authentication).

I don't see why SSIS would be a problem (unless you or you manager don't have any experience with that tool)

---

Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

Answer 2

Thank you Sebastian. Both my manager and myself have expertise in SSIS. Actually, we are data warehouse developers. The idea of putting this task in SSIS has implications associated with tortuous release policies. So getting this in the Agent job will circumvent that hustle and get this task done as quickly as possible.

Is there any way that can be done without touching the linked server aspect?

---

Mpumelelo

Answer 3

You can write a PowerShell or VBS script to connect to both servers and do the necessary calculations, but wouldn't that be even more tortuous than

just writing an SSIS package or a Linked Server query?

---

Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

Answer 4

We have decided to use a different logistical approach to this. SSIS and linked server options are valid but have not been opted for this time. The original plan was to do validation of rowcount as a pre-ETL process but we have opted to do it as a post-ETL process instead. This means that we will get the RAISERROR alert anyway but as a post-ETL step, and I will be querying from only one server - the destination server.

Thanks.

---

Mpumelelo