Open Forum

Expand all | Collapse all

Integration Manager - Stored Proc Source

  • 1.  Integration Manager - Stored Proc Source

    Posted Jan 08, 2019 07:55 PM
    Hello Everyone,
    I'm curious whether or not anyone has an elegant solution for using a stored procedure that produces a simple recordset as the source query for an integration in Integration Manager? I tried to simply enter the EXECUTE statement as the source query and refresh the columns but that does not work.

    I thought about adding code to the stored procedure to insert the data into a staging table each time the stored procedure is called. I could write a before query script to execute the stored procedure and then my script becomes a simple SELECT statement...if I chose to go this route.

    I'm hoping someone has an idea that I haven't already considered. Thanks in advance!

    ------------------------------
    Corey Clay
    Senior Technical Consultant
    NexTec Group
    ------------------------------


  • 2.  RE: Integration Manager - Stored Proc Source

    TOP CONTRIBUTOR
    Posted Jan 09, 2019 09:03 AM
    Hi Corey,

    I love when solutions are hard to find, but easy to solve :)   Just put stored proc name in the SQL Statement and that should work.

    I kept forgetting to set my default database so it wasn't finding my stored proc.  But, you can always enter the stored proc as DBName..StoredProcName

    Good Luck & Have Fun!

    ------------------------------
    John Arnold
    Senior Software Engineer
    US Digital
    Vancouver WA
    ------------------------------



  • 3.  RE: Integration Manager - Stored Proc Source

    Posted Jan 09, 2019 09:09 AM
    Thanks @John Arnold. Are you saying that i can enter the proc name without EXECUTE or EXEC? I had tried it with the syntax below and it didn't return the columns.

    execute TWO..myStoredProc​


    ------------------------------
    Corey Clay
    Senior Technical Consultant
    NexTec Group
    ------------------------------



  • 4.  RE: Integration Manager - Stored Proc Source

    TOP CONTRIBUTOR
    Posted Jan 09, 2019 09:11 AM
    Yep, just remove the EXEC(UTE) so only have   TWO..myStoredProc   as the SQL Statement

    ------------------------------
    John Arnold
    Senior Software Engineer
    US Digital
    Vancouver WA
    ------------------------------



  • 5.  RE: Integration Manager - Stored Proc Source

    Posted Jan 09, 2019 09:15 AM
    Thanks! I'll give it a try and report back.

    ------------------------------
    Corey Clay
    Senior Technical Consultant
    NexTec Group
    ------------------------------



  • 6.  RE: Integration Manager - Stored Proc Source

    SILVER CONTRIBUTOR
    Posted Jan 09, 2019 09:16 AM
    ​Not certain this will work for you, but could you use a refreshable excel query to get the data and use as your IM source file?  Attached is a link to a Victoria Yudin post on how to set up using a SQL stored procedure.

    https://victoriayudin.com/2014/11/24/create-a-refreshable-excel-report-based-on-a-sql-server-stored-procedure/​​​

    ------------------------------
    Selena Breedlove
    SVP of Finance/Controller
    US Assure Inc
    Jacksonville FL
    ------------------------------



  • 7.  RE: Integration Manager - Stored Proc Source

    TOP CONTRIBUTOR
    Posted Jan 09, 2019 12:37 PM
    I haven't found IM too amenable to direct use of a stored procedure because the output column list isn't readily available in the object definition.  I've always used either a staging Table (which could be populated from a stored procedure in a BEFORE script) or View.  Then I typically set up an AFTER script which then clears out the processed data.  One of my favorite techniques is to use a flag field in my staging table set to 0 (for unprocessed) and a view of the staging table which filters out anything with a flag > 0.

    ------------------------------
    Blair Christensen
    Database Administrator
    Oppenheimer Companies, Inc.
    Boise ID
    ------------------------------