Hi All, I currently have a SSIS package with a data flow task which exports four SQL Server tables to four csv files as can be seen in the image below. The data exported is currently a count of patients by provider for all england providers. However, we have three different requirements: - 1) To export all providers - the SISS package currently fulfills this requirement 2) To export a single provider - I presume the variable/parameter facility will fulfill this requirement 3) To export a selection of providers - no idea how to achieve this. I would like to automate all these requirements where possible instead of changing the 'where clause' in the SQL Command in the OLE DB source editor everytime I want to run the package. I was hoping there was a similar facility to that of SSRS where you can have multivalued parameters with a drop down check box to select the providers you would like to export. The reason I am not using SSRS is because I need to export 4 csv files as opposed to 1. Can anyone help me with this?

You can create two variables. One is called Providers and one called SQL. The SQL variable uses Expressions. A picture of this is below. The expression is like this: LEN( @[User::Providers] ) > 0 ? "SELECT * from tblProviders where Providers in ( " + @[User::Providers] + ")" : "SELECT * from tblProviders" Then you use SQL Command from variable from the OleDb source. One caveat. I would watch out for SQL injection using this method. Russel Loski, MCT, MCITP Business Intelligence Developer and Database Developer 2008

You can create two variables. One is called Providers and one called SQL. The SQL variable uses Expressions. A picture of this is below. The expression is like this: LEN( @[User::Providers] ) > 0 ? "SELECT * from tblProviders where Providers in ( " + @[User::Providers] + ")" : "SELECT * from tblProviders" Then you use SQL Command from variable from the OleDb source. One caveat. I would watch out for SQL injection using this method. Russel Loski, MCT, MCITP Business Intelligence Developer and Database Developer 2008

Thanks for your response Russel. Sorry I failed to mention each OLE DB source eg Record, Episode, Event etc uses a different query to pull patient counts from a different SQL Server table each time. Does this mean I will need a different variable for each OLE DB source? Also, is there any way of implementing a drop down/check box facility for ease of use similar to that of SSRS? Thanks again. Regards, Mike

Yes, in this kind of solution you must have a variable for each query. SSIS doesn't have any GUI where you can input data, so you can't specify that drop down in SSIS. One other option is to have the providers in a text file (in a know location) and load it to some temporary table that will be used in your queries. This way you don't need to open BIDS to change the providers list. David.