08 February 2017

SSIS Script Task : ConnectionString for ADO.Net & OleDb ConnectionManager

In the switching world developer often needs to toggle between connection managers and wish the code operates as its expected.However, the execution engine is yet to be smart enough to undertake the difference.
Connection Manager TypeConnection Manager Name
ADOADO Connection Manager
MSOLAP90Analysis Services Connection Manager
EXCELExcel Connection Manager
FTPFTP Connection Manager
HTTPHTTP Connection Manager
ODBCODBC Connection Manager
OLEDBOLE DB Connection Manager

For those pin point here a code that worked for me and happy if works for you as well.
FOR ADO.NET Connection Manager

SqlConnection sConnection = new SqlConnection();
sConnection = (SqlConnection)(Dts.Connections["MyConnectionManagerADO.NETName"].AcquireConnection(Dts.Transaction) as SqlConnection);
var connection = sConnection.ConnectionString.ToString()

FOR OLEDB Connection Manager

var oConnection = Dts.Connections["MyConnectionManagerOLEDBName"].ConnectionString.ToString().Trim();
oConnection = oConnection.Replace("Provider=SQLNCLI10.1;", "").Replace("Provider=SQLNCLI11;", "").Replace("Provider=SQLNCLI11.1;", "").Replace("Auto Translate=False;", "");

No comments:

Post a Comment

Thanks for your valuable comments

Rate Now: