SSIS guidance

  • hi all

    Looking for a general directional pointer on something I am trying to achieve in SSIS

    I want to pull a number of column values from a table in a DB , store these in a variable and then use that variable in an outbound API call which will use those values as parameters in the request to determine what data I am asking for.

    I can do this where the value is singular but when it is multiples I need a push in the right direction for how I construct the logic to take those values , prefix with the parameter name , suffix with "&" , do the same with subsequent values until I run out of values in order to populate my API URL.

    Needs to look a bit like <variable name>=<1st value from query>&<variable name>=<2nd value from query>&

    Any suggestions gratefully received

     

    Purds

  • If you NEED to do this in SSIS, I think you are going to need a for each loop (or similar).

    If you can do this on the TSQL side before or as you pull it into SSIS, that may be easier, but this assumes that the variable names are either coming from SQL OR can be hard-coded into a temp table.  Here is a quick and dirty example:

    WITH cte AS (SELECT 'hello' AS data, 'world' AS data2 UNION SELECT 'world', 'hello')
    SELECT DISTINCT REPLACE(SUBSTRING((SELECT ',' + data + '=' + data2 AS [text()] FROM cte FOR XML PATH('')),2,1000),',','&') FROM cte

    Here I am using "data" as the variable name and data2 as the value and I get "hello=world&world=hello" as my result.

    The above is all just my opinion on what you should do. 
    As with all advice you find on a random internet forum - you shouldn't blindly follow it.  Always test on a test server to see if there is negative side effects before making changes to live!
    I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

  • If you are looking to pull a number of columns in a single row - you can use the 'Execute SQL Task' - set the ResultSet to 'Single Row' and in the Parameter Mapping page map each column to a pre-defined SSIS variable.

    If you use a stored procedure - and return each value as a separate output parameter, set the ResultSet to None - map the output parameters to the variables.

    If you are retrieving a set of data that you want to map - it gets a bit tricky.  For this, you can use a data flow - with a source as your query and a Recordset Destination.  Setup a package variable with the type of Object and use that for the destination.  That can then be used in a foreach loop container to loop over the recordset - mapping each column to package variables.

    A final package variable can then be used to build the string you need.

     

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • This was removed by the editor as SPAM

  • This was removed by the editor as SPAM

  • Hi Jeffrey , I'm attempting your suggested solution and was hoping you might be able to expand on " For this, you can use a data flow - with a source as your query" . I'm struggling to find the mechanism to define the source as the query from the control flow - any chance you wouldn't mind expanding a little ?

    ( as i'm sure you can infer I have no clue what I am doing)

  • drag a data flow onto the control flow panel - double-click the data flow which will transition you to the data flow tab. Once there - from the SSIS toolbox grab a source (use the Source Assistant), then grab a destination (use the recordset destination) and connect from the source to the destination (green line).

    Remember - this is to return multiple rows of 'parameters' that can be fed into a for each loop.  So - once you have the data flow, connect it to a for each loop and configure that component.

    In the for each loop - you map the fields from the recordset to individual SSIS variables.  Inside the for each, you use those variables to build the command...which is going to depend on how you are calling the API.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply