none
mapping low and high predicate values for 11 queries RRS feed

  • Question

  • Hi we run 2017 std.  I have 11 queries that need to run on 11 non linked servers.  Each query has a high and low date used in a date range predicate.  I'd like to run the queries concurrently (not in a loop) at least to the extent that ssis will allow.

    Each query is associated with a 3 character organization code.  I can easily produce a resultset that returns 11 rows with org code , low date and high date.

    I'm trying to come up with a way to elegantly map this resultset's data to perhaps 11 variable strings that are queries , maybe 11 sets of low and high dates that could easily (thru expressions) be used to build the 11 queries in variables or perhaps something else.

    we use sql agent with the catalog to run our packages.  i thought about building a config file dynamically from the resultset and then mapping its content to the vars but i'm not sure a config file can play with a catalog based pkg.

    does the community have any ideas for elegantly building these 11 queries that will likely be run from 11 different oledb sources each with a different connection?  I even thought about returning a resultset where each column is a query and the columns map to 11 different variable strings. I think a multi column resultset can map in ssis to multiple vars.  I  also thought about pivoting my vertical resultset to return 22 vars that would be mapped to the sets of 11 low and high dates.

    Friday, September 13, 2019 12:22 AM

Answers

  • as painful as it was here is what I did. I've always felt that the more variables (or params) I have in a pkg, the dumber the pkg must be, especially after a certain threshold.  Boy did my number of vars bloat.

    I leveraged my query that returns org abbrev x varchar low date x varchar high date, more or less pivoted it and mapped the 22 columns from the one row returned to 22 new variables.  Each var date range pair's names look like XXXlowDate and XXXhighDate where XXX is the abbreviation of an org.

    I added a  standard extract command  variable (string) whose expression looks something like this

    "select *,'%' OrgCode,cast(aaaa as float) aaaa1,cast(bbbb as float) bbbb1  from [dbo].[zzzzz] where changedate > '?' and changedate <= '#'"

    I added 11 string variables each meant to be a different query whose expressions look something like this ...

    replace(replace(replace (@[User::standardExtractCommand],"?", @[User::XXXlowDate]),"#", @[User::XXXhighDate]),"%","some org abbrev")

    I feel lucky that the union all i'm piping all of these thru allows data to come in with different ordinal positions, varying number of columns, and surrogates for the same column having a different data type (eg real where usually float) .  That allowed me to code each query with a select *.  And make an otherwise messy architecture a bit more sane and maintainable.   

    • Marked as answer by db042190 Monday, September 16, 2019 5:44 PM
    Friday, September 13, 2019 6:18 PM
  • The simplest way to accomplish what you are describing is to simply write the results to a "control" table in the database. Then have 11 tasks which read the data it needs from that table.


    • Marked as answer by db042190 Monday, September 16, 2019 5:44 PM
    Monday, September 16, 2019 12:06 PM
  • Hi db042190,

    Again, the SSIS config files are things from the distant past: SSIS 2005 - 2008R2.

    You need to use SSIS Catalog Environments instead.

    You can find me on LinkedIn and Skype.
    • Edited by Yitzhak Khabinsky Monday, September 16, 2019 5:37 PM
    • Marked as answer by db042190 Monday, September 16, 2019 5:44 PM
    Monday, September 16, 2019 5:36 PM

All replies

  • Hi db042190,

    It is possible to make an asynchronous call to [catalog].[start_execution]. It will allow you to launch SSIS packages in parallel.

    Here is a good link on the subject: A Better Way to Execute SSIS Packages with T-SQL


    Friday, September 13, 2019 2:29 AM
  • Hi db042190,

    Check if these tutorials are helpful.

    Please have a try.

    Regards,

    Zoe


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Friday, September 13, 2019 3:10 AM
  • thx, parallel execution isn't really what challenges me here.

    Its the mapping of 22 resultset values from 11 rows (2 values a row) to 22 variables in my pkg.  The variables will be used as low and high date ranges in 11 queries.  I am wondering of there is an elegant way of doing this with minimal ssis components, perhaps leveraging some newer features in ssis or an older feature that i'm not thinking of.  Should I be thinking about a pivoted single row with 22 cols that can map to 22 vars?  Is there perhaps a way in the most recent release of temporarily using a table to map to variables (or params) similar to the way we used to  use sql tables to map most of our vars, conns etc?

    I don't think parallel execution would help me here because control still needs to return to a specific component after all 11 queries are complete and I don't want to go down that road. 

    Friday, September 13, 2019 11:18 AM
  • as painful as it was here is what I did. I've always felt that the more variables (or params) I have in a pkg, the dumber the pkg must be, especially after a certain threshold.  Boy did my number of vars bloat.

    I leveraged my query that returns org abbrev x varchar low date x varchar high date, more or less pivoted it and mapped the 22 columns from the one row returned to 22 new variables.  Each var date range pair's names look like XXXlowDate and XXXhighDate where XXX is the abbreviation of an org.

    I added a  standard extract command  variable (string) whose expression looks something like this

    "select *,'%' OrgCode,cast(aaaa as float) aaaa1,cast(bbbb as float) bbbb1  from [dbo].[zzzzz] where changedate > '?' and changedate <= '#'"

    I added 11 string variables each meant to be a different query whose expressions look something like this ...

    replace(replace(replace (@[User::standardExtractCommand],"?", @[User::XXXlowDate]),"#", @[User::XXXhighDate]),"%","some org abbrev")

    I feel lucky that the union all i'm piping all of these thru allows data to come in with different ordinal positions, varying number of columns, and surrogates for the same column having a different data type (eg real where usually float) .  That allowed me to code each query with a select *.  And make an otherwise messy architecture a bit more sane and maintainable.   

    • Marked as answer by db042190 Monday, September 16, 2019 5:44 PM
    Friday, September 13, 2019 6:18 PM
  • Hi db042190,

    According to your last post, have you resolved your issue? 

    If you have further question, I am happy to discuss with you.

    Regards,

    Zoe


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, September 16, 2019 8:05 AM
  • sure zoe, can a config file be used with a pkg that is executing from the catalog?
    Monday, September 16, 2019 11:04 AM
  • The simplest way to accomplish what you are describing is to simply write the results to a "control" table in the database. Then have 11 tasks which read the data it needs from that table.


    • Marked as answer by db042190 Monday, September 16, 2019 5:44 PM
    Monday, September 16, 2019 12:06 PM
  • Thx Tom.

    Zoe, one contributor at this link suggests config files can be used with catalog based pkgs, but at least one poster in the same link is suggesting no.  I'll post back here once I figure out the right answer. 

    https://social.msdn.microsoft.com/Forums/en-US/a3975483-bb96-4696-b581-e33f0cb901b9/deploy-a-package-in-ssis-db-catalog-and-pass-config-file-dynamically-in-a-sql-job 

    Monday, September 16, 2019 12:30 PM
  • the fact that this link makes you switch to pkg deploy model first makes me think config files aren't compatible with catalog based pkgs.  and the fact that no clear way of specifying a config file from packages under ssisdb or sql agent convinces me further.

    https://docs.microsoft.com/en-us/sql/integration-services/lesson-5-2-enabling-and-configuring-package-configurations?view=sql-server-2017

    Monday, September 16, 2019 5:31 PM
  • Hi db042190,

    Again, the SSIS config files are things from the distant past: SSIS 2005 - 2008R2.

    You need to use SSIS Catalog Environments instead.

    You can find me on LinkedIn and Skype.
    • Edited by Yitzhak Khabinsky Monday, September 16, 2019 5:37 PM
    • Marked as answer by db042190 Monday, September 16, 2019 5:44 PM
    Monday, September 16, 2019 5:36 PM
  • I like the idea of dynamically setting catalog environ vars, which from what i understand easily map to pkg params.  Arthur shares a link here that shows how to set the env vars programatically  https://social.msdn.microsoft.com/Forums/en-US/71e96b38-ceec-4a37-a25f-c97f001a5be6/dynamically-setting-environment-values-in-the-ssis-catalog?forum=sqlintegrationservices  . 
    Tuesday, September 17, 2019 12:56 PM