c# - Missing library to reference OLEDB connection types -
i got following piece of code ssis team blog cast oledb connection type can used acquireconnection () method. not sure why dts.connections part not working. dont know library have add make work. pretty added important ones including dts.runtimewrap. please let me know if need more information on question.
connectionmanager cm = dts.connections["oledb"]; microsoft.sqlserver.dts.runtime.wrapper.idtsconnectionmanagerdatabaseparameters100 cmparams = cm.innerobject microsoft.sqlserver.dts.runtime.wrapper.idtsconnectionmanagerdatabaseparameters100; oledbconnection conn = cmparams.getconnectionforschema() oledbconnection; edit below entire code component.
using system; using system.collections.generic; using system.data; using system.data.sqlclient; using microsoft.sqlserver.dts.runtime; using system.data.oledb; using system.data.common; using system.linq; using system.configuration; using system.collections; //using system.data.oledb; namespace aoc.sqlserver.dts.tasks { [dtstask( displayname = "custom logging task", description = "writes logging info table")] public class customloggingtask : task { private string _packagename; private string _taskname; private string _errorcode; private string _errordescription; private string _machinename; private double _packageduration; private string _connectionname; private string _eventtype; private string _executionid; private datetime _handlerdatetime; private string _uid; public string connectionname { set { _connectionname = value; } { return _connectionname; } } public string event { set { _eventtype = value; } { return _eventtype; } } public override dtsexecresult validate(connections connections, variabledispenser variabledispenser, idtscomponentevents componentevents, idtslogging log) { const string method_name = "customloggingtask-validate"; try { if (string.isnullorempty(_eventtype)) { componentevents.fireerror(0, method_name, "the event property must specified", "", -1); return dtsexecresult.failure; } if (string.isnullorempty(_connectionname)) { componentevents.fireerror(0, method_name, "no connection has been specified", "", -1); return dtsexecresult.failure; } //sqlconnection connection = connections[_connectionname].acquireconnection(null) sqlconnection; dbconnection connection = connections[_connectionname].acquireconnection(null) dbconnection; connectionmanager cm = dts.connections["oledb"]; microsoft.sqlserver.dts.runtime.wrapper.idtsconnectionmanagerdatabaseparameters100 cmparams = cm.innerobject microsoft.sqlserver.dts.runtime.wrapper.idtsconnectionmanagerdatabaseparameters100; oledbconnection conn = cmparams.getconnectionforschema() oledbconnection; if (connection == null) { componentevents.fireerror(0, method_name, "the connection not valid ado.net connection", "", -1); return dtsexecresult.failure; } if (!variabledispenser.contains("system::sourceid")) { componentevents.fireerror(0, method_name, "no system::sourceid variable available. task can used in event handler", "", -1); return dtsexecresult.failure; } return dtsexecresult.success; } catch (exception exc) { componentevents.fireerror(0, method_name, "validation failed: " + exc.tostring(), "", -1); return dtsexecresult.failure; } } public override dtsexecresult execute(connections connections, variabledispenser variabledispenser, idtscomponentevents componentevents, idtslogging log, object transaction) { try { string commandtext = @"insert ssislog (eventtype, packagename, taskname, eventcode, eventdescription, packageduration, host, executionid, eventhandlerdatetime,uid) values (@eventtype, @packagename, @taskname, @eventcode, @eventdescription, @packageduration, @host, @executionid, @handlerdatetime,@uid)"; readvariables(variabledispenser); dbconnection connection = connections[_connectionname].acquireconnection(transaction) dbconnection; //sqlconnection connection = (sqlconnection)connections[_connectionname].acquireconnection(transaction); dbcommand command = null; //using (sqlcommand command = new sqlcommand()) if (connection sqlconnection) command = new sqlcommand(); else if (connection oledbconnection) command = new oledbcommand(); { command.commandtext = commandtext; command.commandtype = commandtype.text; command.connection = connection; command.parameters.add(new sqlparameter("@eventtype", _eventtype)); command.parameters.add(new sqlparameter("@packagename", _packagename)); command.parameters.add(new sqlparameter("@taskname", _taskname)); command.parameters.add(new sqlparameter("@eventcode", _errorcode ?? string.empty)); command.parameters.add(new sqlparameter("@eventdescription", _errordescription ?? string.empty)); command.parameters.add(new sqlparameter("@packageduration", _packageduration)); command.parameters.add(new sqlparameter("@host", _machinename)); command.parameters.add(new sqlparameter("@executionid", _executionid)); command.parameters.add(new sqlparameter("@handlerdatetime", _handlerdatetime)); command.parameters.add(new sqlparameter("@uid", _uid)); command.executenonquery(); } connection.close(); return dtsexecresult.success; } catch (exception exc) { componentevents.fireerror(0, "customloggingtask-execute", "task errored: " + exc.tostring(), "", -1); return dtsexecresult.failure; } } private void readvariables(variabledispenser variabledispenser) { variabledispenser.lockforread("system::starttime"); variabledispenser.lockforread("system::packagename"); variabledispenser.lockforread("system::sourcename"); variabledispenser.lockforread("system::machinename"); variabledispenser.lockforread("system::executioninstanceguid"); variabledispenser.lockforread("system::eventhandlerstarttime"); variabledispenser.lockforread("user::uid"); bool includeserror = variabledispenser.contains("system::errorcode"); if (includeserror) { variabledispenser.lockforread("system::errorcode"); variabledispenser.lockforread("system::errordescription"); } variables vars = null; variabledispenser.getvariables(ref vars); datetime starttime = (datetime)vars["system::starttime"].value; _packageduration = datetime.now.subtract(starttime).totalseconds; _packagename = vars["system::packagename"].value.tostring(); _taskname = vars["system::sourcename"].value.tostring(); _machinename = vars["system::machinename"].value.tostring(); _executionid = vars["system::executioninstanceguid"].value.tostring(); _handlerdatetime = (datetime)vars["system::eventhandlerstarttime"].value; _uid = vars["user::uid"].value.tostring(); if (includeserror) { _errorcode = vars["system::errorcode"].value.tostring(); _errordescription = vars["system::errordescription"].value.tostring(); } // release variable locks. vars.unlock(); // reset dispenser variabledispenser.reset(); } } }
with code posted, figured out trying accomplish.
these things i've done work:
a) compile error you're receiving:
"error 1 type or namespace name 'connections' not exist in namespace 'aoc.sqlserver.dts' (are missing assembly reference?)" is because way you're trying oledb connection manager:
connectionmanager cm = dts.connections["oledb"]; the dts object facility available in script task component. should replace line with:
connectionmanager cm = connections["oledb"]; i've seen in code access connection manager in way, maybe left behind.
b) validate if connection manager either ado.net connection manager or oledb one, changed part of code:
dbconnection connection = connections[_connectionname].acquireconnection(null) dbconnection; connectionmanager cm = dts.connections["oledb"]; microsoft.sqlserver.dts.runtime.wrapper.idtsconnectionmanagerdatabaseparameters100 cmparams = cm.innerobject microsoft.sqlserver.dts.runtime.wrapper.idtsconnectionmanagerdatabaseparameters100; oledbconnection conn = cmparams.getconnectionforschema() oledbconnection; if (connection == null) { componentevents.fireerror(0, method_name, "the connection not valid ado.net connection", "", -1); return dtsexecresult.failure; } first, added private variable @ class level store connection:
private dbconnection _connection; and then, modified validation check if connection ado.net, , in case it's not, check if it's oledb:
_connection = connections[_connectionname].acquireconnection(null) dbconnection; if (_connection == null) { connectionmanager cm = connections[_connectionname]; microsoft.sqlserver.dts.runtime.wrapper.idtsconnectionmanagerdatabaseparameters100 cmparams = cm.innerobject microsoft.sqlserver.dts.runtime.wrapper.idtsconnectionmanagerdatabaseparameters100; _connection = cmparams.getconnectionforschema() oledbconnection; if (_connection == null) { componentevents.fireerror(0, method_name, "the connection not valid ado.net or oledb connection", "", -1); return dtsexecresult.failure; } } note replaced hardcoded "oledb" _connectionname variable, , modified error string when _connection null in both cases.
c) execute command using oledb provider necesary following changes:
- use new
_connectionvariable, wich holds retrieved connection. - the parameters added command must
oledbparameterinstead ofsqlparameter. - the ole db .net provider not support named parameters. so,
valuespart ininsertstatement command needs modified use?.
d) complete working code:
using system; using system.data; using system.data.common; using system.data.oledb; using system.data.sqlclient; using microsoft.sqlserver.dts.runtime; //using system.data.oledb; namespace aoc.sqlserver.dts.tasks { [dtstask( displayname = "custom logging task", description = "writes logging info table")] public class customloggingtask : task { private string _packagename; private string _taskname; private string _errorcode; private string _errordescription; private string _machinename; private double _packageduration; private string _connectionname; private string _eventtype; private string _executionid; private datetime _handlerdatetime; private string _uid; public string connectionname { set { _connectionname = value; } { return _connectionname; } } public string event { set { _eventtype = value; } { return _eventtype; } } private dbconnection _connection; public override dtsexecresult validate(connections connections, variabledispenser variabledispenser, idtscomponentevents componentevents, idtslogging log) { const string method_name = "customloggingtask-validate"; try { if (string.isnullorempty(_eventtype)) { componentevents.fireerror(0, method_name, "the event property must specified", "", -1); return dtsexecresult.failure; } if (string.isnullorempty(_connectionname)) { componentevents.fireerror(0, method_name, "no connection has been specified", "", -1); return dtsexecresult.failure; } //sqlconnection connection = connections[_connectionname].acquireconnection(null) sqlconnection; _connection = connections[_connectionname].acquireconnection(null) dbconnection; if (_connection == null) { connectionmanager cm = connections[_connectionname]; microsoft.sqlserver.dts.runtime.wrapper.idtsconnectionmanagerdatabaseparameters100 cmparams = cm.innerobject microsoft.sqlserver.dts.runtime.wrapper.idtsconnectionmanagerdatabaseparameters100; _connection = cmparams.getconnectionforschema() oledbconnection; if (_connection == null) { componentevents.fireerror(0, method_name, "the connection not valid ado.net or oledb connection", "", -1); return dtsexecresult.failure; } } if (!variabledispenser.contains("system::sourceid")) { componentevents.fireerror(0, method_name, "no system::sourceid variable available. task can used in event handler", "", -1); return dtsexecresult.failure; } return dtsexecresult.success; } catch (exception exc) { componentevents.fireerror(0, method_name, "validation failed: " + exc.tostring(), "", -1); return dtsexecresult.failure; } } public override dtsexecresult execute(connections connections, variabledispenser variabledispenser, idtscomponentevents componentevents, idtslogging log, object transaction) { try { string commandtext = null; readvariables(variabledispenser); //dbconnection connection = connections[_connectionname].acquireconnection(transaction) dbconnection; //sqlconnection connection = (sqlconnection)connections[_connectionname].acquireconnection(transaction); dbcommand command = null; //using (sqlcommand command = new sqlcommand()) if (_connection sqlconnection) { commandtext = @"insert ssislog (eventtype, packagename, taskname, eventcode, eventdescription, packageduration, host, executionid, eventhandlerdatetime,uid) values (@eventtype, @packagename, @taskname, @eventcode, @eventdescription, @packageduration, @host, @executionid, @handlerdatetime,@uid)"; command = new sqlcommand(); command.parameters.add(new sqlparameter("@eventtype", _eventtype)); command.parameters.add(new sqlparameter("@packagename", _packagename)); command.parameters.add(new sqlparameter("@taskname", _taskname)); command.parameters.add(new sqlparameter("@eventcode", _errorcode ?? string.empty)); command.parameters.add(new sqlparameter("@eventdescription", _errordescription ?? string.empty)); command.parameters.add(new sqlparameter("@packageduration", _packageduration)); command.parameters.add(new sqlparameter("@host", _machinename)); command.parameters.add(new sqlparameter("@executionid", _executionid)); command.parameters.add(new sqlparameter("@handlerdatetime", _handlerdatetime)); command.parameters.add(new sqlparameter("@uid", _uid)); } else if (_connection oledbconnection) { commandtext = @"insert ssislog (eventtype,packagename,taskname,eventcode,eventdescription,packageduration,host,executionid,eventhandlerdatetime,uid) values (?,?,?,?,?,?,?,?,?,?)"; command = new oledbcommand(); command.parameters.add(new oledbparameter("@eventtype", _eventtype)); command.parameters.add(new oledbparameter("@packagename", _packagename)); command.parameters.add(new oledbparameter("@taskname", _taskname)); command.parameters.add(new oledbparameter("@eventcode", _errorcode ?? string.empty)); command.parameters.add(new oledbparameter("@eventdescription", _errordescription ?? string.empty)); command.parameters.add(new oledbparameter("@packageduration", _packageduration)); command.parameters.add(new oledbparameter("@host", _machinename)); command.parameters.add(new oledbparameter("@executionid", _executionid)); command.parameters.add(new oledbparameter("@handlerdatetime", _handlerdatetime)); command.parameters.add(new oledbparameter("@uid", _uid)); } command.commandtext = commandtext; command.commandtype = commandtype.text; command.connection = _connection; command.executenonquery(); _connection.close(); return dtsexecresult.success; } catch (exception exc) { componentevents.fireerror(0, "customloggingtask-execute", "task errored: " + exc.tostring(), "", -1); return dtsexecresult.failure; } } private void readvariables(variabledispenser variabledispenser) { variabledispenser.lockforread("system::starttime"); variabledispenser.lockforread("system::packagename"); variabledispenser.lockforread("system::sourcename"); variabledispenser.lockforread("system::machinename"); variabledispenser.lockforread("system::executioninstanceguid"); variabledispenser.lockforread("system::eventhandlerstarttime"); variabledispenser.lockforread("user::uid"); bool includeserror = variabledispenser.contains("system::errorcode"); if (includeserror) { variabledispenser.lockforread("system::errorcode"); variabledispenser.lockforread("system::errordescription"); } variables vars = null; variabledispenser.getvariables(ref vars); datetime starttime = (datetime)vars["system::starttime"].value; _packageduration = datetime.now.subtract(starttime).totalseconds; _packagename = vars["system::packagename"].value.tostring(); _taskname = vars["system::sourcename"].value.tostring(); _machinename = vars["system::machinename"].value.tostring(); _executionid = vars["system::executioninstanceguid"].value.tostring(); _handlerdatetime = (datetime)vars["system::eventhandlerstarttime"].value; _uid = vars["user::uid"].value.tostring(); if (includeserror) { _errorcode = vars["system::errorcode"].value.tostring(); _errordescription = vars["system::errordescription"].value.tostring(); } // release variable locks. vars.unlock(); // reset dispenser variabledispenser.reset(); } } } just record, leave links i've found useful develop, deploy , debug custom components (but maybe have went through them!):
http://bennyaustin.wordpress.com/2009/06/30/steps-to-build-and-deploy-custom-ssis-components/
http://msdn.microsoft.com/en-us/library/ms403356%28v=sql.105%29.aspx
http://toddmcdermid.blogspot.com.ar/2009/06/converting-your-script-task-into-custom_22.html
cheers.
Comments
Post a Comment