August 18, 2008 at 1:28 am
hi guys
i am using ssis.in that my source is OLEDB source and destination is integer i have to convert it.is it possible to convert data type using data conversion.for my knowledge in data conversion chaged data type column created add another column.but i want change same column.
example:my source column is primary key(string)
my destination column is primary key(integer)
dont want in destination like this pirmary key(string) primary key1(integer)
and what is the main use of data converstion transforamtion
August 18, 2008 at 1:36 am
No. SSIS does not allow for meta data changes at runtime. i.e. you cannot to inplace conversions like that.
You must get the source to do it. It is faster, better, cheaper on memory and resources than getting SSIS to do it.
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
August 18, 2008 at 1:37 am
GGAAAARRRRRRRRRR Why the double post
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
January 12, 2010 at 1:38 am
how do we get the source to do it?:unsure:
January 12, 2010 at 1:45 am
Change your source from a table or view to a query and perform a CAST there ...
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
January 12, 2010 at 3:34 am
Thanks for the information. but i'm creating a package programmatically. Before mapping the source tom destination columns i need to cast appropriate columns. I've posted my problem in this site.
January 12, 2010 at 4:04 am
susanaw007 (1/12/2010)
Thanks for the information. but i'm creating a package programmatically....
I don't see why that precludes the use of a query for the source, but I guess you have your reasons.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
January 12, 2010 at 4:31 am
My source is currently Excel Source(excel file which i don't want to change). I would really appreciate if you would help me by modifying in the code below
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SqlServer.Dts.Runtime;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using System.Data.SqlClient;
using System.Xml;
using System.Data.OleDb;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Runtime.InteropServices;
using System.Xml.Linq;
using System.Runtime.InteropServices;
using System.ComponentModel;
using System.Globalization;
using System.Reflection;
using System.Data.SqlClient;
using System.IO;
using Microsoft.SqlServer.Dts.Runtime;
using PipeLineWrapper = Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using RuntimeWrapper = Microsoft.SqlServer.Dts.Runtime.Wrapper;
using MSExcel = Microsoft.Office.Interop.Excel;
using Microsoft.SqlServer.Dts.Pipeline;
/// <summary>
/// Summary description for CreateAndExecutePackage
/// </summary>
public class CreateAndExecutePackage
{
//Check column names from the template.
//DataSet dsData = ds.Clone();
public static readonly string ExcelConnectionString = ConfigurationManager.AppSettings["ExcelConn"].ToString();
//Database Table
DataTable dtDBTable;
DataTable dtSetDataTypeProperties = new DataTable();
public DataTable getExcelFields()
{
//DataSet Excel Source
//ArrayList arrExcelFields = new ArrayList();
int iRow = 0;
DataTable dtExcelFields= new DataTable();
dtExcelFields.Columns.Add("ExcelFields", typeof(string));
dtExcelFields.Columns.Add("DataType", typeof(string));
DataSet dsTemplate = GetExcelData(@"D:\iPlan\Site work\SampleExcelFiles\India.xls");
DataTable dt = dsTemplate.Tables[0];
foreach (DataColumn dc in dt.Columns)
{
dtExcelFields.Rows.Add();
dtExcelFields.Rows[iRow]["ExcelFields"] = dc.ColumnName;
dtExcelFields.Rows[iRow]["DataType"] = dc.DataType;
//arrExcelFields.Add(dc.ColumnName);
iRow = iRow + 1;
}
return dtExcelFields;
}
public DataTable getDataTableFields()
{
int iRow = 0;
DataTable dtDBFields = new DataTable();
dtDBFields.Columns.Add("DBFields", typeof(string));
dtDBFields.Columns.Add("DataType", typeof(string));
DataTable dt = GetDBColumnNames();
foreach (DataColumn dc in dt.Columns)
{
dtDBFields.Rows.Add();
dtDBFields.Rows[iRow]["DBFields"] = dc.ColumnName;
dtDBFields.Rows[iRow]["DataType"] = dc.DataType;
iRow = iRow + 1;
}
return dtDBFields;
}
public void CreateSaveExecutePackage(DataTable dtMappedExcelToDB,DataTable dtExcelFields, DataTable dtDBfields)
{
DataTable dtGetSourceTblWithDestTblColumnNames = new DataTable();
try
{
Microsoft.SqlServer.Dts.Runtime.Application app = new Microsoft.SqlServer.Dts.Runtime.Application();
//Microsoft.SqlServer.Dts.Runtime.Application app = new Microsoft.SqlServer.Dts.Runtime.Application();
//To Create a package named [Sample Package]
Package package = new Package();
package.Name = "Sample Package";
package.PackageType = DTSPackageType.DTSDesigner90;
package.VersionBuild = 1;
//To add package variables
Variable RowCountVar = package.Variables.Add("RowCountVar", false, "User", 0);
RowCountVar.Name = @"RowCountVar";
Variable TransactionID = package.Variables.Add("TransactionID", false, "User", 0);
TransactionID.Name = @"TransactionID";
Variable CurrentExcelPath = package.Variables.Add("CurrentExcelPath", false, "User", @"D:\iPlan\Site work\SampleExcelFiles\India");
CurrentExcelPath.Name = @"CurrentExcelPath";
//DataType will be automatically set to String
//For source database (Excel)
ConnectionManager ExcelSource = package.Connections.Add("EXCEL");
ExcelSource.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=+ @[User::CurrentExcelPath] +;Extended Properties=\""EXCEL 8.0;HDR=YES;IMEX=1\"";";
ExcelSource.Name = "Excel Connection Manager";
ExcelSource.SetExpression("ConnectionString", @"""Provider=Microsoft.Jet.OLEDB.4.0;Data Source="" + @[User::CurrentExcelPath] + "";Extended Properties=\""Excel 8.0;HDR=Yes;IMEX=1\""""");
ExcelSource.DelayValidation = true;
//For destination database (OLAP)SQLNCLI10 SQLNCLI10 SQLNCLI.1
ConnectionManager OLAP = package.Connections.Add("OLEDB");
OLAP.ConnectionString = ConfigurationManager.AppSettings["AdventureWorksConnectionString"].ToString();
OLAP.Name = "LocalHost.OLAP";
//To add "Excel Sheet Iterator" (ForEach Loop container)
ForEachLoop ForEachLoopContainer = (ForEachLoop)package.Executables.Add("STOCK:FOREACHLOOP");
ForEachLoopContainer.Name = @"Excel Sheet Iterator";
ForEachLoopContainer.Description = @"Foreach Loop Container";
//To Set Foreach loop container collection information
ForEachEnumeratorInfo FileEnumeratorInfo = app.ForEachEnumeratorInfos["Foreach File Enumerator"];
ForEachEnumeratorHost FileEnumeratorHost = FileEnumeratorInfo.CreateNew();
FileEnumeratorHost.CollectionEnumerator = false;
//FileEnumeratorHost.Properties["Directory"].SetValue(FileEnumeratorHost, @"C:\Site work\SampleExcelFiles");
FileEnumeratorHost.Properties["Directory"].SetValue(FileEnumeratorHost, @"D:\iPlan\Site work\SampleExcelFiles");
FileEnumeratorHost.Properties["FileSpec"].SetValue(FileEnumeratorHost, @"*.xls*");
//0 = "Fully qualified" ; 1 = "Name and extension" ; 2 = "Name only"
FileEnumeratorHost.Properties["FileNameRetrieval"].SetValue(FileEnumeratorHost, 0);
FileEnumeratorHost.Properties["Recurse"].SetValue(FileEnumeratorHost, "False");
ForEachLoopContainer.ForEachEnumerator = FileEnumeratorHost;
//To Set Foreach loop container variable mappings
ForEachVariableMapping CurrenFileVariableMapping = ForEachLoopContainer.VariableMappings.Add();
CurrenFileVariableMapping.VariableName = @"User::CurrentExcelPath";
CurrenFileVariableMapping.ValueIndex = 0;
//To add Sales inside Foreach loop container (Data Flow Task)
TaskHost dataFlowTaskHost = (TaskHost)ForEachLoopContainer.Executables.Add("DTS.Pipeline.1");
dataFlowTaskHost.Name = @"Role";
dataFlowTaskHost.FailPackageOnFailure = true;
dataFlowTaskHost.FailParentOnFailure = true;
//dataFlowTaskHost.DelayValidation = false;
dataFlowTaskHost.DelayValidation = true;
dataFlowTaskHost.Description = @"Data Flow Task";
dataFlowTaskHost.TransactionOption = DTSTransactionOption.NotSupported;
//-----------Data Flow Inner component starts----------------
MainPipe dataFlowTask = dataFlowTaskHost.InnerObject as MainPipe;
// Source OLE DB connection manager to the package.
ConnectionManager SconMgr = package.Connections["Excel Connection Manager"];
// Destination OLE DB connection manager to the package.
ConnectionManager DconMgr = package.Connections["LocalHost.OLAP"];
// Create and configure an Excel source component.
IDTSComponentMetaData90 source = dataFlowTask.ComponentMetaDataCollection.New();
source.ComponentClassID = "DTSAdapter.ExcelSource.1";
// Create the design-time instance of the source.
CManagedComponentWrapper srcDesignTime = source.Instantiate();
// The ProvideComponentProperties method creates a default output.
srcDesignTime.ProvideComponentProperties();
source.Name = "Excel Source (Role)";
// Assign the connection manager.
source.RuntimeConnectionCollection[0].ConnectionManagerID = SconMgr.ID;
source.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(SconMgr);
// Set the custom properties of the source.
srcDesignTime.SetComponentProperty("AccessMode", 0); // Mode 0 : OpenRowset / Table - View
srcDesignTime.SetComponentProperty("OpenRowset", "Sheet1$");
//srcDesignTime.SetComponentProperty("OpenRowset", GetSheetNames());
// Connect to the data source, and then update the metadata for the source.
srcDesignTime.AcquireConnections(null);
srcDesignTime.ReinitializeMetaData();
srcDesignTime.ReleaseConnections();
////addded
//Open Excel Connection
////Add an Row Count to the data flow.
IDTSComponentMetaData90 RowCountComponent = dataFlowTask.ComponentMetaDataCollection.New();
RowCountComponent.Name = "Row Count";
RowCountComponent.ComponentClassID = "DTSTransform.RowCount.1"; //Public Token is: "{DE50D3C7-41AF-4804-9247-CF1DEB147971}";
CManagedComponentWrapper rowCountDesignTime = RowCountComponent.Instantiate();
rowCountDesignTime.ProvideComponentProperties();
rowCountDesignTime.SetComponentProperty("VariableName", "RowCountVar");
rowCountDesignTime.AcquireConnections(null);
rowCountDesignTime.ReinitializeMetaData();
rowCountDesignTime.ReleaseConnections();
//// Create the path from source to Row Count Transformation.
IDTSPath90 pathSource_RowCount = dataFlowTask.PathCollection.New();
pathSource_RowCount.AttachPathAndPropagateNotifications(source.OutputCollection[0], RowCountComponent.InputCollection[0]);
// Connect the source and the transform
//dataFlowTask.PathCollection.New().AttachPathAndPropagateNotifications(source.OutputCollection[0],
// dataConvertComponent.InputCollection[0]);
// Create and configure an OLE DB destination component.
IDTSComponentMetaData90 destination = dataFlowTask.ComponentMetaDataCollection.New();
destination.ComponentClassID = "DTSAdapter.OLEDBDestination.1";
// Create the design-time instance of the destination.
CManagedComponentWrapper destDesignTime = destination.Instantiate();
// The ProvideComponentProperties method creates a default input.
destDesignTime.ProvideComponentProperties();
//added
destination.Name = "Role from OLAP";
// Assign the connection manager.
destination.RuntimeConnectionCollection[0].ConnectionManagerID = DconMgr.ID;
destination.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(DconMgr);
// Set the custom properties.
destDesignTime.SetComponentProperty("AccessMode", 3); // Mode 3 : OpenRowset Using FastLoad / Table - View fast load
destDesignTime.SetComponentProperty("OpenRowset", "[dbo].[Role]");
destDesignTime.AcquireConnections(null);
destDesignTime.ReinitializeMetaData();
destDesignTime.ReleaseConnections();
//// Create the path from Rowcount to destination.
IDTSPath90 pathRowCount_Dest = dataFlowTask.PathCollection.New();
pathRowCount_Dest.AttachPathAndPropagateNotifications(RowCountComponent.OutputCollection[0], destination.InputCollection[0]);
// Get the destination's default input and virtual input.
IDTSInput90 input1 = destination.InputCollection[0];
IDTSVirtualInput90 vInput = input1.GetVirtualInput();
IDTSOutput90 dataConvertOutput = destination.OutputCollection[0];
// Iterate through the virtual input column collection.
foreach (IDTSVirtualInputColumn90 vColumn in vInput.VirtualInputColumnCollection)
{
destDesignTime.SetUsageType(
input1.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READONLY);
}
After getting the tranformed IDTSOutputColumn100 column in each loop ...how do i override the destination columns given below :
//map external metadata to the inputcolumn
foreach (IDTSInputColumn90 inputColumn in input1.InputColumnCollection)
{
string formattedColName = getformmatedSourceColumn(dtMappedExcelToDB, inputColumn.Name);
IDTSExternalMetadataColumn90 exMetaColumn = (IDTSExternalMetadataColumn90)input1.ExternalMetadataColumnCollection[formattedColName];
inputColumn.ExternalMetadataColumnID = exMetaColumn.ID;
}
//Saving the package
app.SaveToXml(@"D:\iPlan\Site work\SamplePackage\SamplePackage\SamplePackage.dtsx", package, null);
//app.SaveToXml(@"\\ts-dev1\\iPlan\\Site work\\SamplePackage\\SamplePackage\\SamplePackage.dtsx", package, null);
string pkgLocation;
Package pkg;
pkgLocation =
@"D:\iPlan\Site work\SamplePackage\SamplePackage\SamplePackage.dtsx";
Application app1 = new Application();
pkg = app1.LoadPackage(pkgLocation, null);
pkg.Execute();
}
catch (COMException ex)
{
string symbolicName = GetSymbolicName(ex.ErrorCode);
System.Diagnostics.Debug.WriteLine(ex.GetType().Name);
System.Diagnostics.Debug.WriteLine(ex.ErrorCode);
throw (ex);
}
}
public Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType getSSISDataTypes(string DataType)
{
int codepage = 0; ;
int length = 0; ;
int scale=0;
int precision=0;
Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType Dtype = Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_BYTES;
switch (DataType)
{
case "System.Bit": Dtype = Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_BOOL;
codepage = 0;
length = 0;
scale = 0;
precision = 0;
break;
case "System.Binary": Dtype = Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_BYTES;
codepage = 0;
length = 50;
scale = 0;
precision = 0;
break;
case "System.Varbinary": Dtype = Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_BYTES;
codepage = 0;
length = 0;
scale = 0;
precision = 0;
break;
case "System.Timestamp": Dtype = Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_BYTES;
codepage = 0;
length = 50;
scale = 0;
precision = 0;
break;
case "System.Money": Dtype = Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_CY;
codepage = 0;
length = 0;
scale = 0;
precision = 0;
break;
case "System.Smallmoney": Dtype = Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_CY;
codepage = 0;
length = 0;
scale = 0;
precision = 0;
break;
case "System.Date": Dtype = Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_DBDATE;
codepage = 0;
length = 0;
scale = 0;
precision = 0;
break;
case "System.Datetime": Dtype = Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_DBTIMESTAMP;
codepage = 0;
length = 0;
scale = 0;
precision = 0;
break;
//case "System.Datetime2": Dtype = Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_DBTIMESTAMP2;
// codepage = 0;
// length = 0;
// scale = 0;
// precision = 0;
// break;
case "System.Smalldatetime": Dtype = Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_DBTIMESTAMP;
codepage = 0;
length = 0;
scale = 0;
precision = 0;
break;
case "System.nchar": Dtype = Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_WSTR;
codepage = 0;
length = 3000;
scale = 0;
precision = 0;
break;
case "System.Nvarchar": Dtype = Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_WSTR;
codepage = 0;
length = 3000;
scale = 0;
precision = 0;
break;
case "System.Sql_variant": Dtype = Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_WSTR;
codepage = 0;
length = 3000;
scale = 0;
precision = 0;
break;
case "System.Xml": Dtype = Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_WSTR;
codepage = 0;
length = 3000;
scale = 0;
precision = 0;
break;
case "System.Decimal": Dtype = Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_NUMERIC;
codepage = 0;
length = 0;
scale = 0;
precision = 18;
break;
case "System.Numeric": Dtype = Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_NUMERIC;
codepage = 0;
length = 0;
scale = 3;
precision = 2;
break;
case "System.Char": Dtype = Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_STR;
codepage = 1252;
length = 4000;
scale = 0;
precision = 0;
break;
case "System.String":
Dtype = Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_STR;
codepage = 1252;
length = 4000;
scale = 0;
precision = 0;
break;
}
dtSetDataTypeProperties.Rows.Add();
dtSetDataTypeProperties.Rows[0]["length"] = length;
dtSetDataTypeProperties.Rows[0]["scale"] = scale;
dtSetDataTypeProperties.Rows[0]["precision"] = precision;
dtSetDataTypeProperties.Rows[0]["codepage"] = codepage;
return Dtype;
}
public string getformmatedSourceColumn(DataTable dtMappedExcelToDB,string inputColName)
{
string formattedColName = "";
foreach (DataRow dr in dtMappedExcelToDB.Rows)
{
if (dr["Label"].ToString() == inputColName)
{
formattedColName = dr["Combo"].ToString();
break;
}
}
return formattedColName;
}
protected DataSet GetExcelData(string templateName)
{
DataSet ds = null;
OleDbConnection _objExcelConn = new OleDbConnection();
string _ExcelConnectionString = ExcelConnectionString.Replace("@FileName", templateName);
_objExcelConn.ConnectionString = _ExcelConnectionString;
try
{
ds = new DataSet();
OleDbCommand command = new OleDbCommand("Select * FROM [" + "Sheet1" + "$]", _objExcelConn);
_objExcelConn.Open();
OleDbDataAdapter da = new OleDbDataAdapter(command);
da.Fill(ds);
}
catch(Exception e)
{
throw (e);
}
finally
{
_objExcelConn.Close();
_objExcelConn.Dispose();
}
return ds;
}
protected DataTable GetDBColumnNames()
{
DataSet ds =new DataSet ();
DataTable dt = null;
OleDbConnection conn = new OleDbConnection(ConfigurationManager.AppSettings["AdventureWorksConnectionString"].ToString());
try
{
OleDbCommand cmd = new OleDbCommand("Select * from Role", conn);
conn.Open();
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
da.Fill(ds);
dt = ds.Tables[0];
}
catch (Exception e)
{
throw (e);
}
finally
{
conn.Close();
conn.Dispose();
}
return dt;
}
public static string GetSymbolicName(int errorCode)
{
string symbolicName = string.Empty;
HResults hresults = new HResults();
foreach (FieldInfo fieldInfo in hresults.GetType().GetFields())
{
if ((int)fieldInfo.GetValue(hresults) == errorCode)
{
symbolicName = fieldInfo.Name;
break;
}
}
return symbolicName;
}
}
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply