I'm working on a data migration package and find that when I use an OLEDB connection manager, I'm able to call a stored proc using SQLSourceType of Direct Input, and easily set it up to use variables to replace the parameter placeholders and parse the query or execute the task successfully.
However - since the application I'm building will ultimately run on Azure, it is not very good practice to use OLEDB so I'd like to use ADO.NET instead. The database I'm currently using is on my localhost, but I have the same issue when I use a connection manager that connects to our current dev database which is hosted on Azure.
When using ADO.NET, the only way I can get it to work is to use a variable as SQLSourceType and then physically build up the string like this:
" [reference].[GetTemplate_ColumnHeaders] @TemplateName = '" + @[User::TemplateName] + "'"
When using OLEDB, I can tell SQLSourceType to use direct input and simply specify the name of the stored proc as follows in the field:
[reference].[GetTemplate_ColumnHeaders] @TemplateName = ?
According to what I can find in the documentation, I should be able to use the second example with ADO.NET as well although they say it may differ depending on the source database I'm connecting to, but it's a MS SQL 2019 database, so I wouldn't expect there to be huge exceptions since it is all Microsoft technology.
I'm carrying on and I'll use the expression to create the SQL Source as in the first example, but does anybody know why it won't accept the second format for ADO.NET? My resultset is set up correctly and so are my parameters because everything works 100% with OLEDB, I left it exactly the same when I switched to ADO.NET although technically I can now remove the parameters since I'm building up the SQL Source using an expression.
