Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Save ImAbhishekTomar/b67665211779c9557cce2df9108a0196 to your computer and use it in GitHub Desktop.
Save ImAbhishekTomar/b67665211779c9557cce2df9108a0196 to your computer and use it in GitHub Desktop.
Script Component - OdbcConnection Source Connection
#region Help: Introduction to the Script Component
/* The Script Component allows you to perform virtually any operation that can be accomplished in
* a .Net application within the context of an Integration Services data flow.
*
* Expand the other regions which have "Help" prefixes for examples of specific ways to use
* Integration Services features within this script component. */
#endregion
#region Namespaces
using System;
using System.Data;
using System.Data.Odbc;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
#endregion
/// <summary>
/// This is the class to which to add your code. Do not change the name, attributes, or parent
/// of this class.
/// </summary>
[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
#region Help: Using Integration Services variables and parameters
/* To use a variable in this script, first ensure that the variable has been added to
* either the list contained in the ReadOnlyVariables property or the list contained in
* the ReadWriteVariables property of this script component, according to whether or not your
* code needs to write into the variable. To do so, save this script, close this instance of
* Visual Studio, and update the ReadOnlyVariables and ReadWriteVariables properties in the
* Script Transformation Editor window.
* To use a parameter in this script, follow the same steps. Parameters are always read-only.
*
* Example of reading from a variable or parameter:
* DateTime startTime = Variables.MyStartTime;
*
* Example of writing to a variable:
* Variables.myStringVariable = "new value";
*/
#endregion
#region Help: Using Integration Services Connnection Managers
/* Some types of connection managers can be used in this script component. See the help topic
* "Working with Connection Managers Programatically" for details.
*
* To use a connection manager in this script, first ensure that the connection manager has
* been added to either the list of connection managers on the Connection Managers page of the
* script component editor. To add the connection manager, save this script, close this instance of
* Visual Studio, and add the Connection Manager to the list.
*
* If the component needs to hold a connection open while processing rows, override the
* AcquireConnections and ReleaseConnections methods.
*
* Example of using an ADO.Net connection manager to acquire a SqlConnection:
* object rawConnection = Connections.SalesDB.AcquireConnection(transaction);
* SqlConnection salesDBConn = (SqlConnection)rawConnection;
*
* Example of using a File connection manager to acquire a file path:
* object rawConnection = Connections.Prices_zip.AcquireConnection(transaction);
* string filePath = (string)rawConnection;
*
* Example of releasing a connection manager:
* Connections.SalesDB.ReleaseConnection(rawConnection);
*/
#endregion
#region Help: Firing Integration Services Events
/* This script component can fire events.
*
* Example of firing an error event:
* ComponentMetaData.FireError(10, "Process Values", "Bad value", "", 0, out cancel);
*
* Example of firing an information event:
* ComponentMetaData.FireInformation(10, "Process Values", "Processing has started", "", 0, fireAgain);
*
* Example of firing a warning event:
* ComponentMetaData.FireWarning(10, "Process Values", "No rows were received", "", 0);
*/
#endregion
ConnectionManagerOdbc connMgr;
OdbcConnection odbcConn;
OdbcDataReader odbcReader;
public override void AcquireConnections(object Transaction)
{
connMgr = (ConnectionManagerOdbc)this.Connections.Connection;
odbcConn = (OdbcConnection)connMgr.AcquireConnection(null);
}
public override void PreExecute()
{
base.PreExecute();
string query = Variables.queryprovider;
OdbcCommand cmd = new OdbcCommand(query, odbcConn);
odbcReader = cmd.ExecuteReader();
}
public override void PostExecute()
{
base.PostExecute();
odbcReader?.Close();
}
public override void CreateNewOutputRows()
{
while (odbcReader.Read())
{
{
try
{
OutputUBHearderBuffeBuffer.AddRow();
OutputUBHearderBuffeBuffer.CCN = Convert.IsDBNull(odbcReader?.GetString(0)) ? "" : odbcReader?.GetString(0);
OutputUBHearderBuffeBuffer.MEMID = Convert.IsDBNull(odbcReader?.GetString(1)) ? "" : odbcReader?.GetString(1);
OutputUBHearderBuffeBuffer.PRVADDR = Convert.IsDBNull(odbcReader?.GetString(2)) ? "" : odbcReader?.GetString(2);
OutputUBHearderBuffeBuffer.PRVCITY = Convert.IsDBNull(odbcReader?.GetString(3)) ? "" : odbcReader?.GetString(3);
OutputUBHearderBuffeBuffer.PRVST = Convert.IsDBNull(odbcReader?.GetString(4)) ? "" : odbcReader?.GetString(4);
OutputUBHearderBuffeBuffer.PRVZIP = Convert.IsDBNull(odbcReader?.GetString(5)) ? "" : odbcReader?.GetString(5);
OutputUBHearderBuffeBuffer.SRVCFACLOCNAME = Convert.IsDBNull(odbcReader?.GetString(6)) ? "" : odbcReader?.GetString(6);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
}
}
public override void ReleaseConnections()
{
connMgr.ReleaseConnection(odbcConn);
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment