February 5, 2008 at 8:25 pm
Hi all
I m trying to transfer my csv file into datbase ..
and i m getting an error on managedOleInstance.AcquireConnections(null);
here is my code can anyone help to solve this !
Thank you so much !!!
using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.SqlServer.Dts.Runtime;
using PipeLineWrapper = Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using RuntimeWrapper = Microsoft.SqlServer.Dts.Runtime.Wrapper;
using System.Diagnostics;
namespace Ver1_H1
{
class ImportPackage
{
public const string fileName = @"c:\dts.dtsx";
public const string flatFileName = @"C:\Projects\K2 Development\Sample.csv";
public const string FlatFileMoniker = @"FLATFILE";
public const string OleDBMoniker = @"OLEDB";
public const string ConnectionString = @"Data Source=SSIBRD14;Initial Catalog=Indian Test;Integrated Security=SSPI;Provider=SQLNCLI;Auto Translate=false;";
public const string DatabaseName = "ImportDatabase";
public const string TableName = "DataTable";
private const string dataFlowTaskMoniker = "DTS.Pipeline.1";
public const string SourceDataFlowComponentID = "{90C7770B-DE7C-435E-880E-E718C92C0573}";
// The data flow component id
private const string DataConversionDataflowComponentID = "{C3BF62C8-7C5C-4F85-83C3-E0B6F6BE267C}";
// the compoenent ID
public const string OleDBDestinationDataFlowComponentID = "{E2568105-9550-4F71-A638-B7FE42E66922}";
private Package package;
private ConnectionManager flatFileConnectionManager;
private ConnectionManager destinationDatabaseConnectionManager;
private Executable dataflowTask;
List ();
Dictionary ();
public ImportPackage()
{
derivedLineageIdentifiers.Clear();
outputColumnLineageIDs.Clear();
srcColumns.Clear();
srcColumns.Add("\"Name\"");
srcColumns.Add("\"Age\"");
srcColumns.Add("\"JoinDate\"");
srcColumns.Add("\"Salary\"");
srcColumns.Add("\"Retired\"");
//Microsoft.SqlServer.Dts.Runtime.Wrapper.compo ComponentMetaData.get
}
private Column GetTargetColumnInfo(string sourceColumnName)
{
Column cl = new Column();
if (sourceColumnName.Contains("Name"))
{
cl.Name = "Name";
cl.DataType = Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_STR;
cl.Precision = 0;
cl.Scale = 0;
cl.Length = 255;
cl.CodePage = 1252;
}
else if (sourceColumnName.Contains("Age"))
{
cl.Name = "Age";
cl.DataType = Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_I4;
cl.Precision = 0;
cl.Scale = 0;
cl.Length = 0;
}
else if (sourceColumnName.Contains("JoinDate"))
{
cl.Name = "JoinDate";
cl.DataType = Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_DATE;
cl.Precision = 0;
cl.Scale = 0;
cl.Length = 0;
}
else if (sourceColumnName.Contains("Salary"))
{
cl.Name = "Salary";
cl.DataType = Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_NUMERIC;
cl.Precision = 6;
cl.Scale = 3;
cl.Length = 0;
}
else if (sourceColumnName.Contains("Retired"))
{
cl.Name = "Retired";
cl.DataType = Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_BOOL;
cl.Precision = 0;
cl.Scale = 0;
cl.Length = 0;
}
return cl;
}
public void Import()
{
CreatePackage();
CreateFlatFileConnection();
CreateDestinationDatabaseConnection();
CreateDataFlowTask();
Application app = new Application();
app.SaveToSqlServer(package, null, "SSIBRD14", null, null);
}
private void CreatePackage()
{
package = new Package();
package.CreationDate = DateTime.Now;
package.ProtectionLevel = DTSProtectionLevel.DontSaveSensitive;
package.Name = "csvIMPORT";
package.Description = "A simple package";
package.DelayValidation = true;
package.PackageType = Microsoft.SqlServer.Dts.Runtime.DTSPackageType.DTSDesigner90;
}
private object GetSourceColumnLineageID(string p)
{
if (outputColumnLineageIDs.ContainsKey(p))
{
return outputColumnLineageIDs[p];
}
else
{
return outputColumnLineageIDs["\"" + p + "\""];
}
}
private Dictionary ();
private void CreateFlatFileConnection()
{
flatFileConnectionManager = package.Connections.Add(FlatFileMoniker);
flatFileConnectionManager.ConnectionString = flatFileName;
flatFileConnectionManager.Name = "SSIS Connection Manager for Files";
flatFileConnectionManager.Description = string.Concat("SSIS Connection Manager");
// Setting some common properties of the connection manager object
flatFileConnectionManager.Properties["ColumnNamesInFirstDataRow"].SetValue(flatFileConnectionManager, true);
flatFileConnectionManager.Properties["Format"].SetValue(flatFileConnectionManager, "Delimited");
flatFileConnectionManager.Properties["HeaderRowDelimiter"].SetValue(flatFileConnectionManager, "\r");
// If user has been specified a text qualifier then put it into the connection string property
flatFileConnectionManager.Properties["TextQualifier"].SetValue(flatFileConnectionManager, "\"");
// create the source columns into the connection manager
CreateSourceColumns();
}
private void CreateSourceColumns()
{
// get the actuall connection manger instance
RuntimeWrapper.IDTSConnectionManagerFlatFile90 flatFileConnection =
flatFileConnectionManager.InnerObject as RuntimeWrapper.IDTSConnectionManagerFlatFile90;
RuntimeWrapper.IDTSConnectionManagerFlatFileColumn90 column;
RuntimeWrapper.IDTSName90 name;
// trace the current count
Debug.WriteLine(flatFileConnection.Columns.Count);
foreach (String colName in srcColumns)
{ // iterate
// now create a new column for the connection manager
column = flatFileConnection.Columns.Add(); // if this is the last row
if (srcColumns.IndexOf(colName) == (srcColumns.Count - 1))
column.ColumnDelimiter = "\r";
// add the row delimiter
else
column.ColumnDelimiter = ",";
name = (RuntimeWrapper.IDTSName90)column;
name.Name = colName.Replace("\"", "");
column.TextQualified = true;
column.ColumnType = "Delimited";
column.DataType = Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_STR;
column.ColumnWidth = 0;
column.MaximumWidth = 255;
column.DataPrecision = 0;
column.DataScale = 0;
}
}
private void CreateDestinationDatabaseConnection()
{
// Creating a connection using the oledb moniker
destinationDatabaseConnectionManager = package.Connections.Add(OleDBMoniker);
destinationDatabaseConnectionManager.ConnectionString = ConnectionString;
destinationDatabaseConnectionManager.Name = "SSIS Connection Manager for Oledb";
destinationDatabaseConnectionManager.Description = string.Concat("SSIS Connection Manager for ");
}
private PipeLineWrapper.IDTSComponentMetaData90 CreateSourceFileReaderTask()
{
PipeLineWrapper.IDTSComponentMetaData90 sourceComponent =
((dataflowTask as TaskHost).InnerObject as PipeLineWrapper.MainPipe).ComponentMetaDataCollection.New();
sourceComponent.Name = "Source File Component";
sourceComponent.ComponentClassID = SourceDataFlowComponentID;
return sourceComponent;
}
private void CreateDataFlowTask()
{
dataflowTask = package.Executables.Add(dataFlowTaskMoniker);
PipeLineWrapper.IDTSComponentMetaData90 sourceComponent = CreateSourceFileReaderTask();
ConfigureSourceComponent(sourceComponent);
PipeLineWrapper.IDTSComponentMetaData90 dataconversionComponent = CreateDataConversionTask();
PipeLineWrapper.IDTSPath90 path = ((dataflowTask as TaskHost).InnerObject as PipeLineWrapper.MainPipe).PathCollection.New();
path.AttachPathAndPropagateNotifications(
sourceComponent.OutputCollection[0],
dataconversionComponent.InputCollection[0]);
ConfigureDataConversionComponent(dataconversionComponent);
PipeLineWrapper.IDTSComponentMetaData90 destinationComponent = CreateDataDestinationComponent();
PipeLineWrapper.IDTSPath90 pathDD = ((dataflowTask as TaskHost).InnerObject as PipeLineWrapper.MainPipe).PathCollection.New();
pathDD.AttachPathAndPropagateNotifications(
dataconversionComponent.OutputCollection[0],
destinationComponent.InputCollection[0]);
ConfigureDataDestinationComponent(destinationComponent);
}
private PipeLineWrapper.CManagedComponentWrapper managedOleInstanceDataConversionComponent;
///
///
///
///
private Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSComponentMetaData90 CreateDataConversionTask()
{
PipeLineWrapper.IDTSComponentMetaData90 dataconversionComponent =
((dataflowTask as TaskHost).InnerObject as PipeLineWrapper.MainPipe).ComponentMetaDataCollection.New();
dataconversionComponent.Name = "Data conversion Component";
dataconversionComponent.ComponentClassID = DataConversionDataflowComponentID;
managedOleInstanceDataConversionComponent = dataconversionComponent.Instantiate();
managedOleInstanceDataConversionComponent.ProvideComponentProperties();
dataconversionComponent.InputCollection[0].ExternalMetadataColumnCollection.IsUsed = true;
dataconversionComponent.InputCollection[0].HasSideEffects = false;
return dataconversionComponent;
}
private void ConfigureSourceComponent(Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSComponentMetaData90 sourceComponent)
{
// load the COM for the given GUID
PipeLineWrapper.CManagedComponentWrapper managedFlatFileInstance = sourceComponent.Instantiate();
// get the populate the properties
managedFlatFileInstance.ProvideComponentProperties();
// putting the connection
if (sourceComponent.RuntimeConnectionCollection.Count > 0)
{ // If connection is necessary
sourceComponent.RuntimeConnectionCollection[0].ConnectionManagerID =
flatFileConnectionManager.ID;
sourceComponent.RuntimeConnectionCollection[0].ConnectionManager =
DtsConvert.ToConnectionManager90(flatFileConnectionManager);
}
// establish a connection
// managedFlatFileInstance.AcquireConnections(null);
// Initialize the metadata
// managedFlatFileInstance.ReinitializeMetaData();
// create the mapping now
PipeLineWrapper.IDTSExternalMetadataColumn90 exOutColumn;
foreach (PipeLineWrapper.IDTSOutputColumn90 outColumn in
sourceComponent.OutputCollection[0].OutputColumnCollection)
{ // create the MAP
exOutColumn =
sourceComponent.OutputCollection[0].ExternalMetadataColumnCollection[outColumn.Name];
// map it
managedFlatFileInstance.MapOutputColumn(
sourceComponent.OutputCollection[0].ID, outColumn.ID, exOutColumn.ID, true);
outputColumnLineageIDs.Add(outColumn.Name, outColumn.ID);
}
// Release the connection now
managedFlatFileInstance.ReleaseConnections();
}
private void ConfigureDataConversionComponent(Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSComponentMetaData90 dataconversionComponent)
{
PipeLineWrapper.CManagedComponentWrapper managedOleInstance = managedOleInstanceDataConversionComponent;
// Get the derived's default input and virtual input.
PipeLineWrapper.IDTSInput90 input = dataconversionComponent.InputCollection[0];
PipeLineWrapper.IDTSVirtualInput90 vInput = input.GetVirtualInput();
// Iterate through the virtual input column collection.
foreach (PipeLineWrapper.IDTSVirtualInputColumn90 vColumn in vInput.VirtualInputColumnCollection)
{
managedOleInstance.SetUsageType(
input.ID, vInput, vColumn.LineageID, PipeLineWrapper.DTSUsageType.UT_READONLY);
}
// putting the truncation row disposition
dataconversionComponent.OutputCollection[0].TruncationRowDisposition =
PipeLineWrapper.DTSRowDisposition.RD_NotUsed;
// putting the error row disposition
dataconversionComponent.OutputCollection[0].ErrorRowDisposition =
PipeLineWrapper.DTSRowDisposition.RD_NotUsed;
// get the output column collection reference
PipeLineWrapper.IDTSOutput90 output = dataconversionComponent.OutputCollection[0];
foreach (PipeLineWrapper.IDTSInputColumn90 inColumn in
dataconversionComponent.InputCollection[0].InputColumnCollection)
{ // create the map
// get the target column from the mapping informations
PipeLineWrapper.IDTSOutputColumn90 outputColumn =
dataconversionComponent.OutputCollection[0].OutputColumnCollection.New();
outputColumn.Name = inColumn.Name;
Column targetColumn = GetTargetColumnInfo(inColumn.Name);
// find out the length of the column
int length = targetColumn.Length;
// get the precision of the target column
int precision = targetColumn.Precision;
// get the scale of the target column
int scale = targetColumn.Scale;
// get the SSSIS complaint datatype from the given mappings
Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType dataType = targetColumn.DataType;
// setting the data properties
outputColumn.SetDataTypeProperties
(dataType, length, precision, scale, targetColumn.CodePage);
// putting the external metadata column id to zero
outputColumn.ExternalMetadataColumnID = 0;
outputColumn.ErrorRowDisposition = PipeLineWrapper.DTSRowDisposition.RD_RedirectRow;
outputColumn.TruncationRowDisposition = PipeLineWrapper.DTSRowDisposition.RD_RedirectRow;
PipeLineWrapper.IDTSCustomProperty90 property = outputColumn.CustomPropertyCollection.New();
property.Name = "SourceInputColumnLineageID";
property.Value = GetSourceColumnLineageID(targetColumn.Name);
property = outputColumn.CustomPropertyCollection.New();
property.Name = "FastParse";
property.Value = false;
// Now we are preserving the Lineage id into a list.
// you know, when later we will configure the dataflowcomponent of SQL destination
// then, we will find all the inputs (the input came from flat file and the inputs
// came from the derived columns output). And we need to distinguish among them.
// we will only consider those inputs into the data destination component, where the
// inputs are came from the out put of derived column component. which is actually here.
derivedLineageIdentifiers[outputColumn.LineageID] = outputColumn.Name;
}
}
private PipeLineWrapper.CManagedComponentWrapper managedOleInstanceDestinationComponent;
///
///
///
///
private Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSComponentMetaData90 CreateDataDestinationComponent()
{
PipeLineWrapper.IDTSComponentMetaData90 datadestinationComponent =
((dataflowTask as TaskHost).InnerObject as PipeLineWrapper.MainPipe).ComponentMetaDataCollection.New();
datadestinationComponent.Name = "Data Destination Component";
datadestinationComponent.ComponentClassID = OleDBDestinationDataFlowComponentID;
// get the COM instance
managedOleInstanceDestinationComponent = datadestinationComponent.Instantiate();
// populate the properties
managedOleInstanceDestinationComponent.ProvideComponentProperties();
return datadestinationComponent;
}
private PipeLineWrapper.CManagedComponentWrapper managedOleInstanceErrorComponent;
///
///
///
///
private void ConfigureDataDestinationComponent(Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSComponentMetaData90 destinationComponent)
{
// get the COM instance
PipeLineWrapper.CManagedComponentWrapper managedOleInstance = managedOleInstanceDestinationComponent;
// setting the connection
if (destinationComponent.RuntimeConnectionCollection.Count > 0)
{ // If connection is necessary
destinationComponent.RuntimeConnectionCollection[0].ConnectionManagerID =
destinationDatabaseConnectionManager.ID;
destinationComponent.RuntimeConnectionCollection[0].ConnectionManager =
DtsConvert.ToConnectionManager90(destinationDatabaseConnectionManager);
}
// Set the custom properties.
managedOleInstance.SetComponentProperty("AccessMode", 0); // Table of View mode
managedOleInstance.SetComponentProperty("AlwaysUseDefaultCodePage", false); // Default Codepage
managedOleInstance.SetComponentProperty("DefaultCodePage", 1252); // Set it
managedOleInstance.SetComponentProperty("FastLoadKeepIdentity", false); // Fast load
managedOleInstance.SetComponentProperty("FastLoadKeepNulls", false);
managedOleInstance.SetComponentProperty("FastLoadMaxInsertCommitSize", 0);
managedOleInstance.SetComponentProperty("FastLoadOptions", "TABLOCK,CHECK_CONSTRAINTS");
managedOleInstance.SetComponentProperty("OpenRowset",
string.Format("[{0}].[dbo].[{1}]", DatabaseName, TableName));
// Now activate a connection and create the mappings
// =========================================================================
// Establish a connection
// managedOleInstance.AcquireConnections(null);
// initialize the metadata
// managedOleInstance.ReinitializeMetaData();
// Get the destination's default input and virtual input.
PipeLineWrapper.IDTSInput90 input = destinationComponent.InputCollection[0];
PipeLineWrapper.IDTSVirtualInput90 vInput = input.GetVirtualInput();
// Iterate through the virtual input column collection.
foreach (PipeLineWrapper.IDTSVirtualInputColumn90 vColumn in vInput.VirtualInputColumnCollection)
{
// Remember, we will find all columns here into the 'vInput.VirtualInputColumnCollection'
// Which is, if the total columns count into the flat file is 6 then here you will
// get 12 ( 6 * 2 ) columns into the 'vInput.VirtualInputColumnCollection'. Becoz,
// the data derived column usually provides all the inputs as its outputs along with the out
// puts that it really creates. And here we need to consider only those inputs which came
// from the derived column component (not form the flat file source component).
// how can we do that? we can do this by checking the lineageid that we did populate during
// the derived column creation process.
if (derivedLineageIdentifiers.ContainsKey(vColumn.LineageID))
{ // if the column came from the derived column dataflow component
managedOleInstance.SetUsageType(
input.ID, vInput, vColumn.LineageID, PipeLineWrapper.DTSUsageType.UT_READONLY);
}
}
PipeLineWrapper.IDTSExternalMetadataColumn90 exColumn;
foreach (PipeLineWrapper.IDTSInputColumn90 inColumn in
destinationComponent.InputCollection[0].InputColumnCollection)
{ // create the map
exColumn = destinationComponent.InputCollection[0].ExternalMetadataColumnCollection[inColumn.Name.Replace("\"", "")];
//exColumn = destinationComponent.InputCollection[0].ExternalMetadataColumnCollection[inColumn.Name];
// our mapped column
Column mappedColumn = GetTargetColumnInfo(exColumn.Name);
string destName = mappedColumn.Name;
// setting the new name
exColumn.Name = destName;
// setting the datatype
exColumn.DataType = mappedColumn.DataType;
// creating the mapping
managedOleInstance.MapInputColumn(destinationComponent.InputCollection[0].ID,
inColumn.ID, exColumn.ID);
}
// Now release the connection
managedOleInstance.ReleaseConnections();
}
}
}
July 7, 2008 at 8:51 am
Did you have any luck bud? I am having similar problems
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply