SSIS using variables and params in queries

Params can be set at design time and updated once the package is deployed - can put params or parts of queries into these
Variables are internal to the package & can be set at design time but not updated once deployed; sql can be exectued and the results put into a variable - can put variables or parts of variables into querries


Putting a query into a param so it can be updated after package is deployed

1. Add the param on the Parameters tab
2. When querying the data tell it to use the command in the variable. The '$Package' syntax with the $ appears to be for params e.g. $Package::PrimaryNurseQuery. Variables are like User::MyVariableName.
   
    
3. Once the package is deployed access the parameters in SSIS by right clicking the package
   
   
4. Update the parameter to whatever is needed

Selecting data into a variable, then using that variable as part of a subsequent query

The variable can be used by multiple containers

1. Create a variable in SSIS -> Variables that will store the result of a query. Can put a dummy value in it to start with
   
2. Create another variable that holds the template for your second query, called something like the poorly named 'SQLCommandMHSmart'. It holds something like SELECT field1, field2 FROM table1 WHERE table1.admissionNumber in (encounterList) AND table1.nhi in (nhiList). This is bad SQL, the 'encounterList' and 'nhiList' will be replaced with values later e.g. encounterList would be replaced with the string '1234567','222222','323456'.
3. Create an Execute SQL task 
   
4. Put the query into the execute sql task, this one will return data in format 'value1','value2','value3'... so that it can easily be used in an 'in' clause. 
5. This is what this query looks like
   
6. Set the Execute SQL task to store data into the variable
7. Create a script task that is going to use the above variable and build up a new query and will store the constructed query in another variable (in this case SQLCommandMHSmart). This is kind of dumb, it's really just doing string manipulation and script tasks are annoying to use. 
8. First, set the script task so it has access to the right variables
   
9. Click the Edit Script button and wait for eternity for the new instance of visual studio to open. It takes a while. Feel yourself aging. Look, here comes retirement. The script task contents will look something like this:
   
10. it's very exciting string manipulation, putting the contents of one variable into the template held by a second variable, then saving that back into the second variable.
11. The query is now ready to use. When getting data from the next source set the query as coming from the variable