EzApi: How to map outputcolumn from EzDataConvert to inputcolumn on EzSqlDestination
This ought to be reasonably easy but it has me pulled out my already sparse hair in frustration. Deriving from EzTransformPackage<EzOleDbSource, EzOleDbConnectionManager, EzDataConvert, EzSqlDestination, EzOleDbConnectionManager> I manage to setup the package, and can view it in BIDS. During the package creation, I loop through the Transform.Meta.InputCollection[0].InputColumnCollection to find columns with datatypes I need to transform. For these I call the Convert methods on the Transform property to add the transformed column to the outputcollection of the Transform. This seems to work fine. When I open the package in BIDS the transformed columns appear correctly in the DataConvert component, and they are also availabe in the Mapping panel of the Edit SqlDestination dialog. But no matter what I try, I cannot through EzApi get the SqlDestination to use the correct, transformed outputcolumn from the DataConvert component. I can very easily - in BIDS - change the mapping in the SqlDestination component to use the correct column, so I believe that my package is quite valid. If anybody can lend me a hand here I would be most grateful. Thanks Here is my code - it creates a Dictionary containing the original column name and the name of the transformed outputcolumn in the DataConvert component. I have deleted all attempts to map the transformed output column from the DataConvert component to the correct inputcolumn on thye SqlDestination component. Important note: My task is to load data from a DB2/zOS database to an Sql Server 2008 database. All columnnames are identical in the source and target databases. public class DB2toSqlPackageEzApi : EzTransformPackage<EzOleDbSource, EzOleDbConnectionManager, EzDataConvert, EzSqlDestination, EzOleDbConnectionManager> { private static readonly int _codePage; static DB2toSqlPackageEzApi() { _codePage = 1252; } public DB2toSqlPackageEzApi(string srcCS, string srcsql, string tgtCS, string tgtTable) { SrcConn.ConnectionString = srcCS; SrcConn.Name = "DB2"; Source.AccessMode = AccessMode.AM_SQLCOMMAND; Source.SqlCommand = srcsql; Source.Name = "DB2"; Source.LinkAllInputsToOutputs(); DestConn.ConnectionString = tgtCS; DestConn.Name = "SQL"; Dest.Table = tgtTable; Dest.Name = "SQL"; Dest.MaxInsertCommitSize = 10000; Dest.ReinitializeMetaData(); Dest.LinkAllInputsToOutputs(); Transform.Name = "Convert"; Transform.LinkAllInputsToOutputs(); Dictionary<string, string> transforms = new Dictionary<string, string>(); for (int i = 0; i < Transform.Meta.InputCollection[0].InputColumnCollection.Count; i++) { var iCol = Transform.Meta.InputCollection[0].InputColumnCollection[i]; int oScale = 0; int oLength = 0; int oCodepage = 0; var dType = DataType.DT_EMPTY; switch (iCol.DataType) { case DataType.DT_DBTIMESTAMP: dType = DataType.DT_DBTIMESTAMP2; oScale = 6; break; case DataType.DT_WSTR: dType = DataType.DT_STR; oLength = iCol.Length; oCodepage = _codePage; break; default: break; } if (DataType.DT_EMPTY != dType) { var oName = string.Concat(iCol.Name, "_cnv"); Transform.Convert(iCol.Name, oName, dType, oLength, 0, oScale, oCodepage); transforms.Add(iCol.Name, oName); } } DataFlow.Name = tgtTable.Split('.').Last(); ReinitializeMetaData(); } }
September 27th, 2011 6:13pm

Just thinking: your difficulty in getting the transformed outputcolumn may be related to the fact you are using an OLEDB to connect to DB2. May not play nice. Can you try to use ADO instead?Arthur My Blog
Free Windows Admin Tool Kit Click here and download it now
September 27th, 2011 7:35pm

Hello Arthur, thanks for your answer. There is no 'out of the box' ADO driver for DB2. Via google I can find a 3. party driver from DataDirect (http://www.datadirect.com/products/net/net-for-db2/), but I am on a customer system and limited by their policies, so I cannot install that one. Any way, my (very simple) data flow is like this: OleDb Source (IBM Ole DB driver for DB2) -> Data Conversion -> SqlDestination. My problem lies in getting the SqlDestination to use the correct output column from the Data Conversion, so the Ole DB source is not really involved.
September 28th, 2011 12:04pm

After about 24 hours of high frustration levels I finally got it solved. I post the solution here in case anybody have the same kind of issues. <TiredRant> The thing is, that documentation that I have been able to find on both the EzApi and also the official, COM wrapper interface to SSIS consist of only sample code, none of which covers this area at all (in fact, the only destination type in play in all samples is the FlatFile destinations, usage of the EzDataConvert component is not documented at all). The result is that in order to solve this kind of problem the only solution (if you are not already an expert) is a fumbling mountain of frustrating quesswork in the dark in order to comprehend these (not uncomplicated) interfaces. Why is there no proper documentation - at least for the official interface? </TiredRant> Anyway, below is the code that works. Bear in mind that the purpose is to copy data from a table in DB2/zOS to a table in Sql Server 2008. The 2 tables contain the same columns, but it is necessary to convert certain datatypes on the way (unicode strings and DB2 timestamps to DateTime2) using System.Linq; using Microsoft.SqlServer.Dts.Runtime.Wrapper; using Microsoft.SqlServer.SSIS.EzAPI; namespace Xact.XdataCon.Export.SSIS.Library { public class DB2toSqlPackage : EzTransformPackage<EzOleDbSource, EzOleDbConnectionManager, EzDataConvert, EzSqlDestination, EzOleDbConnectionManager> { private static readonly int _codePage; static DB2toSqlPackage() { _codePage = 1252; } public DB2toSqlPackage(string srcCS, string srcsql, string tgtCS, string tgtTable) { SrcConn.ConnectionString = srcCS; SrcConn.Name = "DB2"; Source.AccessMode = AccessMode.AM_SQLCOMMAND; Source.SqlCommand = srcsql; Source.Name = "DB2"; Source.LinkAllInputsToOutputs(); DestConn.ConnectionString = tgtCS; DestConn.Name = "SQL"; Dest.Table = tgtTable; Dest.Name = "SQL"; Dest.MaxInsertCommitSize = 10000; Dest.KeepNulls = true; // Necessary to call Dest.ReinitializeMetaData() in order to // set up the ExternalColumns of the Dest component Dest.ReinitializeMetaData(); // Dest.ReinitializeMetaData() have set up all input columns and linked them to // the identically named output columns from the Source component // This is no good, so all Dest inputcolumns are removed below for (int i = Dest.Meta.InputCollection[0].InputColumnCollection.Count - 1; i > -1; i--) { var name = Dest.Meta.InputCollection[0].InputColumnCollection[i].Name; Dest.DeleteInputColumn(0, name, false); } Transform.Name = "Convert"; // Loop through Source outputcolumns and look for required conversions for (int i = 0; i < Source.Meta.OutputCollection[0].OutputColumnCollection.Count; i++) { var iCol = Source.Meta.OutputCollection[0].OutputColumnCollection[i]; var iName = iCol.Name; int oScale = 0; int oLength = 0; int oCodepage = 0; var dType = DataType.DT_EMPTY; switch (iCol.DataType) { case DataType.DT_DBTIMESTAMP: dType = DataType.DT_DBTIMESTAMP2; oScale = 6; break; case DataType.DT_WSTR: dType = DataType.DT_STR; oLength = iCol.Length; oCodepage = _codePage; break; default: break; } if (DataType.DT_EMPTY != dType) { // Conversion is required, add it to the DataConvert component var oName = string.Concat(iName, "_cnv"); Transform.Convert(iName, oName, dType, oLength, 0, oScale, oCodepage); // Create the input column in Dest component linked to the converted column // from the Transform component and mapped to external column Dest.MapColumn(oName, iName); } else { // Conversion is not required // Create the input column in Dest component linked to the orignal column // from the Source component and mapped to external column Dest.MapColumn(iName, iName); } } DataFlow.Name = tgtTable.Split('.').Last(); } } }
Free Windows Admin Tool Kit Click here and download it now
September 28th, 2011 12:20pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics