Hi Dror,

We do not have that feature explicitly, but as a workaround, you can try creating a data warehouse database, pointing it at your source database, then reverse the stored procedures.  Given that Discovery Hub assumes you will be db owner of a data warehouse database, this might not work.  If it doesn't, there's no other option but to request a copy of the script from someone who has access to it. 

One of the reasons we don't have this function is that we strongly recommend against creating tables using custom scripts.  Custom objects like that exist outside of the project metadata, and are effectively invisible to Discovery Hub.  You get none of the benefits of automation our tool offers, nor do you get the advantages of features like data lineage and impact.  

I recommend reaching out to your contact at Discovery Hub, or creating a thread in the general forum about the requirement behind the use of this script.  It may be possible to meet the requirement with native Discovery Hub functionality, which is generally faster, more scalable, and easier to maintain.  

Best regards,

Doug Wynkoop

Oh, silly me. This is in the General section already!  If you have more information you can provide, we may be able to offer a better solution than simply re-using a script from the source system.

Best regards,

Doug Wynkoop

Doug, 

Thank you for your quick respond. 

Unfortunately the stored-procedures have quite "deep" dependencies and not only from table/view types. Therefore I'm not sure you can reverse engineer them. 

The content of the stored-procedure contain i,e the IP of the software which I'm afraid we will access to. 

At this point, the only solution i can come up with is to establish another tier (database) between the source database and TX. I will call  the  stored-procedure remotely and output will be stored in designated tables.Those tables will be read into TX. 

It's not the perfect solution and it pose a lot of technical challenges (to name a few: grant remote exec permission to stored-procedure; output table needs to be re-created each time where the table structure can change depending on the sp output; no data lineage; no single thread - TX cannot trigger the whole process just wait until the data flows in), but this is it..

Kind regards, 

Dror Svartzman

Hi Dror

You can create custom tables in Discovery Hub and execute the remote stored procedures from there. Either directly or via a SSIS package.

Basically same solution as the one you propose except you save the middle step. I bet a LinkedServer to source could enable this setup quite efficiently.