IS–Parameters and Return Codes in Execute SQL Task

Connection Type	Parameter marker	Parameter name	Example SQL command
ADO	?	Param1, Param2, …	SELECT FirstName, LastName, Title FROM Person.Contact WHERE ContactID = ?
ADO.NET	@<parameter name>	@<parameter name>	SELECT FirstName, LastName, Title FROM Person.Contact WHERE ContactID = @parmContactID
ODBC	?	1, 2, 3, …	SELECT FirstName, LastName, Title FROM Person.Contact WHERE ContactID = ?
EXCEL and OLE DB	?	0, 1, 2, 3, …	SELECT FirstName, LastName, Title FROM Person.Contact WHERE ContactID = ?

Excel - Tips to Use.

Some of the tricks, which are usefull in tips, i will discuss in this post.

1) The following is one function usefull when we want to concate the value with any leading values.
=Right(Concatenate("00",A1),8)
=Right(Concatenate("xx",A1),8)

 

2) Replace is the one good function we can use as follows:
=REPLACE(A1,1,2,"A")     i.e [Replace(OldValue,StartNumber,NumberOfCharecters,NewValue)]

 

 

 

 

 

3) Convert date to text format as follows:

=TEXT(A1,”YYYY-MM-DD”)

=TEXT(A2,”YYYY/MM/DD”)