Dynamic SQL Command Variable Data Access with ADO.NET Source

There is a way to make the SQL command dynamic. In the Control Flow Tab, select the Data Flow Task that contains the ADO.NET Source and press F4. This will bring up the properties window for the DFT.

Click the expression button, opening the Property Expressions Editor, and select SQL command for the property.

Click the expression button to open the Expression Builder.

Proceed to build the expression using the tools and/or by typing the code. In this example, the variable is user defined, but you could also use a system variable. The tricky part is making sure you have double quotes all in the right places and have the correct variable type because the expression must evaluate to a string (another blog, another day).

Be sure to Evaluate Expression to see if it indeed looks like an executable SQL command.

Note: If you define a variable you might need to place a proper default Value in the variable so the SQL command will allow validation of the metadata

in order to use OUTPUT parameters to return values, you must follow these steps while configuring the Execute SQL Task:

For OLEDB Connection Types:

1. You must select the OLEDB connection type.
2. The IsQueryStoredProcedure option will be greyed out.
3. Use the syntax EXEC ? = dbo.StoredProcedureName ? OUTPUT, ? OUTPUT, ? OUTPUT, ? OUTPUT The first ? will give the return code. You can use the syntax EXEC dbo.StoredProcedureName ? OUTPUT, ? OUTPUT, ? OUTPUT, ? OUTPUT to not capture the return code.
4. Ensure a compatible data type is selected for each Parameter in the Parameters page.
5. Set your parameters Direction to Output.
6. Set the Parameter Name to the parameter marker's ordinal position. That is the first ? maps to Parameter Name 0. The second ? maps to Parameter Name 1, etc.

For ADO.Net Connection Types:

1. You must select the ADO.Net connection type.
2. You must set IsQueryStoredProcedure to True.
3. Put only the stored procedure's name in SQLStatement.
4. Ensure the data type for each parameter in Parameter Mappings matches the data type you declared the variable as in your SSIS package.
5. Set your parameters Direction to Output.
6. Set the Parameter Name to the same name as the parameter is declared in stored procedure.

For other connection types, check out the table on this page



Note: if you choose the ADO/ADO.Net connection type, parameters will not have datatypes like LONG, ULONG, etc. The datatypes will change to Int32, etc. Make sure that the datatype is EXACTLY the same type as the Variable in your package is defined. If you choose a different datatype (bigger/smaller/different type) you will get the error:

Error: 0xC001F009 at Customers: The type of the value being assigned to variable "User::Result_CustomerID" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
Error: 0xC002F210 at Add New Customer, Execute SQL Task: Executing the query "dbo.AddCustomer" failed with the following error: "The type of the value being assigned to variable "User::Result_CustomerID" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

To fix this error make sure the datatype you select for each parameter in the Parameters page exactly matches the datatype for the variable.
For example i was fetching bigint from the query and setting it to variable with int 64 but type didint match so i had to use cast (mybigintcolumn as signed integer) as mycolumn to match the type .
If you have attempted to use a connection type other than ADO.Net with named parameters you will recieve this error:

Error: 0xC002F210 at Add New customer, Execute SQL Task: Executing the query "exec dbo.AddCustomer" failed with the following error: "Value does not fall within the expected range.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Named parameters can only be used with the ADO.net connection type. Use ordinal position numbering in order to use OUTPUT parameters with the OLEDB connection type. Eg: 0, 1, 2, 3, etc.

OUTPUT parameters are extremely useful for returning small fragments of data from SQL Server, instead of having a recordset returned. You might use OUTPUT parameters when you want to load a value into a SSIS Package variable so that the value can be reused in many places. The data that is output might be used for configuring / controlling other Control Flow items, instead of being part of a data flow task.