Dynamic Selects on SSIS Packages


  • All, 

    We're trying to create a data dump solution leveraging SSIS.

    Here at our production environment, we consider any report in SSRS that runs for over 60 seconds or delivers over 200K rows a data dump and therefore it shouldn't be delivered through SSRS, otherwise it could potentially bring our production systems down.

    The original idea was to create an SSIS package that would have that select from the report passed dynamically to the package and have that data delivered to a file on a network share.

    Now, I don't think we achieve that since we can't have dynamic selects on SSIS packages (am I right?) because the mappings don't get built dynamically.

    Is there a way to dynamically build the package then?

    Have you guys worked on any solutions like that?

    Do you guys have any suggestions for me?

    Thanks in advance,

    -Igor Santos
    Friday, January 29, 2010 7:40 PM


All replies

  • Packages can be built on the fly using code, but that's a big undertaking, and probably not the soution you are looking for in terms of effort / cost.

    Assuming your reports are fairly fixed in terms of columns, and only vary in terms of parameters, you may be able to do this on a 1:1 report:package basis. The SQL can be dynamic, the columns cannot. I.E SSIS does not care whether your select is

    FROM Table
    WHERE A =1 AND B > 5


    FROM Table
    WHERE C ='Monkey' AND B < 5

    As long as columns A,B,C are always selected in that order. How you pass the SQL / parameters from the report to the package is a one I can't answer.

    However what I would seriously be considering is why you even have a report that delivers 200,000 rows. That's a data set for consumption by a computer - no human will ever make sense of a report much beyond 10 rows (yes, I did say 10).

    Cheers, James

    James Beresford @ SSIS / MSBI Consultant in Sydney, Australia
    Saturday, January 30, 2010 1:22 AM
  • See if this example helps you: | Don’t forget to mark the post(s) that answered your question
    • Marked as answer by sqlsantos Monday, February 1, 2010 10:06 PM
    Sunday, January 31, 2010 2:48 AM
  • See if this example helps you: | Don’t forget to mark the post(s) that answered your question


    This statement "The metadata of a SSIS dataflow cannot be changed at run time (e.g. changing number or name of columns in source components)"

    is not true. We have demonstrated with CozyRoc Data Flow Task Plus , it is possible to change data flow at runtime and have dynamic number of columns.

    SSIS Tasks Components Scripts Services |
    Sunday, January 31, 2010 10:14 PM
  • Hmmm...I wasn't  referring to the 3rd party extension you guys sell; I was really talking about the SSIS built-in data flow task. If you think that still is not true, please feel to post examples here so we all can benefit from your knowledge and perhaps help the original poster with his problem.

    Now that you mention it, perhaps, I should have qualified my statement better. Thanks for bringing that to my attention. | Don’t forget to mark the post(s) that answered your question
    Monday, February 1, 2010 3:15 AM
  • Rafael, 
    Thanks for the link, your blog is always helping me with some tasks, I really appreciate the effort.

    I have downloaded the dtsx file and I'm going to play to with tonight.

    I'll let you know if this solution worked. I'll mark it as the answer for now.

    -Igor Santos
    Monday, February 1, 2010 10:06 PM
  • Rafael, 

    I got this to work. I only had to add quotes around the @TableNameOrQuery variable, so I pass my whole SELECT inside of double quotes when the ExportType = 'Query'.

    Other than that, everything works fine.

    I'll be touching bases with the rest of my team tomorrow to see what they think.

    Again, thanks for the response.

    -Igor Santos
    Tuesday, February 2, 2010 12:23 AM
  • I am glad you found it helpful, please post back if run into problems or have suggestions.. | Don’t forget to mark the post(s) that answered your question
    Tuesday, February 2, 2010 5:39 AM
  • Rafael, 
    What would you suggest if I want to bcp data from a stored procedure out?
    The solution you presented is good for extracting data from tables but not optimal for stored procedures, am I right?

    Any ideas or suggestions?
    Wednesday, March 3, 2010 6:25 PM