Home > SQL Server > Parameters in SSIS OLE DB Source Editor

Parameters in SSIS OLE DB Source Editor

2013/06/30

In a parameterized query for a .NET SqlCommand we use the @ symbol for parameters specification.

For example the C# code can be

conADO = new SqlConnection(connectionString);
conADO.Open();
cmdADO = new SqlCommand();
cmdADO.CommandType = CommandType.Text;
cmdADO.CommandText = "SELECT MyField FROM MyTable WHERE Plate = @Tg";
cmdADO.Connection = conADO;
cmdADO.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Tg", SqlDbType.VarChar, 50));
cmdADO.Parameters["@Tg"].Value = aStringParam;

When in a SSIS package has to be configured a OLE DB Source Editor (with a SQL Command as data access mode)


The parameters must be specified as in the old ADO of the COM age (Vb5/6 ancient times) , that is using the “?” character.

So we must write our example SQL as

SELECT MyField FROM MyTable WHERE Plate = ?

And by clicking the Parameters button the parser automatically recognize our parameters.

If we have three parameters expressed by three “?” we obtain


Advertisements
Categories: SQL Server
%d bloggers like this: