Hi,
In my scenario, I rely on a stored-procedure in the source (external) database to create the table which will be used in TX. The stored-procedure might have various table/view/functions dependencies.
Is it possible to import stored procedure/scalar functions into ODX layer?
I know it's possible to recreate (internal) stored-procedure, however I have no access to the underline script in the stored-procedure
Kind regards.
Dror
Best answer by wynkoop
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 TimeXtender 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 TimeXtender. You get none of the benefits of automation our tool offers, nor do you get the advantages of features like data lineage and impact.
If you have more information you can provide in terms of the requirement behind the use of this script, we may be able to offer a better solution than simply re-using a script from the source system. It may be possible to meet the requirement with native TimeXtender functionality, which is generally faster, more scalable, and easier to maintain.
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 TimeXtender 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 TimeXtender. You get none of the benefits of automation our tool offers, nor do you get the advantages of features like data lineage and impact.
If you have more information you can provide in terms of the requirement behind the use of this script, we may be able to offer a better solution than simply re-using a script from the source system. It may be possible to meet the requirement with native TimeXtender functionality, which is generally faster, more scalable, and easier to maintain.
Thank you for your quick response.
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 TimeXtender 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.
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
