none
Loading partial rows during package execution

    Question

  • Hi All,

     

    I have created run-time package in C# Windows Application which i use to read RAWFile data and fill recordset to populate DataGridView.

     

    Now, the problem i am here facing is Loading partial Rows (i.e. 1000 rows at first load then pause or msgbox to see more)during package execution.

     

    I tried other way (Loading 1000 rows using recordcount ) out but that gives perfomance issues.

     

    So, can anyone help me sorting this thing out.

     

    I would be very thankful to you.

     

    Thanks.

     

     

    Wednesday, October 15, 2008 7:08 AM

Answers

  • Hi Duane,

     

    I am glad that i have found one solution the way you told and it worked for me.

     

    Here i am quoting a copy to my source to help the community of SSIS 2005.

     

    Can you please have a look and let me know if it anything more i can add or edit to make it better.

     

    Your comments are always welcome.

     

    I am now on moving for one more addition to it reading packages from SQL Server 2005 and loading them to a treeview.

     

    If anyone of you have explored it or if any one of you can help letting me know from the community.

     

     

    //*********************************************************************************************

    // Description: Moving data from the RAWFile to Grid/RecordSet using Runtime SSIS Package

    //*********************************************************************************************

    using System;

    using System.Text;

    using System.Collections.Generic;

    using System.Diagnostics;

    using Microsoft.SqlServer.Dts.Runtime;

    using Microsoft.SqlServer.Dts.Runtime.Wrapper;

    using Microsoft.SqlServer.Dts.Pipeline;

    using Microsoft.SqlServer.Dts.Pipeline.Wrapper;

    using Microsoft.SqlServer.Dts;

    using System.IO;

    using System.Data;

    using ADODB;

     

    namespace Utility

    {

    public class clsDataFlow

    {

    #region Private Members/Properties

    //Declares Package,Pipe,Connection Members

    private Microsoft.SqlServer.Dts.Runtime.Package objPackage = new Microsoft.SqlServer.Dts.Runtime.Package();

    private MainPipe objMainPipe = null;

    //private ConnectionManager objConnManager = null;

    private IDTSPath90 objIDTSPath = null;

    private DataTable objDataTable = null;

    //Declares Metadata Members

    private Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSComponentMetaData90 objIDTSSRCMetaData = null;

    private Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSComponentMetaData90 objIDTSDSTRecordsetMetaData = null;

    //Declares Wrapper Members

    private CManagedComponentWrapper objSourceWrapper;

    private CManagedComponentWrapper objDestinationRecordsetWrapper;

    //Declares Moniker & Constants

    private const string strDataFlowTaskMoniker = "DTS.Pipeline.1";

    //Declares DataFlow Component Id

    private const string strSourceDFComponentID = "{E2568105-9550-4F71-A638-B7FE42E66930}";

    private const string strDestinationDFRecordsetComponentID = "{FFF3C376-C5BF-45BC-A3A3-C8C9DEDD2CF5}";

    //Declares Other Memebers

    private string _strRAWFileName = string.Empty ;

    private Microsoft.SqlServer.Dts.Runtime.Variable _dataVariable = null;

    private StringBuilder _strRAWColNames = new StringBuilder() ;

    private char delim = ',';

    private DataRow drRAWData = null;

    private string strValue = null;

    #endregion

    #region Public Members/Properties

    public string strRAWFileName

    {

    get { return _strRAWFileName; }

    set { _strRAWFileName = value; }

    }

    public string strPKGFileName

    {

    get { return _strPKGFileName; }

    set { _strPKGFileName = value; }

    }

    public string strRAWColNames

    {

    get { return _strRAWColNames.ToString() ; }

    set { _strRAWColNames.Append(value); }

    }

    #endregion

    #region Constructor

    public clsDataFlow()

    {

    //Constructor

    }

    #endregion

    #region Creates Package

    // Creates Runtime Package

    public void CreatePackage()

    {

    objPackage = new Microsoft.SqlServer.Dts.Runtime.Package();

    objPackage.CreationDate = DateTime.Now;

    objPackage.ProtectionLevel = Microsoft.SqlServer.Dts.Runtime.DTSProtectionLevel.DontSaveSensitive;

    objPackage.Name = "RAWReader";

    objPackage.Description = "RAW To Recordset Conversion Package";

    objPackage.DelayValidation = true;

    objPackage.PackageType = Microsoft.SqlServer.Dts.Runtime.DTSPackageType.DTSDesigner90;

    _dataVariable = objPackage.Variables.Add("DataRecords", false, "User", new System.Object());

    }

    #endregion

    #region Call Dataflow Component Methods

    #region Source And Destination Component Methods

    //Creates Source Component ( Output Collection )

    internal void CreateSourceComponent(string strRAWFilePath)

    {

    // Creates mainpipe for the executable component.

    objMainPipe = ((Microsoft.SqlServer.Dts.Runtime.TaskHost)objPackage.Executables.Add(strDataFlowTaskMoniker)).InnerObject as MainPipe;

    // Adds a component from the MainPipe to the Source Metadata.

    objIDTSSRCMetaData = objMainPipe.ComponentMetaDataCollection.New();

    // Sets the source component class id.

    objIDTSSRCMetaData.ComponentClassID = strSourceDFComponentID;

    // Sets the locale property.

    objIDTSSRCMetaData.LocaleID = -1;

    // Instantiates the Wrapper adding Source Metadata.

    objSourceWrapper = objIDTSSRCMetaData.Instantiate();

    // Provides default properties

    objSourceWrapper.ProvideComponentProperties();

    // Sets RAWFile Component Property

    objSourceWrapper.SetComponentProperty("AccessMode", 0);

    objSourceWrapper.SetComponentProperty("FileName", strRAWFilePath);

    objSourceWrapper.SetComponentProperty("FileNameVariable", null);

    // Sets the connection

    objSourceWrapper.AcquireConnections(null);

    // Reinitializes the Source Metadata.

    objSourceWrapper.ReinitializeMetaData();

    // Fetch ColumnNames for the MetaData

    if (_strRAWColNames.Length == 0 && _strRAWColNames.ToString() == string.Empty )

    {

    foreach (IDTSOutputColumn90 idtsOutPutColumn in objIDTSSRCMetaData.OutputCollection[0].OutputColumnCollection)

    {

    _strRAWColNames.Append(idtsOutPutColumn.Name + ",");

    }

    }

    // Releases the Wrapper connection.

    objSourceWrapper.ReleaseConnections();

    }

    internal DataTable CreateDestinationRecordSetComponent(ref int iRow,ref int iLimit, ref int iEOF)

    {

    int iDestInputID = 0;

    string strIndex = string.Empty;

    int iIndex = 0;

    IDTSInput90 objIDTSInput = null;

    IDTSVirtualInput90 objIDTSVirtualInput = null;

    string[] strFilterArray = null;

    if (objMainPipe != null)

    {

    // Adds a component from the MainPipe to the Destination Recordset Metadata.

    objIDTSDSTRecordsetMetaData = objMainPipe.ComponentMetaDataCollection.New();

    // Sets the Destination recordset component name

    objIDTSDSTRecordsetMetaData.Name = "Destination Component";

    // Sets the destination recordset component class id.

    objIDTSDSTRecordsetMetaData.ComponentClassID = strDestinationDFRecordsetComponentID;

    // Sets the locale id.

    objIDTSDSTRecordsetMetaData.LocaleID = -1;

    // Instantiates the Wrapper adding Destination Recordset Metadata.

    objDestinationRecordsetWrapper = objIDTSDSTRecordsetMetaData.Instantiate();

    // Provides default properties

    objDestinationRecordsetWrapper.ProvideComponentProperties();

    objDestinationRecordsetWrapper.SetComponentProperty("VariableName", "User:Big SmileataRecords");

    // Creates the IDTSPath from the Object MainPipe.

    objIDTSPath = objMainPipe.PathCollection.New();

    objIDTSPath.AttachPathAndPropagateNotifications(objIDTSSRCMetaData.OutputCollection[0],

    objIDTSDSTRecordsetMetaData.InputCollection[0]);

    objIDTSInput = objIDTSDSTRecordsetMetaData.InputCollection[0];

    // Gets the Virtual Input Column Collection from the Destination InputCollection

    objIDTSVirtualInput = objIDTSInput.GetVirtualInput();

    iDestInputID = Convert.ToInt32(objIDTSInput.ID);

    if (strRAWColNames != null && strRAWColNames.Equals(string.Empty)== false && strRAWColNames != "" )

    {

    if (strRAWColNames.EndsWith(","))

    {

    iIndex = strRAWColNames.LastIndexOf(",");

    strIndex = strRAWColNames.Remove(iIndex);

    }

    strFilterArray = strIndex.Split(delim);

    }

    foreach (IDTSVirtualInputColumn90 objIDTSVirtualInputColumn in objIDTSVirtualInput.VirtualInputColumnCollection)

    {

    if (strFilterArray == null)

    {

    objDestinationRecordsetWrapper.SetUsageType(iDestInputID, objIDTSVirtualInput, objIDTSVirtualInputColumn.LineageID, DTSUsageType.UT_READONLY);

    }

    else

    {

    if (FilterField(objIDTSVirtualInputColumn.Name, strFilterArray) == false)

    objDestinationRecordsetWrapper.SetUsageType(iDestInputID, objIDTSVirtualInput, objIDTSVirtualInputColumn.LineageID, DTSUsageType.UT_READONLY);

    }

    }

    // Sets the connection

    objDestinationRecordsetWrapper.AcquireConnections(null);

    // Reinitializes the Source Metadata.

    objDestinationRecordsetWrapper.ReinitializeMetaData();

    // Releases the Wrapper connection.

    objDestinationRecordsetWrapper.ReleaseConnections();

    //Executes the Package

    Execute_Package();

    //Read records.

    objDataTable = new DataTable();

    try

    {

    ADODB.Recordset objRSRawData = (ADODB.Recordset)(_dataVariable.Value);

    foreach (Field _field in objRSRawData.Fields)

    {

    objDataTable.Columns.Add(_field.Name);

    }

    objRSRawData.MoveNext();

    iEOF = objRSRawData.RecordCount;

    if (iLimit < iEOF)

    {

    iRow += 1;

    if ((iEOF - iLimit) < 1000)

    iLimit = iEOF;

    else

    iLimit += 1000;

    // Load 1000 records per run.

    objDataTable = GetPaging(objDataTable, objRSRawData, ref iRow, ref iLimit);

    }

    }

    catch (Exception ex)

    {

    string str = ex.Message;

    }

    finally

    {}

    }

    return objDataTable;

    }

    #endregion

    #region Other Supporting Dataflow Methods

    //Gets the Paging DataTable with 1000 records.

    internal DataTable GetPaging(DataTable dtTemp, Recordset rsTemp, ref int iRow, ref int iLimit)

    {

    int RowCount = 1;

    while (!rsTemp.EOF)

    {

    if (RowCount == iRow && iRow != iLimit)

    {

    drRAWData = objDataTable.NewRow();

    for (int iRFCount = 0; iRFCount < rsTemp.Fields.Count; iRFCount++)

    {

    if (rsTemp.Fields[iRFCount].Name == "ErrorCode")

    {

    strValue = objIDTSDSTRecordsetMetaData.GetErrorDescription((int)rsTemp.Fields[iRFCount].Value);

    }

    else

    {

    strValue = rsTemp.Fields[iRFCount].Value.ToString();

    drRAWData[iRFCount] = strValue;

    }

    }

    objDataTable.Rows.Add(drRAWData);

    iRow++;

    }

    if (iRow == iLimit)

    {

    break;

    }

    else

    {

    RowCount++;

    rsTemp.MoveNext();

    }

    }

    return objDataTable;

    }

    //Returns true if Unchecked Field presents in strArray or false for Checked Fields

    internal bool FilterField(string strColName, string[] strArray)

    {

    bool bFilter = false;

    if (strArray != null || (strArray.ToString() != string.Empty))

    {

    foreach (string strComp in strArray)

    {

    if (strColName.Equals(strComp))

    {

    bFilter = true;

    }

    }

    }

    return bFilter;

    }

    //Clears the RAWColumnNames

    internal void ClearRAWColNames()

    {

    _strRAWColNames.Remove(0, _strRAWColNames.Length);

    }

     

    #endregion

    #endregion

    #region Executes the Package

    // Saves a new package

    internal void Execute_Package()

    {

    Microsoft.SqlServer.Dts.Runtime.Application app = new Microsoft.SqlServer.Dts.Runtime.Application();

    app.SaveToXml("C:\\Temp\\RawReader.dtsx", objPackage, null);

    Microsoft.SqlServer.Dts.Runtime.DTSExecResult result = objPackage.Execute();

    Trace.Write(result);

    }

    #endregion

    }

    }

    //=================================================

    // Calling Method to clsDataFlow

    //=================================================

    #region Loads RawData to DataGridView

    // Loads RAWData to DataGridView

    public void LoadGrid()

    {

    objDataFlow = new clsDataFlow();

    objDataFlow.strRAWFileName = strRAWFileName;

    objDataFlow.CreatePackage();

    objDataFlow.CreateSourceComponent(objDataFlow.strRAWFileName);

    for (int i = 0; i < chklbRAWFields.Items.Count; i++)

    {

    //Prepares string of UnChecked Items(Unwanted Columns) in chkListbox

    // To Display Grid without these columns.

    if (chklbRAWFields.GetItemChecked(i) == false)

    {

    strRAWColNames += chklbRAWFields.ItemsIdea.ToString() + ",";

    }

    }

    if (this.strRAWColNames != null && this.strRAWColNames.Length != 0)

    objDataFlow.strRAWColNames = this.strRAWColNames;

    else

    objDataFlow.ClearRAWColNames();

    dtLoadRawData = objDataFlow.CreateDestinationRecordSetComponent(ref iRow, ref iLimit, ref iEOF);

    if (dtLoadRawData != null)

    {

    dgvRAWData.Enabled = true;

    dgvRAWData.DataSource = dtLoadRawData;

    }

    if (iRow != iEOF)

    btnShowNext.Visible = true;

    else

    btnShowNext.Visible = false;

    }

    #endregion

    //=================================================

     

    Thanks again.

     

    Mit_2807

    Thursday, October 16, 2008 1:31 PM
  •  

    Hi Duane,

     

    I am glad to find another solution using DataReader as Destination for better performance and it worked for me.

     

    Here i am quoting a copy to my source to help the community of SSIS 2005.

     

    Can you please have a look and let me know if it anything more i can add or edit to make it better.

     

    Your comments are always welcome.

     

    //*************************************************************************************************************************

    // Description: SSIS 2005 Package Programming to move data from the RAWFile to DataGridView

    //                   using Data Reader as Destination

    //*************************************************************************************************************************

    #region Using Directives

    using System;

    using System.Text;

    using System.Collections.Generic;

    using Microsoft.SqlServer.Dts.Runtime;

    using Microsoft.SqlServer.Dts.Runtime.Wrapper;

    using Microsoft.SqlServer.Dts.Pipeline;

    using Microsoft.SqlServer.Dts.Pipeline.Wrapper;

    using Microsoft.SqlServer.Dts;

    using System.IO;

    using System.Data;

    using ADODB;

    #endregion

    namespace Utility

    {

    internal class clsDataFlow

    {

    #region Private Members

    //Declares Package,Pipe,Connection Members

    private Microsoft.SqlServer.Dts.Runtime.Package _objPackage = new Microsoft.SqlServer.Dts.Runtime.Package();

    private MainPipe _objMainPipe = null;

    private IDTSPath90 _objIDTSPath = null;

    private Microsoft.SqlServer.Dts.Runtime.Application app = null;

    //Declares Metadata Members

    private Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSComponentMetaData90 _objIDTSSRCMetaData = null;

    private Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSComponentMetaData90 _objIDTSDSTReaderMetaData = null;

    //Declares Wrapper Members

    private CManagedComponentWrapper _objSourceWrapper;

    private CManagedComponentWrapper _objDestinationReaderWrapper;

    //Declares Moniker & Constants

    private const string _strDataFlowTaskMoniker = "DTS.Pipeline.1";

    //Declares DataFlow Component Id

    private const string _strSourceDFComponentID = "{E2568105-9550-4F71-A638-B7FE42E66930}";

    private const string _strDestinationDFReaderComponentID = "{BF01D463-7089-41EE-8F05-0A6DC17CE633}";

    //Declares Other Memebers

    private string _strRAWFileName = string.Empty ;

    private string _strPKGFileName = string.Empty;

    private Microsoft.SqlServer.Dts.Runtime.Variable _dataVariable = null;

    private StringBuilder _strRAWColNames = new StringBuilder() ;

    private char _delim = ',';

    private DataRow _drRAWData = null;

    private string _strValue = null;

    private int _iDestInputID = 0;

    private string _strIndex = string.Empty;

    private int _iIndex = 0;

    private IDTSInput90 _objIDTSInput = null;

    private IDTSVirtualInput90 _objIDTSVirtualInput = null;

    private string[] _strFilterArray = null;

    private int _iRowCount = 1;

    private bool _bFilter = false;

    #endregion

    #region Constructor

    internal clsDataFlow()

    {

    //Constructor

    }

    #endregion

    #region Internal Members/Properties

    internal string strRAWFileName

    {

    get { return this._strRAWFileName; }

    set { this._strRAWFileName = value; }

    }

    internal string strPKGFileName

    {

    get { return this._strPKGFileName; }

    set { this._strPKGFileName = value; }

    }

    internal string strRAWColNames

    {

    get { return this._strRAWColNames.ToString(); }

    set { this._strRAWColNames.Append(value); }

    }

    #endregion

    #region Creates Package

    // Creates Runtime Package

    internal void CreatePackage()

    {

    _objPackage = new Microsoft.SqlServer.Dts.Runtime.Package();

    _objPackage.CreationDate = DateTime.Now;

    _objPackage.ProtectionLevel = Microsoft.SqlServer.Dts.Runtime.DTSProtectionLevel.DontSaveSensitive;

    _objPackage.Name = "RAWReader";

    _objPackage.Description = "RAW To Reader Conversion Package";

    _objPackage.DelayValidation = false;

    _objPackage.PackageType = Microsoft.SqlServer.Dts.Runtime.DTSPackageType.DTSDesigner90;

    _dataVariable = _objPackage.Variables.Add("DataRecords", false, "User", new System.Object());

    }

    #endregion

    #region Call Dataflow Component Methods

    #region Source And Destination Component Methods

    //Creates Source Component ( Output Collection )

    internal void CreateSourceComponent(string strRAWFilePath)

    {

    // Creates mainpipe for the executable component.

    _objMainPipe = ((Microsoft.SqlServer.Dts.Runtime.TaskHost)_objPackage.Executables.Add(_strDataFlowTaskMoniker)).InnerObject as MainPipe;

    // Adds a component from the MainPipe to the Source Metadata.

    _objIDTSSRCMetaData = _objMainPipe.ComponentMetaDataCollection.New();

    // Sets the source component class id.

    _objIDTSSRCMetaData.ComponentClassID = _strSourceDFComponentID;

    // Sets the locale property.

    _objIDTSSRCMetaData.LocaleID = -1;

    // Instantiates the Wrapper adding Source Metadata.

    _objSourceWrapper = _objIDTSSRCMetaData.Instantiate();

    // Provides default properties

    _objSourceWrapper.ProvideComponentProperties();

    // Sets RAWFile Component Property

    _objSourceWrapper.SetComponentProperty("AccessMode", 0);

    _objSourceWrapper.SetComponentProperty("FileName", strRAWFilePath);

    _objSourceWrapper.SetComponentProperty("FileNameVariable", null);

    // Sets the connection

    _objSourceWrapper.AcquireConnections(null);

    // Reinitializes the Source Metadata.

    _objSourceWrapper.ReinitializeMetaData();

    // Fetch ColumnNames for the MetaData

    if (_strRAWColNames.Length == 0 && _strRAWColNames.ToString() == string.Empty )

    {

    foreach (IDTSOutputColumn90 idtsOutPutColumn in _objIDTSSRCMetaData.OutputCollection[0].OutputColumnCollection)

    {

    _strRAWColNames.Append(idtsOutPutColumn.Name + ",");

    }

    }

    // Releases the Wrapper connection.

    _objSourceWrapper.ReleaseConnections();

    }

    //Creates Destination Component ( Input Collection )

    internal void CreateDestinationReaderComponent()

    {

    //1>DataReader String:The class name of the DataReader destination.

    //2>FailOnTimeout Boolean:Indicates whether to fail when a ReadTimeout occurs. The default value of this property is False.

    //3>ReadTimeout Integer:The number of milliseconds before a time-out occurs. The default value of this property is 30000 (30 seconds).

     

    // Adds a component from the MainPipe to the Destination Recordset Metadata.

    _objIDTSDSTReaderMetaData = _objMainPipe.ComponentMetaDataCollection.New();

    // Sets the Destination recordset component name

    _objIDTSDSTReaderMetaData.Name = "Test";

    // Sets the destination recordset component class id.

    _objIDTSDSTReaderMetaData.ComponentClassID = _strDestinationDFReaderComponentID;

    object o = new object();

    IDTSCustomProperty90 _property = _objIDTSDSTReaderMetaData.CustomPropertyCollection.New();

    _property.Name = "DataReader";

    _property.Value = o;

    _property = _objIDTSDSTReaderMetaData.CustomPropertyCollection.New();

    _property.Name = "FailOnTimeout";

    _property.Value = false;

    _property = _objIDTSDSTReaderMetaData.CustomPropertyCollection.New();

    _property.Name = "ReadTimeout";

    _property.Value = 30000;

    _property = _objIDTSDSTReaderMetaData.CustomPropertyCollection.New();

    _property.Name = "UserComponentTypeName";

    _property.Value = "Microsoft.SqlServer.Dts.Pipeline.DataReaderDestinationAdapter, Microsoft.SqlServer.DataReaderDest, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91";

     

    // Instantiates the Wrapper adding Destination Recordset Metadata.

    _objDestinationReaderWrapper = _objIDTSDSTReaderMetaData.Instantiate();

    // Provides default properties

    _objDestinationReaderWrapper.ProvideComponentProperties();

    // Sets the connection

    _objDestinationReaderWrapper.AcquireConnections(null);

    // Reinitializes the Source Metadata.

    _objDestinationReaderWrapper.ReinitializeMetaData();

    // Releases the Wrapper connection.

    _objDestinationReaderWrapper.ReleaseConnections();

    // Creates the IDTSPath from the Object MainPipe.

    _objIDTSPath = _objMainPipe.PathCollection.New();

     

    _objIDTSPath.AttachPathAndPropagateNotifications(_objIDTSSRCMetaData.OutputCollection[0],

    _objIDTSDSTReaderMetaData.InputCollection[0]);

    _objIDTSInput = _objIDTSDSTReaderMetaData.InputCollection[0];

    // Gets the Virtual Input Column Collection from the Destination InputCollection

    _objIDTSVirtualInput = _objIDTSInput.GetVirtualInput();

    _iDestInputID = Convert.ToInt32(_objIDTSInput.ID);

    //Splits the RAW Columnnames in an array of string

    if (strRAWColNames != null && strRAWColNames.Equals(string.Empty) == false && strRAWColNames != "")

    {

    if (strRAWColNames.EndsWith(","))

    {

    _iIndex = strRAWColNames.LastIndexOf(",");

    _strIndex = strRAWColNames.Remove(_iIndex);

    }

    _strFilterArray = _strIndex.Split(_delim);

    }

    //Set Usagetype According to FilterArray

    foreach (IDTSVirtualInputColumn90 objIDTSVirtualInputColumn in _objIDTSVirtualInput.VirtualInputColumnCollection)

    {

    if (_strFilterArray == null)

    {

    // When FilterArray string is null

    _objDestinationReaderWrapper.SetUsageType(_iDestInputID, _objIDTSVirtualInput, objIDTSVirtualInputColumn.LineageID, DTSUsageType.UT_READONLY);

    }

    else

    {

    // When FilterArray string is not null

    if (FilterField(objIDTSVirtualInputColumn.Name, _strFilterArray) == false)

    _objDestinationReaderWrapper.SetUsageType(_iDestInputID, _objIDTSVirtualInput, objIDTSVirtualInputColumn.LineageID, DTSUsageType.UT_READONLY);

    }

    }

    // Sets the connection

    _objDestinationReaderWrapper.AcquireConnections(null);

    // Reinitializes the Source Metadata.

    _objDestinationReaderWrapper.ReinitializeMetaData();

    // Releases the Wrapper connection.

    _objDestinationReaderWrapper.ReleaseConnections();

    }

    #endregion

    #region Other Supporting Dataflow Methods

    //Returns true if Unchecked Field presents in strArray or false for Checked Fields

    internal bool FilterField(string strDestInputColumnName, string[] strArrUncheckedColumns)

    {

    if (strArrUncheckedColumns != null || (strArrUncheckedColumns.ToString() != string.Empty))

    {

    foreach (string strUncheckedCol in strArrUncheckedColumns)

    {

    if (strDestInputColumnName.Equals(strUncheckedCol))

    {

    //When match found for filter

    _bFilter = true;

    }

    }

    }

    return _bFilter;

    }

    //Clears the RAWColumnNames

    internal void ClearRawColumnNames()

    {

    //Removes stringbuilder object value

    _strRAWColNames.Remove(0, _strRAWColNames.Length);

    }

    #endregion

    #endregion

    #region Save Package

    // Saves a new package

    internal void Save_Package()

    {

    //Creates DTS Runtime Application's instance

    app = new Microsoft.SqlServer.Dts.Runtime.Application();

    //Save DTSX File to a local path

    app.SaveToXml("C:\\Temp\\RawReader.dtsx", _objPackage, null);

    }

    #endregion

    }

    }

     

    //*************************************************************************************************************************

    // Description: Calling Method to clsDataFlow

    //*************************************************************************************************************************

    #region BindDataGridView

    // Loads RAWData to DataGridView

    internal void BindDataGridView()

    {

    _objDataFlow = new clsDataFlow();

    _objDataFlow.strRAWFileName = strRAWFileName;

    //Creates Package for DataFlow

    _objDataFlow.CreatePackage();

    //Creates Source Component - DataFlow

    _objDataFlow.CreateSourceComponent(_objDataFlow.strRAWFileName);

    for (int iField = 0; iField < chklbRAWFields.Items.Count; iField++)

    {

    // To view grid with only checked items(Filtering Unchecked)

    if (chklbRAWFields.GetItemChecked(iField) == false)

    {

    strRAWColNames += chklbRAWFields.Items[iField].ToString() + ",";

    }

    }

    if (this.strRAWColNames != null && this.strRAWColNames.Length != 0)

    _objDataFlow.strRAWColNames = this.strRAWColNames;

    else

    _objDataFlow.ClearRawColumnNames();

    //Creates Destination Component - DataFlow

    _objDataFlow.CreateDestinationReaderComponent();

    _objDataFlow.Save_Package();

    DataSet dsGridView = GetGridViewData();

    if (dsGridView!= null)

    {

    dgvRAWData.Enabled = true;

    dgvRAWData.DataSource = dsGridView; //.Tables[0].DefaultView

    dgvRAWData.DataMember = dsGridView.Tables[0].TableName;

    }

    }

    #endregion

     

     

    #region GetGridViewData

    internal DataSet GetGridViewData()

    {

    string dtexecArgs;

    string dataReaderName;

    DtsConnection dtsConnection;

    DtsCommand dtsCommand;

    DataSet dsPackageData = new DataSet();

    IDataReader dtsDataReader ;

    dtexecArgs = "-f \"C:\\Temp\\RawReader.dtsx\"";

    dataReaderName = "DataReaderDest";

    dtsConnection = new DtsConnection();

    dtsConnection.ConnectionString = dtexecArgs;

    dtsCommand = new DtsCommand(dtsConnection);

    dtsCommand.CommandText = dataReaderName;

    dtsConnection.Open();

    dtsDataReader = dtsCommand.ExecuteReader(CommandBehavior.Default);

    dsPackageData.Load(dtsDataReader, LoadOption.OverwriteChanges, dtsDataReader.GetSchemaTable().TableName);

    try

    {

    if (dtsDataReader != null)

    {

    dtsDataReader.Close();

    }

    }

    catch (Exception ex)

    {

    MessageBox.Show(("Exception closing DataReader:"

    + (ControlChars.CrLf

    + (ex.Message

    + (ControlChars.CrLf + ex.InnerException.Message)))), "Exception closing DataReader", MessageBoxButtons.OK, MessageBoxIcon.Error);

    }

    try

    {

    if ((dtsConnection.State != ConnectionState.Closed))

    {

    dtsConnection.Close();

    }

    }

    catch (Exception ex)

    {

    MessageBox.Show(("Exception closing connection:"

    + (ControlChars.CrLf

    + (ex.Message

    + (ControlChars.CrLf + ex.InnerException.Message)))), "Exception closing connection", MessageBoxButtons.OK, MessageBoxIcon.Error);

    }

    return dsPackageData;

    }

    #endregion

     

    Thanks.

    Thursday, October 23, 2008 7:06 AM

All replies

  • my first inclination was to consider a workaround.  essentially, a raw file is a temp table without the overhead of a database.  however, the contents of a raw file can only be read inside the data flow, not outside.  what this means is that a raw file must first be converted to another data structure before it can be read outside of the data flow.

     

    the ssis data flow provides a number of destination options to facilitate this conversion.  you can use one of the built-in destination components or you could create your own destination component.  the decision is yours to make.

     

    hth

    Wednesday, October 15, 2008 7:43 AM
    Moderator
  • Hi Duane,

     

    first of all thanks for an immediate response to my query.

     

    Well, i am using recordset as destination component creating one datatable to load the datagridview in windows application.

     

    Now when i am trying to process file having 10,000 or more rows using package.execute() it is getting a longtime. I feel lost. Whether it will result me with success or failure i am unable to judge.

     

    So, concluding all these in a line i am trying to process only 1000 rows at a time out of 10000 rows to reduce less loadtime or memory problem. Next run will start from 1001 to 2000,...and so..on.

     

     Is this possible?

     

    If so can you please help letting me know.

     

    Thanks again.

     

     

    Wednesday, October 15, 2008 8:56 AM
  •  Mit2807 wrote:

    Hi Duane,

     

    first of all thanks for an immediate response to my query.

     

    Well, i am using recordset as destination component creating one datatable to load the datagridview in windows application.

     

    Now when i am trying to process file having 10,000 or more rows using package.execute() it is getting a longtime. I feel lost. Whether it will result me with success or failure i am unable to judge.

     

    the row processing shouldn't take more than a few seconds.  in order to get a sense of how long the row processing takes, what i suggest you do is create a simple package that sends all of the raw file records to a recordset destination.

     

    So, concluding all these in a line i am trying to process only 1000 rows at a time out of 10000 rows to reduce less loadtime or memory problem. Next run will start from 1001 to 2000,...and so..on.

     

     Is this possible?

     

    i don't think so.  if i'm not mistaken recordset population in the data flow can occur only once per package execution.

     

    hth

    Thursday, October 16, 2008 5:45 AM
    Moderator
  • Hi Duane,

     

    I am glad that i have found one solution the way you told and it worked for me.

     

    Here i am quoting a copy to my source to help the community of SSIS 2005.

     

    Can you please have a look and let me know if it anything more i can add or edit to make it better.

     

    Your comments are always welcome.

     

    I am now on moving for one more addition to it reading packages from SQL Server 2005 and loading them to a treeview.

     

    If anyone of you have explored it or if any one of you can help letting me know from the community.

     

     

    //*********************************************************************************************

    // Description: Moving data from the RAWFile to Grid/RecordSet using Runtime SSIS Package

    //*********************************************************************************************

    using System;

    using System.Text;

    using System.Collections.Generic;

    using System.Diagnostics;

    using Microsoft.SqlServer.Dts.Runtime;

    using Microsoft.SqlServer.Dts.Runtime.Wrapper;

    using Microsoft.SqlServer.Dts.Pipeline;

    using Microsoft.SqlServer.Dts.Pipeline.Wrapper;

    using Microsoft.SqlServer.Dts;

    using System.IO;

    using System.Data;

    using ADODB;

     

    namespace Utility

    {

    public class clsDataFlow

    {

    #region Private Members/Properties

    //Declares Package,Pipe,Connection Members

    private Microsoft.SqlServer.Dts.Runtime.Package objPackage = new Microsoft.SqlServer.Dts.Runtime.Package();

    private MainPipe objMainPipe = null;

    //private ConnectionManager objConnManager = null;

    private IDTSPath90 objIDTSPath = null;

    private DataTable objDataTable = null;

    //Declares Metadata Members

    private Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSComponentMetaData90 objIDTSSRCMetaData = null;

    private Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSComponentMetaData90 objIDTSDSTRecordsetMetaData = null;

    //Declares Wrapper Members

    private CManagedComponentWrapper objSourceWrapper;

    private CManagedComponentWrapper objDestinationRecordsetWrapper;

    //Declares Moniker & Constants

    private const string strDataFlowTaskMoniker = "DTS.Pipeline.1";

    //Declares DataFlow Component Id

    private const string strSourceDFComponentID = "{E2568105-9550-4F71-A638-B7FE42E66930}";

    private const string strDestinationDFRecordsetComponentID = "{FFF3C376-C5BF-45BC-A3A3-C8C9DEDD2CF5}";

    //Declares Other Memebers

    private string _strRAWFileName = string.Empty ;

    private Microsoft.SqlServer.Dts.Runtime.Variable _dataVariable = null;

    private StringBuilder _strRAWColNames = new StringBuilder() ;

    private char delim = ',';

    private DataRow drRAWData = null;

    private string strValue = null;

    #endregion

    #region Public Members/Properties

    public string strRAWFileName

    {

    get { return _strRAWFileName; }

    set { _strRAWFileName = value; }

    }

    public string strPKGFileName

    {

    get { return _strPKGFileName; }

    set { _strPKGFileName = value; }

    }

    public string strRAWColNames

    {

    get { return _strRAWColNames.ToString() ; }

    set { _strRAWColNames.Append(value); }

    }

    #endregion

    #region Constructor

    public clsDataFlow()

    {

    //Constructor

    }

    #endregion

    #region Creates Package

    // Creates Runtime Package

    public void CreatePackage()

    {

    objPackage = new Microsoft.SqlServer.Dts.Runtime.Package();

    objPackage.CreationDate = DateTime.Now;

    objPackage.ProtectionLevel = Microsoft.SqlServer.Dts.Runtime.DTSProtectionLevel.DontSaveSensitive;

    objPackage.Name = "RAWReader";

    objPackage.Description = "RAW To Recordset Conversion Package";

    objPackage.DelayValidation = true;

    objPackage.PackageType = Microsoft.SqlServer.Dts.Runtime.DTSPackageType.DTSDesigner90;

    _dataVariable = objPackage.Variables.Add("DataRecords", false, "User", new System.Object());

    }

    #endregion

    #region Call Dataflow Component Methods

    #region Source And Destination Component Methods

    //Creates Source Component ( Output Collection )

    internal void CreateSourceComponent(string strRAWFilePath)

    {

    // Creates mainpipe for the executable component.

    objMainPipe = ((Microsoft.SqlServer.Dts.Runtime.TaskHost)objPackage.Executables.Add(strDataFlowTaskMoniker)).InnerObject as MainPipe;

    // Adds a component from the MainPipe to the Source Metadata.

    objIDTSSRCMetaData = objMainPipe.ComponentMetaDataCollection.New();

    // Sets the source component class id.

    objIDTSSRCMetaData.ComponentClassID = strSourceDFComponentID;

    // Sets the locale property.

    objIDTSSRCMetaData.LocaleID = -1;

    // Instantiates the Wrapper adding Source Metadata.

    objSourceWrapper = objIDTSSRCMetaData.Instantiate();

    // Provides default properties

    objSourceWrapper.ProvideComponentProperties();

    // Sets RAWFile Component Property

    objSourceWrapper.SetComponentProperty("AccessMode", 0);

    objSourceWrapper.SetComponentProperty("FileName", strRAWFilePath);

    objSourceWrapper.SetComponentProperty("FileNameVariable", null);

    // Sets the connection

    objSourceWrapper.AcquireConnections(null);

    // Reinitializes the Source Metadata.

    objSourceWrapper.ReinitializeMetaData();

    // Fetch ColumnNames for the MetaData

    if (_strRAWColNames.Length == 0 && _strRAWColNames.ToString() == string.Empty )

    {

    foreach (IDTSOutputColumn90 idtsOutPutColumn in objIDTSSRCMetaData.OutputCollection[0].OutputColumnCollection)

    {

    _strRAWColNames.Append(idtsOutPutColumn.Name + ",");

    }

    }

    // Releases the Wrapper connection.

    objSourceWrapper.ReleaseConnections();

    }

    internal DataTable CreateDestinationRecordSetComponent(ref int iRow,ref int iLimit, ref int iEOF)

    {

    int iDestInputID = 0;

    string strIndex = string.Empty;

    int iIndex = 0;

    IDTSInput90 objIDTSInput = null;

    IDTSVirtualInput90 objIDTSVirtualInput = null;

    string[] strFilterArray = null;

    if (objMainPipe != null)

    {

    // Adds a component from the MainPipe to the Destination Recordset Metadata.

    objIDTSDSTRecordsetMetaData = objMainPipe.ComponentMetaDataCollection.New();

    // Sets the Destination recordset component name

    objIDTSDSTRecordsetMetaData.Name = "Destination Component";

    // Sets the destination recordset component class id.

    objIDTSDSTRecordsetMetaData.ComponentClassID = strDestinationDFRecordsetComponentID;

    // Sets the locale id.

    objIDTSDSTRecordsetMetaData.LocaleID = -1;

    // Instantiates the Wrapper adding Destination Recordset Metadata.

    objDestinationRecordsetWrapper = objIDTSDSTRecordsetMetaData.Instantiate();

    // Provides default properties

    objDestinationRecordsetWrapper.ProvideComponentProperties();

    objDestinationRecordsetWrapper.SetComponentProperty("VariableName", "User:Big SmileataRecords");

    // Creates the IDTSPath from the Object MainPipe.

    objIDTSPath = objMainPipe.PathCollection.New();

    objIDTSPath.AttachPathAndPropagateNotifications(objIDTSSRCMetaData.OutputCollection[0],

    objIDTSDSTRecordsetMetaData.InputCollection[0]);

    objIDTSInput = objIDTSDSTRecordsetMetaData.InputCollection[0];

    // Gets the Virtual Input Column Collection from the Destination InputCollection

    objIDTSVirtualInput = objIDTSInput.GetVirtualInput();

    iDestInputID = Convert.ToInt32(objIDTSInput.ID);

    if (strRAWColNames != null && strRAWColNames.Equals(string.Empty)== false && strRAWColNames != "" )

    {

    if (strRAWColNames.EndsWith(","))

    {

    iIndex = strRAWColNames.LastIndexOf(",");

    strIndex = strRAWColNames.Remove(iIndex);

    }

    strFilterArray = strIndex.Split(delim);

    }

    foreach (IDTSVirtualInputColumn90 objIDTSVirtualInputColumn in objIDTSVirtualInput.VirtualInputColumnCollection)

    {

    if (strFilterArray == null)

    {

    objDestinationRecordsetWrapper.SetUsageType(iDestInputID, objIDTSVirtualInput, objIDTSVirtualInputColumn.LineageID, DTSUsageType.UT_READONLY);

    }

    else

    {

    if (FilterField(objIDTSVirtualInputColumn.Name, strFilterArray) == false)

    objDestinationRecordsetWrapper.SetUsageType(iDestInputID, objIDTSVirtualInput, objIDTSVirtualInputColumn.LineageID, DTSUsageType.UT_READONLY);

    }

    }

    // Sets the connection

    objDestinationRecordsetWrapper.AcquireConnections(null);

    // Reinitializes the Source Metadata.

    objDestinationRecordsetWrapper.ReinitializeMetaData();

    // Releases the Wrapper connection.

    objDestinationRecordsetWrapper.ReleaseConnections();

    //Executes the Package

    Execute_Package();

    //Read records.

    objDataTable = new DataTable();

    try

    {

    ADODB.Recordset objRSRawData = (ADODB.Recordset)(_dataVariable.Value);

    foreach (Field _field in objRSRawData.Fields)

    {

    objDataTable.Columns.Add(_field.Name);

    }

    objRSRawData.MoveNext();

    iEOF = objRSRawData.RecordCount;

    if (iLimit < iEOF)

    {

    iRow += 1;

    if ((iEOF - iLimit) < 1000)

    iLimit = iEOF;

    else

    iLimit += 1000;

    // Load 1000 records per run.

    objDataTable = GetPaging(objDataTable, objRSRawData, ref iRow, ref iLimit);

    }

    }

    catch (Exception ex)

    {

    string str = ex.Message;

    }

    finally

    {}

    }

    return objDataTable;

    }

    #endregion

    #region Other Supporting Dataflow Methods

    //Gets the Paging DataTable with 1000 records.

    internal DataTable GetPaging(DataTable dtTemp, Recordset rsTemp, ref int iRow, ref int iLimit)

    {

    int RowCount = 1;

    while (!rsTemp.EOF)

    {

    if (RowCount == iRow && iRow != iLimit)

    {

    drRAWData = objDataTable.NewRow();

    for (int iRFCount = 0; iRFCount < rsTemp.Fields.Count; iRFCount++)

    {

    if (rsTemp.Fields[iRFCount].Name == "ErrorCode")

    {

    strValue = objIDTSDSTRecordsetMetaData.GetErrorDescription((int)rsTemp.Fields[iRFCount].Value);

    }

    else

    {

    strValue = rsTemp.Fields[iRFCount].Value.ToString();

    drRAWData[iRFCount] = strValue;

    }

    }

    objDataTable.Rows.Add(drRAWData);

    iRow++;

    }

    if (iRow == iLimit)

    {

    break;

    }

    else

    {

    RowCount++;

    rsTemp.MoveNext();

    }

    }

    return objDataTable;

    }

    //Returns true if Unchecked Field presents in strArray or false for Checked Fields

    internal bool FilterField(string strColName, string[] strArray)

    {

    bool bFilter = false;

    if (strArray != null || (strArray.ToString() != string.Empty))

    {

    foreach (string strComp in strArray)

    {

    if (strColName.Equals(strComp))

    {

    bFilter = true;

    }

    }

    }

    return bFilter;

    }

    //Clears the RAWColumnNames

    internal void ClearRAWColNames()

    {

    _strRAWColNames.Remove(0, _strRAWColNames.Length);

    }

     

    #endregion

    #endregion

    #region Executes the Package

    // Saves a new package

    internal void Execute_Package()

    {

    Microsoft.SqlServer.Dts.Runtime.Application app = new Microsoft.SqlServer.Dts.Runtime.Application();

    app.SaveToXml("C:\\Temp\\RawReader.dtsx", objPackage, null);

    Microsoft.SqlServer.Dts.Runtime.DTSExecResult result = objPackage.Execute();

    Trace.Write(result);

    }

    #endregion

    }

    }

    //=================================================

    // Calling Method to clsDataFlow

    //=================================================

    #region Loads RawData to DataGridView

    // Loads RAWData to DataGridView

    public void LoadGrid()

    {

    objDataFlow = new clsDataFlow();

    objDataFlow.strRAWFileName = strRAWFileName;

    objDataFlow.CreatePackage();

    objDataFlow.CreateSourceComponent(objDataFlow.strRAWFileName);

    for (int i = 0; i < chklbRAWFields.Items.Count; i++)

    {

    //Prepares string of UnChecked Items(Unwanted Columns) in chkListbox

    // To Display Grid without these columns.

    if (chklbRAWFields.GetItemChecked(i) == false)

    {

    strRAWColNames += chklbRAWFields.ItemsIdea.ToString() + ",";

    }

    }

    if (this.strRAWColNames != null && this.strRAWColNames.Length != 0)

    objDataFlow.strRAWColNames = this.strRAWColNames;

    else

    objDataFlow.ClearRAWColNames();

    dtLoadRawData = objDataFlow.CreateDestinationRecordSetComponent(ref iRow, ref iLimit, ref iEOF);

    if (dtLoadRawData != null)

    {

    dgvRAWData.Enabled = true;

    dgvRAWData.DataSource = dtLoadRawData;

    }

    if (iRow != iEOF)

    btnShowNext.Visible = true;

    else

    btnShowNext.Visible = false;

    }

    #endregion

    //=================================================

     

    Thanks again.

     

    Mit_2807

    Thursday, October 16, 2008 1:31 PM
  • Mit2807,

     

    i'm glad that you have a solution.  i'll take a look at your code when i have a little more free time. then, i'll let you know my impressions.

    Friday, October 17, 2008 6:03 AM
    Moderator
  • i didn't go through your code with a "fine tooth comb", but it looks ok.

     

    nothing peculiar jumped out at me.

     

    Monday, October 20, 2008 7:18 AM
    Moderator
  • Hi Duane,

     

    Actually speaking i am beginner to SSIS 2005 working on ETL Packages for DW and i wish to improve my edges to become good SQL Professional amongst the community. I am also pursuing for MCTS.

     

    Its my pleasure you spend time for a while to go through and in anyways if it can be updated for betterment of my knowledge then please let me know.

     

    Thanks.

     

     

     

    Tuesday, October 21, 2008 5:18 AM
  •  

    Hi Duane,

     

    I am glad to find another solution using DataReader as Destination for better performance and it worked for me.

     

    Here i am quoting a copy to my source to help the community of SSIS 2005.

     

    Can you please have a look and let me know if it anything more i can add or edit to make it better.

     

    Your comments are always welcome.

     

    //*************************************************************************************************************************

    // Description: SSIS 2005 Package Programming to move data from the RAWFile to DataGridView

    //                   using Data Reader as Destination

    //*************************************************************************************************************************

    #region Using Directives

    using System;

    using System.Text;

    using System.Collections.Generic;

    using Microsoft.SqlServer.Dts.Runtime;

    using Microsoft.SqlServer.Dts.Runtime.Wrapper;

    using Microsoft.SqlServer.Dts.Pipeline;

    using Microsoft.SqlServer.Dts.Pipeline.Wrapper;

    using Microsoft.SqlServer.Dts;

    using System.IO;

    using System.Data;

    using ADODB;

    #endregion

    namespace Utility

    {

    internal class clsDataFlow

    {

    #region Private Members

    //Declares Package,Pipe,Connection Members

    private Microsoft.SqlServer.Dts.Runtime.Package _objPackage = new Microsoft.SqlServer.Dts.Runtime.Package();

    private MainPipe _objMainPipe = null;

    private IDTSPath90 _objIDTSPath = null;

    private Microsoft.SqlServer.Dts.Runtime.Application app = null;

    //Declares Metadata Members

    private Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSComponentMetaData90 _objIDTSSRCMetaData = null;

    private Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSComponentMetaData90 _objIDTSDSTReaderMetaData = null;

    //Declares Wrapper Members

    private CManagedComponentWrapper _objSourceWrapper;

    private CManagedComponentWrapper _objDestinationReaderWrapper;

    //Declares Moniker & Constants

    private const string _strDataFlowTaskMoniker = "DTS.Pipeline.1";

    //Declares DataFlow Component Id

    private const string _strSourceDFComponentID = "{E2568105-9550-4F71-A638-B7FE42E66930}";

    private const string _strDestinationDFReaderComponentID = "{BF01D463-7089-41EE-8F05-0A6DC17CE633}";

    //Declares Other Memebers

    private string _strRAWFileName = string.Empty ;

    private string _strPKGFileName = string.Empty;

    private Microsoft.SqlServer.Dts.Runtime.Variable _dataVariable = null;

    private StringBuilder _strRAWColNames = new StringBuilder() ;

    private char _delim = ',';

    private DataRow _drRAWData = null;

    private string _strValue = null;

    private int _iDestInputID = 0;

    private string _strIndex = string.Empty;

    private int _iIndex = 0;

    private IDTSInput90 _objIDTSInput = null;

    private IDTSVirtualInput90 _objIDTSVirtualInput = null;

    private string[] _strFilterArray = null;

    private int _iRowCount = 1;

    private bool _bFilter = false;

    #endregion

    #region Constructor

    internal clsDataFlow()

    {

    //Constructor

    }

    #endregion

    #region Internal Members/Properties

    internal string strRAWFileName

    {

    get { return this._strRAWFileName; }

    set { this._strRAWFileName = value; }

    }

    internal string strPKGFileName

    {

    get { return this._strPKGFileName; }

    set { this._strPKGFileName = value; }

    }

    internal string strRAWColNames

    {

    get { return this._strRAWColNames.ToString(); }

    set { this._strRAWColNames.Append(value); }

    }

    #endregion

    #region Creates Package

    // Creates Runtime Package

    internal void CreatePackage()

    {

    _objPackage = new Microsoft.SqlServer.Dts.Runtime.Package();

    _objPackage.CreationDate = DateTime.Now;

    _objPackage.ProtectionLevel = Microsoft.SqlServer.Dts.Runtime.DTSProtectionLevel.DontSaveSensitive;

    _objPackage.Name = "RAWReader";

    _objPackage.Description = "RAW To Reader Conversion Package";

    _objPackage.DelayValidation = false;

    _objPackage.PackageType = Microsoft.SqlServer.Dts.Runtime.DTSPackageType.DTSDesigner90;

    _dataVariable = _objPackage.Variables.Add("DataRecords", false, "User", new System.Object());

    }

    #endregion

    #region Call Dataflow Component Methods

    #region Source And Destination Component Methods

    //Creates Source Component ( Output Collection )

    internal void CreateSourceComponent(string strRAWFilePath)

    {

    // Creates mainpipe for the executable component.

    _objMainPipe = ((Microsoft.SqlServer.Dts.Runtime.TaskHost)_objPackage.Executables.Add(_strDataFlowTaskMoniker)).InnerObject as MainPipe;

    // Adds a component from the MainPipe to the Source Metadata.

    _objIDTSSRCMetaData = _objMainPipe.ComponentMetaDataCollection.New();

    // Sets the source component class id.

    _objIDTSSRCMetaData.ComponentClassID = _strSourceDFComponentID;

    // Sets the locale property.

    _objIDTSSRCMetaData.LocaleID = -1;

    // Instantiates the Wrapper adding Source Metadata.

    _objSourceWrapper = _objIDTSSRCMetaData.Instantiate();

    // Provides default properties

    _objSourceWrapper.ProvideComponentProperties();

    // Sets RAWFile Component Property

    _objSourceWrapper.SetComponentProperty("AccessMode", 0);

    _objSourceWrapper.SetComponentProperty("FileName", strRAWFilePath);

    _objSourceWrapper.SetComponentProperty("FileNameVariable", null);

    // Sets the connection

    _objSourceWrapper.AcquireConnections(null);

    // Reinitializes the Source Metadata.

    _objSourceWrapper.ReinitializeMetaData();

    // Fetch ColumnNames for the MetaData

    if (_strRAWColNames.Length == 0 && _strRAWColNames.ToString() == string.Empty )

    {

    foreach (IDTSOutputColumn90 idtsOutPutColumn in _objIDTSSRCMetaData.OutputCollection[0].OutputColumnCollection)

    {

    _strRAWColNames.Append(idtsOutPutColumn.Name + ",");

    }

    }

    // Releases the Wrapper connection.

    _objSourceWrapper.ReleaseConnections();

    }

    //Creates Destination Component ( Input Collection )

    internal void CreateDestinationReaderComponent()

    {

    //1>DataReader String:The class name of the DataReader destination.

    //2>FailOnTimeout Boolean:Indicates whether to fail when a ReadTimeout occurs. The default value of this property is False.

    //3>ReadTimeout Integer:The number of milliseconds before a time-out occurs. The default value of this property is 30000 (30 seconds).

     

    // Adds a component from the MainPipe to the Destination Recordset Metadata.

    _objIDTSDSTReaderMetaData = _objMainPipe.ComponentMetaDataCollection.New();

    // Sets the Destination recordset component name

    _objIDTSDSTReaderMetaData.Name = "Test";

    // Sets the destination recordset component class id.

    _objIDTSDSTReaderMetaData.ComponentClassID = _strDestinationDFReaderComponentID;

    object o = new object();

    IDTSCustomProperty90 _property = _objIDTSDSTReaderMetaData.CustomPropertyCollection.New();

    _property.Name = "DataReader";

    _property.Value = o;

    _property = _objIDTSDSTReaderMetaData.CustomPropertyCollection.New();

    _property.Name = "FailOnTimeout";

    _property.Value = false;

    _property = _objIDTSDSTReaderMetaData.CustomPropertyCollection.New();

    _property.Name = "ReadTimeout";

    _property.Value = 30000;

    _property = _objIDTSDSTReaderMetaData.CustomPropertyCollection.New();

    _property.Name = "UserComponentTypeName";

    _property.Value = "Microsoft.SqlServer.Dts.Pipeline.DataReaderDestinationAdapter, Microsoft.SqlServer.DataReaderDest, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91";

     

    // Instantiates the Wrapper adding Destination Recordset Metadata.

    _objDestinationReaderWrapper = _objIDTSDSTReaderMetaData.Instantiate();

    // Provides default properties

    _objDestinationReaderWrapper.ProvideComponentProperties();

    // Sets the connection

    _objDestinationReaderWrapper.AcquireConnections(null);

    // Reinitializes the Source Metadata.

    _objDestinationReaderWrapper.ReinitializeMetaData();

    // Releases the Wrapper connection.

    _objDestinationReaderWrapper.ReleaseConnections();

    // Creates the IDTSPath from the Object MainPipe.

    _objIDTSPath = _objMainPipe.PathCollection.New();

     

    _objIDTSPath.AttachPathAndPropagateNotifications(_objIDTSSRCMetaData.OutputCollection[0],

    _objIDTSDSTReaderMetaData.InputCollection[0]);

    _objIDTSInput = _objIDTSDSTReaderMetaData.InputCollection[0];

    // Gets the Virtual Input Column Collection from the Destination InputCollection

    _objIDTSVirtualInput = _objIDTSInput.GetVirtualInput();

    _iDestInputID = Convert.ToInt32(_objIDTSInput.ID);

    //Splits the RAW Columnnames in an array of string

    if (strRAWColNames != null && strRAWColNames.Equals(string.Empty) == false && strRAWColNames != "")

    {

    if (strRAWColNames.EndsWith(","))

    {

    _iIndex = strRAWColNames.LastIndexOf(",");

    _strIndex = strRAWColNames.Remove(_iIndex);

    }

    _strFilterArray = _strIndex.Split(_delim);

    }

    //Set Usagetype According to FilterArray

    foreach (IDTSVirtualInputColumn90 objIDTSVirtualInputColumn in _objIDTSVirtualInput.VirtualInputColumnCollection)

    {

    if (_strFilterArray == null)

    {

    // When FilterArray string is null

    _objDestinationReaderWrapper.SetUsageType(_iDestInputID, _objIDTSVirtualInput, objIDTSVirtualInputColumn.LineageID, DTSUsageType.UT_READONLY);

    }

    else

    {

    // When FilterArray string is not null

    if (FilterField(objIDTSVirtualInputColumn.Name, _strFilterArray) == false)

    _objDestinationReaderWrapper.SetUsageType(_iDestInputID, _objIDTSVirtualInput, objIDTSVirtualInputColumn.LineageID, DTSUsageType.UT_READONLY);

    }

    }

    // Sets the connection

    _objDestinationReaderWrapper.AcquireConnections(null);

    // Reinitializes the Source Metadata.

    _objDestinationReaderWrapper.ReinitializeMetaData();

    // Releases the Wrapper connection.

    _objDestinationReaderWrapper.ReleaseConnections();

    }

    #endregion

    #region Other Supporting Dataflow Methods

    //Returns true if Unchecked Field presents in strArray or false for Checked Fields

    internal bool FilterField(string strDestInputColumnName, string[] strArrUncheckedColumns)

    {

    if (strArrUncheckedColumns != null || (strArrUncheckedColumns.ToString() != string.Empty))

    {

    foreach (string strUncheckedCol in strArrUncheckedColumns)

    {

    if (strDestInputColumnName.Equals(strUncheckedCol))

    {

    //When match found for filter

    _bFilter = true;

    }

    }

    }

    return _bFilter;

    }

    //Clears the RAWColumnNames

    internal void ClearRawColumnNames()

    {

    //Removes stringbuilder object value

    _strRAWColNames.Remove(0, _strRAWColNames.Length);

    }

    #endregion

    #endregion

    #region Save Package

    // Saves a new package

    internal void Save_Package()

    {

    //Creates DTS Runtime Application's instance

    app = new Microsoft.SqlServer.Dts.Runtime.Application();

    //Save DTSX File to a local path

    app.SaveToXml("C:\\Temp\\RawReader.dtsx", _objPackage, null);

    }

    #endregion

    }

    }

     

    //*************************************************************************************************************************

    // Description: Calling Method to clsDataFlow

    //*************************************************************************************************************************

    #region BindDataGridView

    // Loads RAWData to DataGridView

    internal void BindDataGridView()

    {

    _objDataFlow = new clsDataFlow();

    _objDataFlow.strRAWFileName = strRAWFileName;

    //Creates Package for DataFlow

    _objDataFlow.CreatePackage();

    //Creates Source Component - DataFlow

    _objDataFlow.CreateSourceComponent(_objDataFlow.strRAWFileName);

    for (int iField = 0; iField < chklbRAWFields.Items.Count; iField++)

    {

    // To view grid with only checked items(Filtering Unchecked)

    if (chklbRAWFields.GetItemChecked(iField) == false)

    {

    strRAWColNames += chklbRAWFields.Items[iField].ToString() + ",";

    }

    }

    if (this.strRAWColNames != null && this.strRAWColNames.Length != 0)

    _objDataFlow.strRAWColNames = this.strRAWColNames;

    else

    _objDataFlow.ClearRawColumnNames();

    //Creates Destination Component - DataFlow

    _objDataFlow.CreateDestinationReaderComponent();

    _objDataFlow.Save_Package();

    DataSet dsGridView = GetGridViewData();

    if (dsGridView!= null)

    {

    dgvRAWData.Enabled = true;

    dgvRAWData.DataSource = dsGridView; //.Tables[0].DefaultView

    dgvRAWData.DataMember = dsGridView.Tables[0].TableName;

    }

    }

    #endregion

     

     

    #region GetGridViewData

    internal DataSet GetGridViewData()

    {

    string dtexecArgs;

    string dataReaderName;

    DtsConnection dtsConnection;

    DtsCommand dtsCommand;

    DataSet dsPackageData = new DataSet();

    IDataReader dtsDataReader ;

    dtexecArgs = "-f \"C:\\Temp\\RawReader.dtsx\"";

    dataReaderName = "DataReaderDest";

    dtsConnection = new DtsConnection();

    dtsConnection.ConnectionString = dtexecArgs;

    dtsCommand = new DtsCommand(dtsConnection);

    dtsCommand.CommandText = dataReaderName;

    dtsConnection.Open();

    dtsDataReader = dtsCommand.ExecuteReader(CommandBehavior.Default);

    dsPackageData.Load(dtsDataReader, LoadOption.OverwriteChanges, dtsDataReader.GetSchemaTable().TableName);

    try

    {

    if (dtsDataReader != null)

    {

    dtsDataReader.Close();

    }

    }

    catch (Exception ex)

    {

    MessageBox.Show(("Exception closing DataReader:"

    + (ControlChars.CrLf

    + (ex.Message

    + (ControlChars.CrLf + ex.InnerException.Message)))), "Exception closing DataReader", MessageBoxButtons.OK, MessageBoxIcon.Error);

    }

    try

    {

    if ((dtsConnection.State != ConnectionState.Closed))

    {

    dtsConnection.Close();

    }

    }

    catch (Exception ex)

    {

    MessageBox.Show(("Exception closing connection:"

    + (ControlChars.CrLf

    + (ex.Message

    + (ControlChars.CrLf + ex.InnerException.Message)))), "Exception closing connection", MessageBoxButtons.OK, MessageBoxIcon.Error);

    }

    return dsPackageData;

    }

    #endregion

     

    Thanks.

    Thursday, October 23, 2008 7:06 AM