Question

Problem with SSIS derived column component programmatically importing data

Asked by: Brettney

Hi experts,

I'm trying to create a package programmatically to import data into SSIS. I'm able to do this via the wizard but, for our project we need it to be done programmatically. I've been successful in creating packages that use the Data conversion component but when I use this component nothing happens.

The package is created successfully and validates ok. Except that when you try to execute it, it fails. I've tried on a number of files all with the same result.

I've attached the code if anyone can help I would be most appreciative.

I'm using .net 3.5 and sql server 2005.

Dictionary<int, string> derivedLineageIdentifiers = new Dictionary<int, string>();
 
                    string filepath = dbfFile.Remove(dbfFile.LastIndexOf(@"\"));
 
                    //set up the package
                    package = new Package();
                    //creates and executable (which translates to a dataflowtask in SSIS)
                    Executable exec = package.Executables.Add("STOCK:PipelineTask");
                    TaskHost taskHost = exec as TaskHost;
                    MainPipe dataFlowTask = taskHost.InnerObject as MainPipe;
 
                    /* ############################################ */
                    /* ADD THE VFP SOURCE COMPONENT TO THE DATAFLOW */
                    /* ############################################ */
 
                    //Creates the connection to the FoxPro dbf file
                    ConnectionManager cm = package.Connections.Add("OLEDB");
                    cm.Name = "FoxPro Connection Manager";
                    cm.ConnectionString = "Data Source=" + filepath + ";Provider=VFPOLEDB.1;Extended Properties=dBase 5.0;Collating Sequence=MACHINE;";//@"Provider=VFPOLEDB.1; DataSource=" + filepath + ";Extended Properties=dBase 5.0; Collating Sequence=general";                    
 
                    //Creates the source component and instantiates it
                    IDTSComponentMetaData90 sourceDataFlowComponent = dataFlowTask.ComponentMetaDataCollection.New();
                    sourceDataFlowComponent.Name = "Visual Fox Pro DBF Source";
                    sourceDataFlowComponent.ComponentClassID = "{2C0A8BE5-1EDC-4353-A0EF-B778599C65A0}";
                    CManagedComponentWrapper instance = sourceDataFlowComponent.Instantiate();
                    instance.ProvideComponentProperties();
 
                    //Specifies the connection manager
                    if (sourceDataFlowComponent.RuntimeConnectionCollection.Count > 0)
                    {
                        sourceDataFlowComponent.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(package.Connections[0]);
                        sourceDataFlowComponent.RuntimeConnectionCollection[0].ConnectionManagerID = package.Connections[0].ID;
                    }
 
                    //Set custom properties
                    instance.SetComponentProperty("AccessMode", 2);
                    instance.SetComponentProperty("SqlCommand", "select * from '" + dbfFile + "'");
                    instance.SetComponentProperty("DefaultCodePage", 1252);
                    instance.SetComponentProperty("AlwaysUseDefaultCodePage", false);
                    instance.SetComponentProperty("CommandTimeout", 0);
 
                    //Reinitialize the metadata (not sure this is required - will test)
                    instance.AcquireConnections(null);
                    instance.ReinitializeMetaData();
 
                    //Initialize and output Column
                    IDTSExternalMetadataColumn90 exOutColumn;
 
                    foreach (IDTSOutputColumn90 outColumn in sourceDataFlowComponent.OutputCollection[0].OutputColumnCollection)
                    {
                        exOutColumn = sourceDataFlowComponent.OutputCollection[0].ExternalMetadataColumnCollection[outColumn.Name];
                        instance.MapOutputColumn(sourceDataFlowComponent.OutputCollection[0].ID, outColumn.ID, exOutColumn.ID, true);
                    }
                    instance.ReleaseConnections();
 
                    /* ############################################ */
                    /* ADD TRANSFORMATION COMPONENT TO THE DATAFLOW */
                    /* ############################################ */
 
                    //Creates and configures the transformation component
                    IDTSComponentMetaData90 transformationDataFlowComponent = dataFlowTask.ComponentMetaDataCollection.New();
                    transformationDataFlowComponent.Name = "Derived Column Component";
                    //transformationDataFlowComponent.ComponentClassID = "DTSTransform.DataConvert.1";
                    transformationDataFlowComponent.ComponentClassID = "{9CF90BF0-5BCC-4C63-B91D-1F322DC12C26}";
                    CManagedComponentWrapper transformationInstance = transformationDataFlowComponent.Instantiate();                    
                    transformationInstance.ProvideComponentProperties();
                    transformationDataFlowComponent.InputCollection[0].ExternalMetadataColumnCollection.IsUsed = false;
                    transformationDataFlowComponent.InputCollection[0].HasSideEffects = false;
 
                    //Create the path from source to transformation
                    IDTSPath90 fPath = dataFlowTask.PathCollection.New();
                    fPath.AttachPathAndPropagateNotifications(sourceDataFlowComponent.OutputCollection[0], transformationDataFlowComponent.InputCollection[0]);
                    transformationInstance.AcquireConnections(null);
                    transformationInstance.ReinitializeMetaData();
                    //Get the output collection
                    IDTSOutput90 output = transformationDataFlowComponent.OutputCollection[0];
 
                    //Create the input columns for the transformation component
                    IDTSInput90 input;
                    IDTSVirtualInput90 vInput;
                    //Get the destinations default input and virtual input
                    input = transformationDataFlowComponent.InputCollection[0];
                    vInput = input.GetVirtualInput();
 
                    //Iterate through the virtual input column collection
                    foreach (IDTSVirtualInputColumn90 vColumn in vInput.VirtualInputColumnCollection)
                    {
                        IDTSInputColumn90 inputColumn = transformationInstance.SetUsageType(input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READONLY);
                        
                        //propogate to output column collection.
                        //IDTSOutputColumn90 outputColumn = transformationInstance.InsertOutputColumnAt(output.ID, output.OutputColumnCollection.Count, vColumn.Name, "Sql server compatible conversion column");
 
                        IDTSOutputColumn90 outputColumn;
                        IDTSCustomProperty90 property;
                        /* for all the columns, search for the ones which are from the converted output collection
                         * then iterate through all these adding the derived column outputs while placing the IDs into
                         * a new derivedColumnLineageID collection
                         */
                        switch (inputColumn.Name.ToUpper())
                        {
                            case "GROUP":
                                //for each new column remember to set new column name
                                outputColumn = transformationDataFlowComponent.OutputCollection[0].OutputColumnCollection.New();
                                outputColumn.SetDataTypeProperties(Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_WSTR, 1, inputColumn.Precision, inputColumn.Scale, 0);
                                outputColumn.Name = "status";
                                outputColumn.ExternalMetadataColumnID = 0;
                                property = outputColumn.CustomPropertyCollection.New();
                                property.Name = "Expression";
                                property.Value = "[SUBSTRING](#" + inputColumn.LineageID + ",5,1)";
                                property = outputColumn.CustomPropertyCollection.New();
                                property.Name = "FriendlyExpression";
                                property.Value = "SUBSTRING(GROUP,5,1)";
                                derivedLineageIdentifiers[outputColumn.LineageID] = outputColumn.Name;
                                //for each new column remember to set new column name
                                outputColumn = transformationDataFlowComponent.OutputCollection[0].OutputColumnCollection.New();
                                outputColumn.SetDataTypeProperties(Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_I4, 0, 0, 0, 0);
                                outputColumn.Name = "class";
                                outputColumn.ExternalMetadataColumnID = 0;
                                property = outputColumn.CustomPropertyCollection.New();
                                property.Name = "Expression";
                                property.Value = "(DT_I4)[SUBSTRING](#" + inputColumn.LineageID + ",9,[FINDSTRING]([RIGHT](#" + inputColumn.LineageID + ",[LEN](#" + inputColumn.LineageID + ") - 9),\"_\",1))";
                                property = outputColumn.CustomPropertyCollection.New();
                                property.Name = "FriendlyExpression";
                                property.Value = "(DT_I4)SUBSTRING(GROUP,9,FINDSTRING(RIGHT(GROUP,LEN(GROUP) - 9),\"_\",1))";
                                derivedLineageIdentifiers[outputColumn.LineageID] = outputColumn.Name;
                                //for each new column remember to set new column name
                                outputColumn = transformationDataFlowComponent.OutputCollection[0].OutputColumnCollection.New();
                                outputColumn.SetDataTypeProperties(Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_WSTR, inputColumn.Length, inputColumn.Precision, inputColumn.Scale, 0);
                                outputColumn.Name = "employer";
                                outputColumn.ExternalMetadataColumnID = 0;
                                property = outputColumn.CustomPropertyCollection.New();
                                property.Name = "Expression";
                                property.Value = "[FINDSTRING](#" + inputColumn.LineageID + ",\"_ER\",1) == 0 ? NULL(DT_WSTR,50) : [SUBSTRING](#" + inputColumn.LineageID + ",[FINDSTRING](#" + inputColumn.LineageID + ",\"_ER\",1) + 3,[FINDSTRING]([RIGHT](#" + inputColumn.LineageID + ",[LEN](#" + inputColumn.LineageID + ") - ([FINDSTRING](#" + inputColumn.LineageID + ",\"_ER\",1) + 3)),\"_\",1))";
                                property = outputColumn.CustomPropertyCollection.New();
                                property.Name = "FriendlyExpression";
                                property.Value = "FINDSTRING(GROUP,\"_ER\",1) == 0 ? NULL(DT_WSTR,50) : SUBSTRING(GROUP,FINDSTRING(GROUP,\"_ER\",1) + 3,FINDSTRING(RIGHT(GROUP,LEN(GROUP) - (FINDSTRING(GROUP,\"_ER\",1) + 3)),\"_\",1))";
                                derivedLineageIdentifiers[outputColumn.LineageID] = outputColumn.Name;
                                //for each new column remember to set new column name
                                outputColumn = transformationDataFlowComponent.OutputCollection[0].OutputColumnCollection.New();
                                outputColumn.SetDataTypeProperties(Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_I4, 0, 0, 0, 0);
                                outputColumn.Name = "age";
                                outputColumn.ExternalMetadataColumnID = 0;
                                property = outputColumn.CustomPropertyCollection.New();
                                property.Name = "Expression";
                                property.Value = "[FINDSTRING](#" + inputColumn.LineageID + ",\"_AG\",1) == 0 ? NULL(DT_I4) : (DT_I4)[SUBSTRING](#" + inputColumn.LineageID + ",[FINDSTRING](#" + inputColumn.LineageID + ",\"_AG\",1) + 3,[FINDSTRING]([RIGHT](#" + inputColumn.LineageID + ",[LEN](#" + inputColumn.LineageID + ") - ([FINDSTRING](#" + inputColumn.LineageID + ",\"_AG\",1) + 3)),\"_\",1))";
                                property = outputColumn.CustomPropertyCollection.New();
                                property.Name = "FriendlyExpression";
                                property.Value = "FINDSTRING(GROUP,\"_AG\",1) == 0 ? NULL(DT_I4) : (DT_I4)SUBSTRING(GROUP,FINDSTRING(GROUP,\"_AG\",1) + 3,FINDSTRING(RIGHT(GROUP,LEN(GROUP) - (FINDSTRING(GROUP,\"_AG\",1) + 3)),\"_\",1))";
                                derivedLineageIdentifiers[outputColumn.LineageID] = outputColumn.Name;
                                //for each new column remember to set new column name
                                outputColumn = transformationDataFlowComponent.OutputCollection[0].OutputColumnCollection.New();
                                outputColumn.SetDataTypeProperties(Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_I4, 0, 0, 0, 0);
                                outputColumn.Name = "individual";
                                outputColumn.ExternalMetadataColumnID = 0;
                                property = outputColumn.CustomPropertyCollection.New();
                                property.Name = "Expression";
                                property.Value = "[FINDSTRING](#25124,\"_\",3 + ([FINDSTRING](#" + inputColumn.LineageID + ",\"_ER\",1) == 0 ? 0 : 1) + ([FINDSTRING](#" + inputColumn.LineageID + ",\"_AG\",1) == 0 ? 0 : 1)) == 0 ? NULL(DT_I4) : (DT_I4)[SUBSTRING](#" + inputColumn.LineageID + ",1 + [FINDSTRING](#25124,\"_\",2 + ([FINDSTRING](#25124,\"_ER\",1) == 0 ? 0 : 1) + ([FINDSTRING](#" + inputColumn.LineageID + ",\"_AG\",1) == 0 ? 0 : 1)),-1 + [FINDSTRING](#25124,\"_\",3 + ([FINDSTRING](#" + inputColumn.LineageID + ",\"_ER\",1) == 0 ? 0 : 1) + ([FINDSTRING](#" + inputColumn.LineageID + ",\"_AG\",1) == 0 ? 0 : 1)) - [FINDSTRING](#" + inputColumn.LineageID + ",\"_\",2 + ([FINDSTRING](#" + inputColumn.LineageID + ",\"_ER\",1) == 0 ? 0 : 1) + ([FINDSTRING](#" + inputColumn.LineageID + ",\"_AG\",1) == 0 ? 0 : 1)))";
                                property = outputColumn.CustomPropertyCollection.New();
                                property.Name = "FriendlyExpression";
                                property.Value = "FINDSTRING(GROUP,\"_\",3 + (FINDSTRING(GROUP,\"_ER\",1) == 0 ? 0 : 1) + (FINDSTRING(GROUP,\"_AG\",1) == 0 ? 0 : 1)) == 0 ? NULL(DT_I4) : (DT_I4)SUBSTRING(GROUP,1 + FINDSTRING(GROUP,\"_\",2 + (FINDSTRING(GROUP,\"_ER\",1) == 0 ? 0 : 1) + (FINDSTRING(GROUP,\"_AG\",1) == 0 ? 0 : 1)),-1 + FINDSTRING(GROUP,\"_\",3 + (FINDSTRING(GROUP,\"_ER\",1) == 0 ? 0 : 1) + (FINDSTRING(GROUP,\"_AG\",1) == 0 ? 0 : 1)) - FINDSTRING(GROUP,\"_\",2 + (FINDSTRING(GROUP,\"_ER\",1) == 0 ? 0 : 1) + (FINDSTRING(GROUP,\"_AG\",1) == 0 ? 0 : 1)))";
                                derivedLineageIdentifiers[outputColumn.LineageID] = outputColumn.Name;
                                break;
                            case "PERIOD":
                                outputColumn = transformationDataFlowComponent.OutputCollection[0].OutputColumnCollection.New();
                                outputColumn.SetDataTypeProperties(Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_I4, 0, 0, 0, 0);
                                outputColumn.Name = "year";
                                outputColumn.ExternalMetadataColumnID = 0;
                                property = outputColumn.CustomPropertyCollection.New();
                                property.Name = "Expression";
                                property.Value = "(DT_I4)[RIGHT](#" + inputColumn.LineageID + ",[LEN](#" + inputColumn.LineageID + ") - 4)";
                                property = outputColumn.CustomPropertyCollection.New();
                                property.Name = "FriendlyExpression";
                                property.Value = "(DT_I4)RIGHT(PERIOD,LEN(PERIOD) - 4)";
                                derivedLineageIdentifiers[outputColumn.LineageID] = outputColumn.Name;
                                break;
                            case "TRAN_ORIG":
                                outputColumn = transformationDataFlowComponent.OutputCollection[0].OutputColumnCollection.New();
                                outputColumn.SetDataTypeProperties(Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_I4, 0, 0, 0, 0);
                                outputColumn.Name = "tranche";
                                outputColumn.ExternalMetadataColumnID = 0;
                                property = outputColumn.CustomPropertyCollection.New();
                                property.Name = "Expression";
                                property.Value = "#" + inputColumn.LineageID;
                                property = outputColumn.CustomPropertyCollection.New();
                                property.Name = "FriendlyExpression";
                                property.Value = "TRAN_ORIG";
                                derivedLineageIdentifiers[outputColumn.LineageID] = outputColumn.Name;
                                break;
                                //USE THESE CASES TO REMOVE THESE COLUMNS FROM THE OUTPUT
                            case "PRODUCT":
                                break;
                            case "PURPOSE":
                                break;
                            case "DISC_RATE":
                                break;
                            case "TIME":
                                break;
                            case "T_FROM":
                                break;
                            case "T_TO":
                                break;
                            case "INPUT_VAR":
                                break;
                            default:
                                outputColumn = transformationInstance.InsertOutputColumnAt(output.ID, output.OutputColumnCollection.Count, inputColumn.Name, "standard column");
                                outputColumn.ExternalMetadataColumnID = 0;
                                derivedLineageIdentifiers[outputColumn.LineageID] = outputColumn.Name;
                                break;
                        }                        
                    }
transformationInstance.ReleaseConnections();
 
 
                    /* ########################################### */
                    /* NOW ADD OUR THIRD COMPONENT TO THE DATAFLOW */
                    /* ########################################### */
 
                    //Creates the SQL destination connection
                    ConnectionManager cm2 = package.Connections.Add("OLEDB");
                    cm2.Name = "MS SQL Server 2005 Exodus Cash Flows DB";
                    cm2.ConnectionString = @"Provider=SQLNCLI.1;Data Source=HRGLADEV07;Initial Catalog=ExodusCashFlow;Integrated Security=SSPI;Auto Translate=false;";
 
                    //Creates the SQL destination component and instantiates it
                    IDTSComponentMetaData90 destination = dataFlowTask.ComponentMetaDataCollection.New();
                    destination.Name = "SQL Server Destination";
                    destination.ComponentClassID = "{E2568105-9550-4F71-A638-B7FE42E66922}";
                    CManagedComponentWrapper destDesignTime = destination.Instantiate();
                    destDesignTime.ProvideComponentProperties();
 
                    //Specify the connection manager for the component
                    if (destination.RuntimeConnectionCollection.Count > 0)
                    {
                        destination.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(package.Connections[1]);
                        destination.RuntimeConnectionCollection[0].ConnectionManagerID = package.Connections[1].ID;
                    }
 
                    //Set custom properties
                    destDesignTime.SetComponentProperty("AccessMode", 3);
                    destDesignTime.SetComponentProperty("OpenRowset", "[ExodusCashFlow].[dbo].[Cashflow]");
                    destDesignTime.SetComponentProperty("FastLoadOptions", "TABLOCK");
                    destDesignTime.SetComponentProperty("DefaultCodePage", 1252);
                    destDesignTime.SetComponentProperty("AlwaysUseDefaultCodePage", false);
 
                    //Create the path from transformation to destination
                    IDTSPath90 path = dataFlowTask.PathCollection.New();
                    //Get the output collection of one component and send it to another
                    path.AttachPathAndPropagateNotifications(transformationDataFlowComponent.OutputCollection[0],
                        destination.InputCollection[0]);
                    destDesignTime.AcquireConnections(null);
                    destDesignTime.ReinitializeMetaData();
 
                    input = destination.InputCollection[0];
                    vInput = input.GetVirtualInput();
 
                    foreach (IDTSVirtualInputColumn90 vColumn in vInput.VirtualInputColumnCollection)
                    {
                        if (derivedLineageIdentifiers.ContainsKey(vColumn.LineageID))
                        {
                            destDesignTime.SetUsageType(input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READONLY);
                        }
                    }
 
                    IDTSExternalMetadataColumn90 exColumn;
                    foreach (IDTSInputColumn90 inColumn in destination.InputCollection[0].InputColumnCollection)
                    {
                        exColumn = destination.InputCollection[0].ExternalMetadataColumnCollection[inColumn.Name.ToUpper()];
                        destDesignTime.MapInputColumn(destination.InputCollection[0].ID, inColumn.ID, exColumn.ID);
                    }
                    destDesignTime.ReleaseConnections();
 
                    ErrorEvents errors = new ErrorEvents();
                    Logging log = new Logging();
 
                    package.Validate(null, null, errors, null);
 
                    long start = DateTime.Now.Ticks;
                    DTSExecResult result = package.Execute(null, null, errors, null, null);
                    long end = DateTime.Now.Ticks;
                    lblResult.Text = result.ToString() + " TIME TAKEN: " + TimeSpan.FromTicks(end - start).TotalSeconds.ToString();

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:
80:
81:
82:
83:
84:
85:
86:
87:
88:
89:
90:
91:
92:
93:
94:
95:
96:
97:
98:
99:
100:
101:
102:
103:
104:
105:
106:
107:
108:
109:
110:
111:
112:
113:
114:
115:
116:
117:
118:
119:
120:
121:
122:
123:
124:
125:
126:
127:
128:
129:
130:
131:
132:
133:
134:
135:
136:
137:
138:
139:
140:
141:
142:
143:
144:
145:
146:
147:
148:
149:
150:
151:
152:
153:
154:
155:
156:
157:
158:
159:
160:
161:
162:
163:
164:
165:
166:
167:
168:
169:
170:
171:
172:
173:
174:
175:
176:
177:
178:
179:
180:
181:
182:
183:
184:
185:
186:
187:
188:
189:
190:
191:
192:
193:
194:
195:
196:
197:
198:
199:
200:
201:
202:
203:
204:
205:
206:
207:
208:
209:
210:
211:
212:
213:
214:
215:
216:
217:
218:
219:
220:
221:
222:
223:
224:
225:
226:
227:
228:
229:
230:
231:
232:
233:
234:
235:
236:
237:
238:
239:
240:
241:
242:
243:
244:
245:
246:
247:
248:
249:
250:
251:
252:
253:
254:
255:
256:
257:
258:
259:
260:
261:
262:
263:
264:
265:
266:
267:
268:
269:
270:
271:
272:
273:
274:
275:
276:
277:
278:
279:

Select all Open in new window

This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.

Subscribe now for full access to Experts Exchange and get

Instant Access to this Solution

Plus...

30 Day FREE access, no risk, no obligation
Collaborate with the world's top tech experts
Unlimited access to our exclusive solution database
Never be left without tech help again

Subscribe Now

Asked On: 2008-12-10 at 04:22:43ID23972361
Tags: microsoft
,; .Net
,; SSIS
,; 3.5
,; c#
Topics: SSIS
,; SQL Server 2005
,; .NET Framework 3.x versions
Participating Experts: 1
Points: 0
Comments: 10

Trusted by hundreds of thousands everyday for fast, accurate and reliable tech support.

"The time we save is the biggest benefit of Experts Exchange to Warner Bros. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange." Mike Kapnisakis, Warner Bros.
"Our team likes having a resource that is more secure than just using Google and most experts using this service really know their stuff. It's nice to look here first versus using Google." Dayna Sellner, Lockheed Martin
"Anytime that I've been stumped with a problem, 9 out of 10 times Experts Exchange has either the accepted solution or an open discussion of the potential solution to the problem." Kenny Red, eBay Inc.

See what Experts Exchange can do for you.

Got a question?

We've got the answer.

Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.

Screenshot of Experts Exchange Knowledgebase

Need individual assistance?

Our experts are ready to help.

If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.

Want to learn from the best?

Read articles from industry experts.

Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.

Working on a long term project?

Store your work and research.

Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.

Access the answers to your technology questions today.

Subscribe Now

30-day free trial. Register in 60 seconds.

What Makes Experts Exchange Unique?

Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.

Trusted by the world's most respected brands.

image of each brand's logo

Faithfully serving IT professionals since 1996.

Experts Exchange Logo

Try it out and discover for yourself.

Subscribe Now

30-day free trial. Register in 60 seconds.

Free Tech Articles

WARNING: 5 Reasons why you should NEVER fix a computer for free.
It is in our nature to love the puzzle. We are obsessed. The lot of us. We love puzzles. We love the challenge. We thrive on finding the answer. We hate disarray. It bothers us deep in our soul. W...
SCCM OSD Basic troubleshooting
SCCM 2007 OSD is a fantastic way to deploy operating systems, however, like most things SCCM issues can sometimes be difficult to resolve due to the sheer volume of logs to sift through and the dispe...
Migrate Small Business Server 2003 to Exchange 2010 and Windows 2008 R2
This guide is intended to provide step by step instructions on how to migrate from Small Business Server 2003 to Windows 2008 R2 with Exchange 2010. For this migration to work you will need the fo...
Create a Win7 Gadget
This article shows you how to create a simple "Gadget" -- a sort of mini-application supported by Windows 7 and Vista. Gadgets can be dropped anywhere on the desktop to provide instant information, ...
Outlook continually prompting for username and password
There have been a lot of questions recently regarding Outlook prompting for a username and password whilst using Exchange 2007. There are a few reasons why this would happen and I will try to cover t...
Backup Exchange 2010 Information Store using Windows Backup
There seems to be quite a lot of confusion around the ability to backup Exchange 2010 using the built in Windows Backup feature. This stems from the omission of this feature prior to Exchange 2007 s...

Cloud Class Webinars

Avoiding Bugs in Microsoft Access
Alison Balter takes and in-depth look at avoiding bugs in Access. In this webinar you will learn about using the immediate window to debug your applications, invoking the debugger, using breakpoints to troubleshoot, stepping through code, setting the next statement to execute, ...
Top 10 Best New Features in Visio 2010
Scott Helmers gives live demonstrations of the top 10 new features in Visio 2010. This webinar will teach you how to create compelling diagrams by adding shapes to the page with a single click, linking the shapes in a diagram to data in Excel (or SQL Server, or SharePoint), ...
IT Consultant Business Secrets Revealed
Michael Munger, Experts Exchange tech pro and IT consultant, pulls back the curtain on his very successful businesses and answers question on every IT consultant and business owner should know about. He shares secrets on what he did to solve the 5 most common problems in IT, ...
Disaster Recovery and Business Continuity
Quest CTO, Mike Billon, gives an overview of the steps involved in building a dunamic disaster recovery plan. Through case studies and an examination of software/hardware tooles for monitoring and testing, you'll gain a better understandin of where you are, where you want ...
Organize Your Visio Diagrams with Containers and Lists
Scott Helmers uses cross functional flowcharts, wireframe diagrams, data graphic legends and seating charts to teach you: how to ustilize all three new structured diagram components in Visio 2010, the best practices for organizeing shapes in previous version of Visio, how to organize ...
How to Us Objects, Properties, Events and Methods in Microsoft Access
Alison Dalter gives an in-depbth look at objects, properties, events and methods in Microsoft Access. In this webinar you will learn about using the object browser, referring to objects, working with properties and methods, working with object variables, understanding the ...

Watch More

Join the Community

Give a Little. Get a Lot.

Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.

Join the Community

Answers

by: BrettneyPosted on 2008-12-10 at 04:25:55ID: 23138167

Not all the code came through, the rest is here:

transformationInstance.ReleaseConnections();

/* ########################################### */
/* NOW ADD OUR THIRD COMPONENT TO THE DATAFLOW */
/* ########################################### */

//Creates the SQL destination connection
ConnectionManager cm2 = package.Connections.Add("OLEDB");
cm2.Name = "MS SQL Server 2005 Exodus Cash Flows DB";
cm2.ConnectionString = @"Provider=SQLNCLI.1;Data Source=HRGLADEV07;Initial Catalog=ExodusCashFlow;Integrated Security=SSPI;Auto Translate=false;";

//Creates the SQL destination component and instantiates it
IDTSComponentMetaData90 destination = dataFlowTask.ComponentMetaDataCollection.New();
destination.Name = "SQL Server Destination";
destination.ComponentClassID = "{E2568105-9550-4F71-A638-B7FE42E66922}";
CManagedComponentWrapper destDesignTime = destination.Instantiate();
destDesignTime.ProvideComponentProperties();

//Specify the connection manager for the component
if (destination.RuntimeConnectionCollection.Count > 0)
{
destination.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(package.Connections[1]);
destination.RuntimeConnectionCollection[0].ConnectionManagerID = package.Connections[1].ID;
}

//Set custom properties
destDesignTime.SetComponentProperty("AccessMode", 3);
destDesignTime.SetComponentProperty("OpenRowset", "[ExodusCashFlow].[dbo].[Cashflow]");
destDesignTime.SetComponentProperty("FastLoadOptions", "TABLOCK");
destDesignTime.SetComponentProperty("DefaultCodePage", 1252);
destDesignTime.SetComponentProperty("AlwaysUseDefaultCodePage", false);

//Create the path from transformation to destination
IDTSPath90 path = dataFlowTask.PathCollection.New();
//Get the output collection of one component and send it to another
path.AttachPathAndPropagateNotifications(transformationDataFlowComponent.OutputCollection[0],
destination.InputCollection[0]);
destDesignTime.AcquireConnections(null);
destDesignTime.ReinitializeMetaData();

input = destination.InputCollection[0];
vInput = input.GetVirtualInput();

foreach (IDTSVirtualInputColumn90 vColumn in vInput.VirtualInputColumnCollection)
{
if (derivedLineageIdentifiers.ContainsKey(vColumn.LineageID))
{
destDesignTime.SetUsageType(input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READONLY);
}
}

IDTSExternalMetadataColumn90 exColumn;
foreach (IDTSInputColumn90 inColumn in destination.InputCollection[0].InputColumnCollection)
{
exColumn = destination.InputCollection[0].ExternalMetadataColumnCollection[inColumn.Name.ToUpper()];
destDesignTime.MapInputColumn(destination.InputCollection[0].ID, inColumn.ID, exColumn.ID);
}
destDesignTime.ReleaseConnections();

ErrorEvents errors = new ErrorEvents();
Logging log = new Logging();

package.Validate(null, null, errors, null);

long start = DateTime.Now.Ticks;
DTSExecResult result = package.Execute(null, null, errors, null, null);
long end = DateTime.Now.Ticks;
lblResult.Text = result.ToString() + " TIME TAKEN: " + TimeSpan.FromTicks(end - start).TotalSeconds.ToString();

by: HoggZillaPosted on 2008-12-10 at 06:25:26ID: 23139076

Please include how you are trying to execute this package. Also include the error received at runtime. And one more thing, after the package is created, are you able to open and run it in BIDS?

by: BrettneyPosted on 2008-12-10 at 07:47:22ID: 23139859

To execute the package I am using:

DTSExecResult result = package.Execute(null, null, errors, null, null);

When I save the package and run it in BIDS it seems to reveal more of the problem, I get the following errors:

Error:The Property "Expression" is empty. The property cannot be empty.
Error: The expression "" on '' input column "product" (374)" is not valid.

It then repeats this error for every input column.Once its finished the input column errors I get:

Error: The output column "status" (725) has an invalid error or truncation row disposition.

Then it tells me the component failed validation.

Any ideas?

by: BrettneyPosted on 2008-12-10 at 08:34:12ID: 23140421

Also when I create the package in BIDS, then I only see 3 inputs and 6 outputs, yet when I create it programmatically I seem to get every column.

Is there an easy way to just create the inputs/outputs I need and map them apporpriately?

by: HoggZillaPosted on 2008-12-10 at 09:44:11ID: 23141060

Looks like the error is in the Derived Column transformation. When you look at this in BIDS, what is in the Derived Column transformation?

by: HoggZillaPosted on 2008-12-10 at 18:09:58ID: 23145142

By the way, I am impressed how much of this you have done. Did you have a good example to go by? When you get this completed it will be a good reference for others. If possible, comment your code at every step so others will be able to follow in the future. Good work!

by: BrettneyPosted on 2008-12-11 at 03:06:35ID: 23147200

Thanks,

I've used a few examples from code project and from sql server central:

http://www.sqlservercentral.com/articles/Integration+Services/64572/http://www.sqlservercentral.com/articles/Integration+Services/64572/

http://www.codeproject.com/KB/database/Digging_SSIS_object_model.aspx?display=PrintAll&fid=420109&df=90&mpp=25&noise=3&sort=Position&view=Quick&fr=26

These helped me most of the way, plus using the MSDN stuff (not very helpful).

I've got a little further though, but my issue is still in the Derived column component but now I am getting a column not referenced error:

Error: The input column "input column "salpenrpi" (504)" is not referenced.

I've attached the updated code with additional comments - Thanks for the replies so far.

//global lineage IDs required for final virtual column collection
                    Dictionary<int, string> globalLineageIDs = new Dictionary<int, string>();
                    //transform lineageIDs - for interim step - should only contain 6 IDs
                    Dictionary<int, string> transformLineageIDs = new Dictionary<int, string>();
                    //derived column lineageIDs - for second interim step - should only contain 8 IDs
                    Dictionary<int, string> derivedColumnLineageIDs = new Dictionary<int, string>();
 
                    string filepath = dbfFile.Remove(dbfFile.LastIndexOf(@"\"));
 
                    //set up the package
                    package = new Package();
                    //creates and executable (which translates to a dataflowtask in SSIS)
                    Executable exec = package.Executables.Add("STOCK:PipelineTask");
                    TaskHost taskHost = exec as TaskHost;
                    MainPipe dataFlowTask = taskHost.InnerObject as MainPipe;
 
                    /* ############################################ */
                    /* ADD THE VFP SOURCE COMPONENT TO THE DATAFLOW */
                    /* ############################################ */
 
                    //Creates the connection to the FoxPro dbf file
                    ConnectionManager cm = package.Connections.Add("OLEDB");
                    cm.Name = "FoxPro Connection Manager";
                    cm.ConnectionString = "Data Source=" + filepath + ";Provider=VFPOLEDB.1;Extended Properties=dBase 5.0;Collating Sequence=MACHINE;";//@"Provider=VFPOLEDB.1; DataSource=" + filepath + ";Extended Properties=dBase 5.0; Collating Sequence=general";                    
 
                    //Creates the source component and instantiates it
                    IDTSComponentMetaData90 sourceDataFlowComponent = dataFlowTask.ComponentMetaDataCollection.New();
                    sourceDataFlowComponent.Name = "Visual Fox Pro DBF Source";
                    sourceDataFlowComponent.ComponentClassID = "{2C0A8BE5-1EDC-4353-A0EF-B778599C65A0}";
                    CManagedComponentWrapper instance = sourceDataFlowComponent.Instantiate();
                    instance.ProvideComponentProperties();
 
                    //Specifies the connection manager
                    if (sourceDataFlowComponent.RuntimeConnectionCollection.Count > 0)
                    {
                        sourceDataFlowComponent.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(package.Connections[0]);
                        sourceDataFlowComponent.RuntimeConnectionCollection[0].ConnectionManagerID = package.Connections[0].ID;
                    }
 
                    //Set custom properties
                    instance.SetComponentProperty("AccessMode", 2);
                    instance.SetComponentProperty("SqlCommand", "select * from '" + dbfFile + "'");
                    instance.SetComponentProperty("DefaultCodePage", 1252);
                    instance.SetComponentProperty("AlwaysUseDefaultCodePage", false);
                    instance.SetComponentProperty("CommandTimeout", 0);
 
                    //Reinitialize the metadata (not sure this is required - will test)
                    instance.AcquireConnections(null);
                    instance.ReinitializeMetaData();
 
                    //Initialize and output Column
                    IDTSExternalMetadataColumn90 exOutColumn;
 
                    foreach (IDTSOutputColumn90 outColumn in sourceDataFlowComponent.OutputCollection[0].OutputColumnCollection)
                    {
                        exOutColumn = sourceDataFlowComponent.OutputCollection[0].ExternalMetadataColumnCollection[outColumn.Name];
                        instance.MapOutputColumn(sourceDataFlowComponent.OutputCollection[0].ID, outColumn.ID, exOutColumn.ID, true);
                    }
                    instance.ReleaseConnections();
 
                    /* ############################################ */
                    /* ADD TRANSFORMATION COMPONENT TO THE DATAFLOW */
                    /* ############################################ */
 
                    //Creates and configures the transformation component
                    IDTSComponentMetaData90 transformationDataFlowComponent = dataFlowTask.ComponentMetaDataCollection.New();
                    transformationDataFlowComponent.Name = "Data Transformaion Component";
                    //transformationDataFlowComponent.ComponentClassID = "DTSTransform.DataConvert.1";
                    transformationDataFlowComponent.ComponentClassID = "{C3BF62C8-7C5C-4F85-83C3-E0B6F6BE267C}";
                    CManagedComponentWrapper transformationInstance = transformationDataFlowComponent.Instantiate();
                    transformationInstance.ProvideComponentProperties();
                    //transformationInstance.SetComponentProperty("ValidateExternalMetadata", true);
 
                    //Create the path from source to transformation
                    IDTSPath90 fPath = dataFlowTask.PathCollection.New();
                    fPath.AttachPathAndPropagateNotifications(sourceDataFlowComponent.OutputCollection[0], transformationDataFlowComponent.InputCollection[0]);
                    transformationInstance.AcquireConnections(null);
                    transformationInstance.ReinitializeMetaData();
                    //Get the output collection
                    IDTSOutput90 output = transformationDataFlowComponent.OutputCollection[0];
 
                    //Create the input columns for the transformation component
                    IDTSInput90 input;
                    IDTSVirtualInput90 vInput;
                    //Get the destinations default input and virtual input
                    input = transformationDataFlowComponent.InputCollection[0];
                    vInput = input.GetVirtualInput();
 
                    //Iterate through the virtual input column collection
                    foreach (IDTSVirtualInputColumn90 vColumn in vInput.VirtualInputColumnCollection)
                    {
                        //set it so that we can read every output column
                        IDTSInputColumn90 inputColumn = transformationInstance.SetUsageType(input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READONLY);
 
                        //Transform the string DataTypes from DT_STR, DT_TEXT to UNICODE STRING TYPES DT_WSTR and DT_NTEXT - we're only transforming 6 columns
                        //so the transformLineageIDs takes the 6 new lineage IDs then adds the original LineageIDs of the others - we end up with 117 as before
                        switch (inputColumn.DataType)
                        {
                            case Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_STR:
                                IDTSOutputColumn90 outputColumn = transformationInstance.InsertOutputColumnAt(output.ID, output.OutputColumnCollection.Count, vColumn.Name, "Sql server compatible conversion column");
                                transformationInstance.SetOutputColumnDataTypeProperties(output.ID, outputColumn.ID, Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_WSTR, vColumn.Length, vColumn.Precision, vColumn.Scale, 0);
                                outputColumn.CustomPropertyCollection[0].Value = inputColumn.LineageID;
                                transformLineageIDs[outputColumn.LineageID] = outputColumn.Name;
                                break;
                            case Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_TEXT:
                                IDTSOutputColumn90 outputColumn1 = transformationInstance.InsertOutputColumnAt(output.ID, output.OutputColumnCollection.Count, vColumn.Name, "Sql server compatible conversion column");
                                transformationInstance.SetOutputColumnDataTypeProperties(output.ID, outputColumn1.ID, Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_NTEXT, vColumn.Length, vColumn.Precision, vColumn.Scale, 0);
                                outputColumn1.CustomPropertyCollection[0].Value = inputColumn.LineageID;
                                transformLineageIDs[outputColumn1.LineageID] = outputColumn1.Name;
                                break;
                            default:
                                //transformationInstance.SetOutputColumnDataTypeProperties(output.ID, outputColumn.ID, vColumn.DataType, vColumn.Length, vColumn.Precision, vColumn.Scale, 0);
                                transformLineageIDs[inputColumn.LineageID] = inputColumn.Name;
                                break;
                        }
                    }
                    //Release the connection
                    transformationInstance.ReleaseConnections();
 
                    /* #################################################### */
                    /* NOW ADD OUR DERIVED COLUMN COMPONENT TO THE DATAFLOW */
                    /* #################################################### */
 
                    IDTSComponentMetaData90 derived = dataFlowTask.ComponentMetaDataCollection.New();
                    derived.Name = "Derived Column Component";
                    derived.ComponentClassID = "{9CF90BF0-5BCC-4C63-B91D-1F322DC12C26}";
                    CManagedComponentWrapper derivedColumns = derived.Instantiate();
                    derivedColumns.ProvideComponentProperties();
                    derived.InputCollection[0].ExternalMetadataColumnCollection.IsUsed = false;
                    derived.InputCollection[0].HasSideEffects = false;
 
                    //Create the path from transformation to derived columns
                    IDTSPath90 dPath = dataFlowTask.PathCollection.New();
                    dPath.AttachPathAndPropagateNotifications(transformationDataFlowComponent.OutputCollection[0], derived.InputCollection[0]);
                    derivedColumns.AcquireConnections(null);
                    derivedColumns.ReinitializeMetaData();
                    //Get the output collection
                    IDTSOutput90 outputd = derived.OutputCollection[0];
 
                    //Create the input columns for the transformation component
                    IDTSInput90 dInput;
                    IDTSVirtualInput90 vdInput;
                    //Get this components default input and virtual input                    
                    dInput = derived.InputCollection[0];
                    vdInput = dInput.GetVirtualInput();
 
                    //Get the output collection
                    IDTSOutput90 dOutput = derived.OutputCollection[0];
 
                    //TAKES CARE OF THE DUPLICATE INPUT COLUMNS
                    foreach (IDTSVirtualInputColumn90 vColumn in vdInput.VirtualInputColumnCollection)
                    {                        
                        //we use the transformLineageIDs so that we have the new set of 117 columns minus the 6 non-unicode columns (these are the ones that were transformed into new columns)
                        //if you examine the virtualinputcolumncollection you'll see we have 123, 6 more than in the Data transform column collection (117 + 6)
                        if (transformLineageIDs.ContainsKey(vColumn.LineageID))
                        {
                            //set it so we can read the columns
                            derivedColumns.SetUsageType(dInput.ID, vdInput, vColumn.LineageID, DTSUsageType.UT_READONLY);
                            //Give me an output column
                            IDTSOutputColumn90 outputColumn;
                            //Give me a CustomProperty
                            IDTSCustomProperty90 property;                            
                            switch (vColumn.Name.ToUpper())
                            {
                                //this should be OO, but in first instance just get it to work!!!
                                case "GROUP":
                                    //for each new column remember to set new column name
                                    outputColumn = derived.OutputCollection[0].OutputColumnCollection.New();
                                    outputColumn.SetDataTypeProperties(Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_WSTR, 1, vColumn.Precision, vColumn.Scale, 0);
                                    outputColumn.Name = "status";
                                    property = outputColumn.CustomPropertyCollection.New();
                                    property.Name = "Expression";
                                    property.Value = "[SUBSTRING](#" + vColumn.LineageID + ",5,1)";
                                    property = outputColumn.CustomPropertyCollection.New();
                                    property.Name = "FriendlyExpression";
                                    property.Value = "SUBSTRING(GROUP,5,1)";
                                    derivedColumnLineageIDs[outputColumn.LineageID] = outputColumn.Name;
 
                                    //for each new column remember to set new column name
                                    outputColumn = derived.OutputCollection[0].OutputColumnCollection.New();
                                    outputColumn.SetDataTypeProperties(Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_I4, 0, 0, 0, 0);
                                    outputColumn.Name = "class";
                                    property = outputColumn.CustomPropertyCollection.New();
                                    property.Name = "Expression";
                                    property.Value = "(DT_I4)[SUBSTRING](#" + vColumn.LineageID + ",9,[FINDSTRING]([RIGHT](#" + vColumn.LineageID + ",[LEN](#" + vColumn.LineageID + ") - 9),\"_\",1))";
                                    property = outputColumn.CustomPropertyCollection.New();
                                    property.Name = "FriendlyExpression";
                                    property.Value = "(DT_I4)SUBSTRING(GROUP,9,FINDSTRING(RIGHT(GROUP,LEN(GROUP) - 9),\"_\",1))";
                                    derivedColumnLineageIDs[outputColumn.LineageID] = outputColumn.Name;
 
                                    //for each new column remember to set new column name
                                    outputColumn = derived.OutputCollection[0].OutputColumnCollection.New();
                                    outputColumn.SetDataTypeProperties(Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_WSTR, vColumn.Length, vColumn.Precision, vColumn.Scale, 0);
                                    outputColumn.Name = "employer";
                                    property = outputColumn.CustomPropertyCollection.New();
                                    property.Name = "Expression";
                                    property.Value = "[FINDSTRING](#" + vColumn.LineageID + ",\"_ER\",1) == 0 ? NULL(DT_WSTR,50) : [SUBSTRING](#" + vColumn.LineageID + ",[FINDSTRING](#" + vColumn.LineageID + ",\"_ER\",1) + 3,[FINDSTRING]([RIGHT](#" + vColumn.LineageID + ",[LEN](#" + vColumn.LineageID + ") - ([FINDSTRING](#" + vColumn.LineageID + ",\"_ER\",1) + 3)),\"_\",1))";
                                    property = outputColumn.CustomPropertyCollection.New();
                                    property.Name = "FriendlyExpression";
                                    property.Value = "FINDSTRING(GROUP,\"_ER\",1) == 0 ? NULL(DT_WSTR,50) : SUBSTRING(GROUP,FINDSTRING(GROUP,\"_ER\",1) + 3,FINDSTRING(RIGHT(GROUP,LEN(GROUP) - (FINDSTRING(GROUP,\"_ER\",1) + 3)),\"_\",1))";
                                    derivedColumnLineageIDs[outputColumn.LineageID] = outputColumn.Name;
 
                                    //for each new column remember to set new column name
                                    outputColumn = derived.OutputCollection[0].OutputColumnCollection.New();
                                    outputColumn.SetDataTypeProperties(Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_I4, 0, 0, 0, 0);
                                    outputColumn.Name = "age";
                                    property = outputColumn.CustomPropertyCollection.New();
                                    property.Name = "Expression";
                                    property.Value = "[FINDSTRING](#" + vColumn.LineageID + ",\"_AG\",1) == 0 ? NULL(DT_I4) : (DT_I4)[SUBSTRING](#" + vColumn.LineageID + ",[FINDSTRING](#" + vColumn.LineageID + ",\"_AG\",1) + 3,[FINDSTRING]([RIGHT](#" + vColumn.LineageID + ",[LEN](#" + vColumn.LineageID + ") - ([FINDSTRING](#" + vColumn.LineageID + ",\"_AG\",1) + 3)),\"_\",1))";
                                    property = outputColumn.CustomPropertyCollection.New();
                                    property.Name = "FriendlyExpression";
                                    property.Value = "FINDSTRING(GROUP,\"_AG\",1) == 0 ? NULL(DT_I4) : (DT_I4)SUBSTRING(GROUP,FINDSTRING(GROUP,\"_AG\",1) + 3,FINDSTRING(RIGHT(GROUP,LEN(GROUP) - (FINDSTRING(GROUP,\"_AG\",1) + 3)),\"_\",1))";
                                    derivedColumnLineageIDs[outputColumn.LineageID] = outputColumn.Name;
 
                                    //for each new column remember to set new column name
                                    outputColumn = derived.OutputCollection[0].OutputColumnCollection.New();
                                    outputColumn.SetDataTypeProperties(Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_I4, 0, 0, 0, 0);
                                    outputColumn.Name = "individual";
                                    property = outputColumn.CustomPropertyCollection.New();
                                    property.Name = "Expression";
                                    property.Value = "[FINDSTRING](#25124,\"_\",3 + ([FINDSTRING](#" + vColumn.LineageID + ",\"_ER\",1) == 0 ? 0 : 1) + ([FINDSTRING](#" + vColumn.LineageID + ",\"_AG\",1) == 0 ? 0 : 1)) == 0 ? NULL(DT_I4) : (DT_I4)[SUBSTRING](#" + vColumn.LineageID + ",1 + [FINDSTRING](#25124,\"_\",2 + ([FINDSTRING](#25124,\"_ER\",1) == 0 ? 0 : 1) + ([FINDSTRING](#" + vColumn.LineageID + ",\"_AG\",1) == 0 ? 0 : 1)),-1 + [FINDSTRING](#25124,\"_\",3 + ([FINDSTRING](#" + vColumn.LineageID + ",\"_ER\",1) == 0 ? 0 : 1) + ([FINDSTRING](#" + vColumn.LineageID + ",\"_AG\",1) == 0 ? 0 : 1)) - [FINDSTRING](#" + vColumn.LineageID + ",\"_\",2 + ([FINDSTRING](#" + vColumn.LineageID + ",\"_ER\",1) == 0 ? 0 : 1) + ([FINDSTRING](#" + vColumn.LineageID + ",\"_AG\",1) == 0 ? 0 : 1)))";
                                    property = outputColumn.CustomPropertyCollection.New();
                                    property.Name = "FriendlyExpression";
                                    property.Value = "FINDSTRING(GROUP,\"_\",3 + (FINDSTRING(GROUP,\"_ER\",1) == 0 ? 0 : 1) + (FINDSTRING(GROUP,\"_AG\",1) == 0 ? 0 : 1)) == 0 ? NULL(DT_I4) : (DT_I4)SUBSTRING(GROUP,1 + FINDSTRING(GROUP,\"_\",2 + (FINDSTRING(GROUP,\"_ER\",1) == 0 ? 0 : 1) + (FINDSTRING(GROUP,\"_AG\",1) == 0 ? 0 : 1)),-1 + FINDSTRING(GROUP,\"_\",3 + (FINDSTRING(GROUP,\"_ER\",1) == 0 ? 0 : 1) + (FINDSTRING(GROUP,\"_AG\",1) == 0 ? 0 : 1)) - FINDSTRING(GROUP,\"_\",2 + (FINDSTRING(GROUP,\"_ER\",1) == 0 ? 0 : 1) + (FINDSTRING(GROUP,\"_AG\",1) == 0 ? 0 : 1)))";
                                    derivedColumnLineageIDs[outputColumn.LineageID] = outputColumn.Name;
                                    break;
                                case "PERIOD":
                                    outputColumn = derived.OutputCollection[0].OutputColumnCollection.New();
                                    outputColumn.SetDataTypeProperties(Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_I4, 0, 0, 0, 0);
                                    outputColumn.Name = "year";
                                    property = outputColumn.CustomPropertyCollection.New();
                                    property.Name = "Expression";
                                    property.Value = "(DT_I4)[RIGHT](#" + vColumn.LineageID + ",[LEN](#" + vColumn.LineageID + ") - 4)";
                                    property = outputColumn.CustomPropertyCollection.New();
                                    property.Name = "FriendlyExpression";
                                    property.Value = "(DT_I4)RIGHT(PERIOD,LEN(PERIOD) - 4)";
                                    derivedColumnLineageIDs[outputColumn.LineageID] = outputColumn.Name;
                                    break;
                                case "TRAN_ORIG":
                                    outputColumn = derived.OutputCollection[0].OutputColumnCollection.New();
                                    outputColumn.SetDataTypeProperties(Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_I4, 0, 0, 0, 0);
                                    outputColumn.Name = "tranche";
                                    property = outputColumn.CustomPropertyCollection.New();
                                    property.Name = "Expression";
                                    property.Value = "#" + vColumn.LineageID;
                                    property = outputColumn.CustomPropertyCollection.New();
                                    property.Name = "FriendlyExpression";
                                    property.Value = "TRAN_ORIG";
                                    derivedColumnLineageIDs[outputColumn.LineageID] = outputColumn.Name;
                                    break;
                                //What we are doing here is basically ditching any columns from the input that we don't want!!
                                //This is done by not adding them to the derivedColumnLineageIDs collection.
                                case "PRODUCT":
                                    break;
                                case "PURPOSE":
                                    break;
                                case "DISC_RATE":
                                    break;
                                case "TIME":
                                    break;
                                case "T_FROM":
                                    break;
                                case "T_TO":
                                    break;
                                case "INPUT_VAR":
                                    break;
                                default:
                                    //just want the lineageIDs passed through from the original to our new collection
                                    derivedColumnLineageIDs[vColumn.LineageID] = vColumn.Name;
                                    break;
                            }
                        }
                    }                                    
                    derivedColumns.ReleaseConnections();
 
                    /* ################################################# */
                    /* NOW ADD OUR DESTINATION COMPONENT TO THE DATAFLOW */
                    /* ################################################# */
 
                    //Creates the SQL destination connection
                    ConnectionManager cm2 = package.Connections.Add("OLEDB");
                    cm2.Name = "MS SQL Server 2005 Exodus Cash Flows DB";
                    cm2.ConnectionString = @"Provider=SQLNCLI.1;Data Source=HRGLADEV07;Initial Catalog=ExodusCashFlow;Integrated Security=SSPI;Auto Translate=false;";
 
                    //Creates the SQL destination component and instantiates it
                    IDTSComponentMetaData90 destination = dataFlowTask.ComponentMetaDataCollection.New();
                    destination.Name = "SQL Server Destination";
                    destination.ComponentClassID = "{E2568105-9550-4F71-A638-B7FE42E66922}";
                    CManagedComponentWrapper destDesignTime = destination.Instantiate();
                    destDesignTime.ProvideComponentProperties();
 
                    //Specify the connection manager for the component
                    if (destination.RuntimeConnectionCollection.Count > 0)
                    {
                        destination.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(package.Connections[1]);
                        destination.RuntimeConnectionCollection[0].ConnectionManagerID = package.Connections[1].ID;
                    }
 
                    //Set custom properties
                    destDesignTime.SetComponentProperty("AccessMode", 3);
                    destDesignTime.SetComponentProperty("OpenRowset", "[ExodusCashFlow].[dbo].[Cashflow]");
                    destDesignTime.SetComponentProperty("FastLoadOptions", "TABLOCK");
                    destDesignTime.SetComponentProperty("DefaultCodePage", 1252);
                    destDesignTime.SetComponentProperty("AlwaysUseDefaultCodePage", false);
 
                    //Create the path from transformation to destination
                    IDTSPath90 path = dataFlowTask.PathCollection.New();
                    //Get the output collection of one component and send it to another
                    path.AttachPathAndPropagateNotifications(derived.OutputCollection[0], destination.InputCollection[0]);
                    destDesignTime.AcquireConnections(null);
                    destDesignTime.ReinitializeMetaData();
                    //Get the inputs and virtual inputs
                    input = destination.InputCollection[0];
                    vInput = input.GetVirtualInput();
 
                    //Now as above - we should see 130 columns in the VirtualInputColumnCollection that is:
                    //the 117 original + 6 data transformed + 7 that we derived above
                    foreach (IDTSVirtualInputColumn90 vColumn in vInput.VirtualInputColumnCollection)
                    {
                        //as before, we only want columns which are in our destination. So what we actually have in the derivedColumnLineageIDs
                        //is 114. This is 117 - 10 columns we removed in the derived(remember ones that you derive directly from are removed)
                        // + 7 the ones we created in the derived column component. This is correct amount.
                        if (derivedColumnLineageIDs.ContainsKey(vColumn.LineageID))
                        {
                            destDesignTime.SetUsageType(input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READONLY);
                        }
                    }
                    
 
                    IDTSExternalMetadataColumn90 exColumn;
                    //now loop through our destination input collection - mapping the columns to the table in the DB
                    foreach (IDTSInputColumn90 inColumn in destination.InputCollection[0].InputColumnCollection)
                    {
                        exColumn = destination.InputCollection[0].ExternalMetadataColumnCollection[inColumn.Name.ToUpper()];
                        destDesignTime.MapInputColumn(destination.InputCollection[0].ID, inColumn.ID, exColumn.ID);
                    }
                    destDesignTime.ReleaseConnections();
 
 
                    //Set up the logging and error handling stuff
                    ErrorEvents errors = new ErrorEvents();
                    Logging log = new Logging();
                    
                   
 
                    DTSExecResult validated = package.Validate(null, null, errors, log);
                    Microsoft.SqlServer.Dts.Runtime.Application app = new Microsoft.SqlServer.Dts.Runtime.Application();
                    app.SaveToXml(@"c:\temp\bob.dtsx", package, null);
 
                    
                    long start = DateTime.Now.Ticks;
                    DTSExecResult result = package.Execute(null, null, errors, null, null);
                    long end = DateTime.Now.Ticks;
                    lblResult.Text = result.ToString() + " TIME TAKEN: " + TimeSpan.FromTicks(end - start).TotalSeconds.ToString();

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:
80:
81:
82:
83:
84:
85:
86:
87:
88:
89:
90:
91:
92:
93:
94:
95:
96:
97:
98:
99:
100:
101:
102:
103:
104:
105:
106:
107:
108:
109:
110:
111:
112:
113:
114:
115:
116:
117:
118:
119:
120:
121:
122:
123:
124:
125:
126:
127:
128:
129:
130:
131:
132:
133:
134:
135:
136:
137:
138:
139:
140:
141:
142:
143:
144:
145:
146:
147:
148:
149:
150:
151:
152:
153:
154:
155:
156:
157:
158:
159:
160:
161:
162:
163:
164:
165:
166:
167:
168:
169:
170:
171:
172:
173:
174:
175:
176:
177:
178:
179:
180:
181:
182:
183:
184:
185:
186:
187:
188:
189:
190:
191:
192:
193:
194:
195:
196:
197:
198:
199:
200:
201:
202:
203:
204:
205:
206:
207:
208:
209:
210:
211:
212:
213:
214:
215:
216:
217:
218:
219:
220:
221:
222:
223:
224:
225:
226:
227:
228:
229:
230:
231:
232:
233:
234:
235:
236:
237:
238:
239:
240:
241:
242:
243:
244:
245:
246:
247:
248:
249:
250:
251:
252:
253:
254:
255:
256:
257:
258:
259:
260:
261:
262:
263:
264:
265:
266:
267:
268:
269:
270:
271:
272:
273:
274:
275:
276:
277:
278:
279:
280:
281:
282:
283:
284:
285:
286:
287:
288:
289:
290:
291:
292:
293:
294:
295:
296:
297:
298:
299:
300:
301:
302:
303:
304:
305:
306:
307:
308:
309:
310:
311:
312:
313:
314:
315:
316:
317:
318:
319:
320:
321:
322:
323:
324:
325:
326:
327:
328:
329:
330:
331:
332:
333:
334:
335:
336:
337:
338:
339:
340:
341:
342:
343:
344:
345:
346:
347:
348:
349:
350:
351:
352:
353:
354:

Select all Open in new window

by: HoggZillaPosted on 2008-12-11 at 03:47:00ID: 23147396

Two things can be happening. Either you have a Source Column that is not being used - this is just a warning though. Or you have a Destination Column that can't find it's Source column.

Once you create the package with the program, can you open the package in BIDS and look at the Data Flow components? I can debug the existing package no problem, but the code is a little more difficult. Sorry.

During creation of the package there is a reference to a column, "salpenrpi", it could be in the Derived Column Transform? Anything unusual about this column? Is it first, last, an odd data type? Anything about this column that makes it special?

by: BrettneyPosted on 2008-12-12 at 02:28:41ID: 23156234

I've managed to solve the issue by plugging away and comparing the working BIDS package to my saved package. Eventually I got all of the inputs/outputs to look the same and it worked.

Thanks for your help though, it definitely gave me pointers as to the places to look for the errors.

The salpenrpi error came out of it being an input colum present in the Data Transform component, once I removed it from the input the error disappeared.

Once this was resolved I had an error to do with error or row truncation disposition. You'll see in the code below how I solved this. SSIS is a brilliant piece of kit but, the documentation on it is truly shocking - if I didn't have the XML output of the package to look at I would've definitely been here for a considerable more amount of time.

When I get the time, hopefully next week, I'll post an article on it reference it here, so hopefully not many people will have to go through the pain that I have this week.

Thanks again, HoggZilla.

//transform lineageIDs - for interim step - should only contain 6 IDs
                    Dictionary<int, string> transformLineageIDs = new Dictionary<int, string>();
                    //derived column lineageIDs - for second interim step - should only contain 8 IDs
                    Dictionary<int, string> derivedColumnLineageIDs = new Dictionary<int, string>();
 
                    string filepath = dbfFile.Remove(dbfFile.LastIndexOf(@"\"));
 
                    //set up the package
                    package = new Package();
                    //creates and executable (which translates to a dataflowtask in SSIS)
                    Executable exec = package.Executables.Add("STOCK:PipelineTask");
                    TaskHost taskHost = exec as TaskHost;
                    MainPipe dataFlowTask = taskHost.InnerObject as MainPipe;
 
                    /* ############################################ */
                    /* ADD THE VFP SOURCE COMPONENT TO THE DATAFLOW */
                    /* ############################################ */
 
                    //Creates the connection to the FoxPro dbf file
                    ConnectionManager cm = package.Connections.Add("OLEDB");
                    cm.Name = "FoxPro Connection Manager";
                    cm.ConnectionString = "Data Source=" + filepath + ";Provider=VFPOLEDB.1;Extended Properties=dBase 5.0;Collating Sequence=MACHINE;";//@"Provider=VFPOLEDB.1; DataSource=" + filepath + ";Extended Properties=dBase 5.0; Collating Sequence=general";                    
 
                    
                    //Creates the source component and instantiates it
                    IDTSComponentMetaData90 sourceDataFlowComponent = dataFlowTask.ComponentMetaDataCollection.New();
                    sourceDataFlowComponent.Name = "Visual Fox Pro DBF Source";
                    sourceDataFlowComponent.ComponentClassID = "{2C0A8BE5-1EDC-4353-A0EF-B778599C65A0}";
                    CManagedComponentWrapper instance = sourceDataFlowComponent.Instantiate();
                    instance.ProvideComponentProperties();
 
                    //Specifies the connection manager
                    if (sourceDataFlowComponent.RuntimeConnectionCollection.Count > 0)
                    {
                        sourceDataFlowComponent.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(package.Connections[0]);
                        sourceDataFlowComponent.RuntimeConnectionCollection[0].ConnectionManagerID = package.Connections[0].ID;
                    }
 
                    //Set custom properties
                    instance.SetComponentProperty("AccessMode", 2);
                    instance.SetComponentProperty("SqlCommand", "select * from '" + dbfFile + "'");
                    instance.SetComponentProperty("DefaultCodePage", 1252);
                    instance.SetComponentProperty("AlwaysUseDefaultCodePage", false);
                    instance.SetComponentProperty("CommandTimeout", 0);
 
                    //Reinitialize the metadata (not sure this is required - will test)
                    instance.AcquireConnections(null);
                    instance.ReinitializeMetaData();
 
                    //Initialize and output Column
                    IDTSExternalMetadataColumn90 exOutColumn;
 
                    foreach (IDTSOutputColumn90 outColumn in sourceDataFlowComponent.OutputCollection[0].OutputColumnCollection)
                    {
                        exOutColumn = sourceDataFlowComponent.OutputCollection[0].ExternalMetadataColumnCollection[outColumn.Name];
                        instance.MapOutputColumn(sourceDataFlowComponent.OutputCollection[0].ID, outColumn.ID, exOutColumn.ID, true);
                    }
                    instance.ReleaseConnections();
 
                    /* ############################################ */
                    /* ADD TRANSFORMATION COMPONENT TO THE DATAFLOW */
                    /* ############################################ */
 
                    //Creates and configures the transformation component
                    IDTSComponentMetaData90 transformationDataFlowComponent = dataFlowTask.ComponentMetaDataCollection.New();
                    transformationDataFlowComponent.Name = "Data Transformaion Component";
                    //transformationDataFlowComponent.ComponentClassID = "DTSTransform.DataConvert.1";
                    transformationDataFlowComponent.ComponentClassID = "{C3BF62C8-7C5C-4F85-83C3-E0B6F6BE267C}";
                    CManagedComponentWrapper transformationInstance = transformationDataFlowComponent.Instantiate();
                    transformationInstance.ProvideComponentProperties();
                    //transformationInstance.SetComponentProperty("ValidateExternalMetadata", true);
 
                    //Create the path from source to transformation
                    IDTSPath90 fPath = dataFlowTask.PathCollection.New();
                    fPath.AttachPathAndPropagateNotifications(sourceDataFlowComponent.OutputCollection[0], transformationDataFlowComponent.InputCollection[0]);
                    transformationInstance.AcquireConnections(null);
                    transformationInstance.ReinitializeMetaData();
                    //Get the output collection
                    IDTSOutput90 output = transformationDataFlowComponent.OutputCollection[0];
 
                    //Create the input columns for the transformation component
                    IDTSInput90 input;
                    IDTSVirtualInput90 vInput;
                    //Get the destinations default input and virtual input
                    input = transformationDataFlowComponent.InputCollection[0];
                    vInput = input.GetVirtualInput();
 
                    //Iterate through the virtual input column collection
                    foreach (IDTSVirtualInputColumn90 vColumn in vInput.VirtualInputColumnCollection)
                    {
                        //set it so that we can read every output column
                        IDTSInputColumn90 inputColumn = transformationInstance.SetUsageType(input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READONLY);
 
                        //Transform the string DataTypes from DT_STR, DT_TEXT to UNICODE STRING TYPES DT_WSTR and DT_NTEXT - we're only transforming 6 columns
                        //so the transformLineageIDs takes the 6 new lineage IDs then adds the original LineageIDs of the others - we end up with 117 as before
                        switch (inputColumn.DataType)
                        {
                            case Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_STR:
                                IDTSOutputColumn90 outputColumn = transformationInstance.InsertOutputColumnAt(output.ID, output.OutputColumnCollection.Count, vColumn.Name, "Sql server compatible conversion column");
                                transformationInstance.SetOutputColumnDataTypeProperties(output.ID, outputColumn.ID, Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_WSTR, vColumn.Length, vColumn.Precision, vColumn.Scale, 0);
                                outputColumn.CustomPropertyCollection[0].Value = inputColumn.LineageID;
                                transformLineageIDs[outputColumn.LineageID] = outputColumn.Name;
                                break;
                            case Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_TEXT:
                                IDTSOutputColumn90 outputColumn1 = transformationInstance.InsertOutputColumnAt(output.ID, output.OutputColumnCollection.Count, vColumn.Name, "Sql server compatible conversion column");
                                transformationInstance.SetOutputColumnDataTypeProperties(output.ID, outputColumn1.ID, Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_NTEXT, vColumn.Length, vColumn.Precision, vColumn.Scale, 0);
                                outputColumn1.CustomPropertyCollection[0].Value = inputColumn.LineageID;
                                transformLineageIDs[outputColumn1.LineageID] = outputColumn1.Name;
                                break;
                            default:
                                transformLineageIDs[inputColumn.LineageID] = inputColumn.Name;
                                /* ############# VERY IMPORTANT - TO REMOVE OTHER COLUMNS FROM THE INPUT SET DTSUsageType to UT_IGNORED the columns will then be removed from the input collection. ##############*/
                                inputColumn = transformationInstance.SetUsageType(input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_IGNORED);                                
                                break;
                        }
                    }
                    //Release the connection
                    transformationInstance.ReleaseConnections();
 
                    /* #################################################### */
                    /* NOW ADD OUR DERIVED COLUMN COMPONENT TO THE DATAFLOW */
                    /* #################################################### */
 
                    IDTSComponentMetaData90 derived = dataFlowTask.ComponentMetaDataCollection.New();
                    derived.Name = "Derived Column Component";
                    derived.ComponentClassID = "{9CF90BF0-5BCC-4C63-B91D-1F322DC12C26}";
                    CManagedComponentWrapper derivedColumns = derived.Instantiate();
                    derivedColumns.ProvideComponentProperties();
                    derived.InputCollection[0].ExternalMetadataColumnCollection.IsUsed = false;
                    derived.InputCollection[0].HasSideEffects = false;
 
                    //Create the path from transformation to derived columns
                    IDTSPath90 dPath = dataFlowTask.PathCollection.New();
                    dPath.AttachPathAndPropagateNotifications(transformationDataFlowComponent.OutputCollection[0], derived.InputCollection[0]);
                    derivedColumns.AcquireConnections(null);
                    derivedColumns.ReinitializeMetaData();
 
                    
                    //Get the output collection
                    IDTSOutput90 outputd = derived.OutputCollection[0];
 
                    //Create the input columns for the transformation component
                    IDTSInput90 dInput;
                    IDTSVirtualInput90 vdInput;
                    //Get this components default input and virtual input                    
                    dInput = derived.InputCollection[0];
                    dInput.ErrorRowDisposition = DTSRowDisposition.RD_NotUsed;
                    dInput.ErrorOrTruncationOperation = "";
 
                    vdInput = dInput.GetVirtualInput();
                    
 
                    //Get the output collection
                    IDTSOutput90 dOutput = derived.OutputCollection[0];
 
                    //TAKES CARE OF THE DUPLICATE INPUT COLUMNS
                    foreach (IDTSVirtualInputColumn90 vColumn in vdInput.VirtualInputColumnCollection)
                    {                        
                        //we use the transformLineageIDs so that we have the new set of 117 columns minus the 6 non-unicode columns (these are the ones that were transformed into new columns)
                        //if you examine the virtualinputcolumncollection you'll see we have 123, 6 more than in the Data transform column collection (117 + 6)
                        if (transformLineageIDs.ContainsKey(vColumn.LineageID))
                        {
                            
                            //Give me an output column
                            IDTSOutputColumn90 outputColumn;
                            //Give me a CustomProperty
                            IDTSCustomProperty90 property;                            
                            switch (vColumn.Name.ToUpper())
                            {
                                //this should be OO, but in first instance just get it to work!!!
                                case "GROUP":
                                    //set it so we can read the columns
                                    derivedColumns.SetUsageType(dInput.ID, vdInput, vColumn.LineageID, DTSUsageType.UT_READONLY);
                                    //for each new column remember to set new column name
                                    outputColumn = derived.OutputCollection[0].OutputColumnCollection.New();
                                    outputColumn.SetDataTypeProperties(Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_WSTR, 1, vColumn.Precision, vColumn.Scale, 0);
                                    /* VERY IMPORTANT - SET THE TRUNCATION AND ERROR ROW DISPOSITION - IF YOU DON'T THE PACKAGE WILL FAIL 
                                       DO THIS FOR ALL DERIVED COLUMNS YOU ARE ADDING
                                       ALSO TELL THE PROPERTIES THAT THE EXPRESSION CONTAINS AN ID */
                                    outputColumn.TruncationRowDisposition = DTSRowDisposition.RD_FailComponent;
                                    outputColumn.ErrorRowDisposition = DTSRowDisposition.RD_FailComponent;
                                    outputColumn.ErrorOrTruncationOperation = "Computation";                                    
                                    outputColumn.Name = "status";
                                    property = outputColumn.CustomPropertyCollection.New();
                                    property.Name = "Expression";
                                    property.Value = "[SUBSTRING](#" + vColumn.LineageID + ",5,1)";
                                    property.ContainsID = true;
                                    property = outputColumn.CustomPropertyCollection.New();
                                    property.Name = "FriendlyExpression";
                                    property.Value = "SUBSTRING(GROUP,5,1)";
                                    property.ContainsID = true;
                                    property.ExpressionType = DTSCustomPropertyExpressionType.CPET_NOTIFY;
                                    derivedColumnLineageIDs[outputColumn.LineageID] = outputColumn.Name;
 
                                    //for each new column remember to set new column name
                                    outputColumn = derived.OutputCollection[0].OutputColumnCollection.New();
                                    outputColumn.SetDataTypeProperties(Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_I4, 0, 0, 0, 0);
                                    outputColumn.TruncationRowDisposition = DTSRowDisposition.RD_FailComponent;
                                    outputColumn.ErrorRowDisposition = DTSRowDisposition.RD_FailComponent;
                                    outputColumn.ErrorOrTruncationOperation = "Computation";
                                    outputColumn.Name = "class";
                                    property = outputColumn.CustomPropertyCollection.New();
                                    property.Name = "Expression";
                                    property.Value = "(DT_I4)[SUBSTRING](#" + vColumn.LineageID + ",9,[FINDSTRING]([RIGHT](#" + vColumn.LineageID + ",[LEN](#" + vColumn.LineageID + ") - 9),\"_\",1))";
                                    property.ContainsID = true;
                                    property = outputColumn.CustomPropertyCollection.New();
                                    property.Name = "FriendlyExpression";
                                    property.Value = "(DT_I4)SUBSTRING(GROUP,9,FINDSTRING(RIGHT(GROUP,LEN(GROUP) - 9),\"_\",1))";
                                    property.ContainsID = true;
                                    property.ExpressionType = DTSCustomPropertyExpressionType.CPET_NOTIFY;
                                    derivedColumnLineageIDs[outputColumn.LineageID] = outputColumn.Name;
 
                                    //for each new column remember to set new column name
                                    outputColumn = derived.OutputCollection[0].OutputColumnCollection.New();
                                    outputColumn.SetDataTypeProperties(Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_WSTR, vColumn.Length, vColumn.Precision, vColumn.Scale, 0);
                                    outputColumn.TruncationRowDisposition = DTSRowDisposition.RD_FailComponent;
                                    outputColumn.ErrorRowDisposition = DTSRowDisposition.RD_FailComponent;
                                    outputColumn.ErrorOrTruncationOperation = "Computation";
                                    outputColumn.Name = "employer";
                                    property = outputColumn.CustomPropertyCollection.New();
                                    property.Name = "Expression";
                                    property.Value = "[FINDSTRING](#" + vColumn.LineageID + ",\"_ER\",1) == 0 ? NULL(DT_WSTR,50) : [SUBSTRING](#" + vColumn.LineageID + ",[FINDSTRING](#" + vColumn.LineageID + ",\"_ER\",1) + 3,[FINDSTRING]([RIGHT](#" + vColumn.LineageID + ",[LEN](#" + vColumn.LineageID + ") - ([FINDSTRING](#" + vColumn.LineageID + ",\"_ER\",1) + 3)),\"_\",1))";
                                    property.ContainsID = true;
                                    property = outputColumn.CustomPropertyCollection.New();
                                    property.Name = "FriendlyExpression";
                                    property.Value = "FINDSTRING(GROUP,\"_ER\",1) == 0 ? NULL(DT_WSTR,50) : SUBSTRING(GROUP,FINDSTRING(GROUP,\"_ER\",1) + 3,FINDSTRING(RIGHT(GROUP,LEN(GROUP) - (FINDSTRING(GROUP,\"_ER\",1) + 3)),\"_\",1))";
                                    property.ContainsID = true;
                                    property.ExpressionType = DTSCustomPropertyExpressionType.CPET_NOTIFY;
                                    derivedColumnLineageIDs[outputColumn.LineageID] = outputColumn.Name;
 
                                    //for each new column remember to set new column name
                                    outputColumn = derived.OutputCollection[0].OutputColumnCollection.New();
                                    outputColumn.SetDataTypeProperties(Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_I4, 0, 0, 0, 0);
                                    outputColumn.TruncationRowDisposition = DTSRowDisposition.RD_FailComponent;
                                    outputColumn.ErrorRowDisposition = DTSRowDisposition.RD_FailComponent;
                                    outputColumn.ErrorOrTruncationOperation = "Computation";
                                    outputColumn.Name = "age";
                                    property = outputColumn.CustomPropertyCollection.New();
                                    property.Name = "Expression";
                                    property.Value = "[FINDSTRING](#" + vColumn.LineageID + ",\"_AG\",1) == 0 ? NULL(DT_I4) : (DT_I4)[SUBSTRING](#" + vColumn.LineageID + ",[FINDSTRING](#" + vColumn.LineageID + ",\"_AG\",1) + 3,[FINDSTRING]([RIGHT](#" + vColumn.LineageID + ",[LEN](#" + vColumn.LineageID + ") - ([FINDSTRING](#" + vColumn.LineageID + ",\"_AG\",1) + 3)),\"_\",1))";
                                    property.ContainsID = true;
                                    property = outputColumn.CustomPropertyCollection.New();
                                    property.Name = "FriendlyExpression";
                                    property.Value = "FINDSTRING(GROUP,\"_AG\",1) == 0 ? NULL(DT_I4) : (DT_I4)SUBSTRING(GROUP,FINDSTRING(GROUP,\"_AG\",1) + 3,FINDSTRING(RIGHT(GROUP,LEN(GROUP) - (FINDSTRING(GROUP,\"_AG\",1) + 3)),\"_\",1))";
                                    property.ContainsID = true;
                                    property.ExpressionType = DTSCustomPropertyExpressionType.CPET_NOTIFY;
                                    derivedColumnLineageIDs[outputColumn.LineageID] = outputColumn.Name;
 
                                    //for each new column remember to set new column name
                                    outputColumn = derived.OutputCollection[0].OutputColumnCollection.New();
                                    outputColumn.SetDataTypeProperties(Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_I4, 0, 0, 0, 0);
                                    outputColumn.TruncationRowDisposition = DTSRowDisposition.RD_FailComponent;
                                    outputColumn.ErrorRowDisposition = DTSRowDisposition.RD_FailComponent;
                                    outputColumn.ErrorOrTruncationOperation = "Computation";
                                    outputColumn.Name = "individual";
                                    property = outputColumn.CustomPropertyCollection.New();
                                    property.Name = "Expression";
                                    property.Value = "[FINDSTRING](#" + vColumn.LineageID + ",\"_\",3 + ([FINDSTRING](#" + vColumn.LineageID + ",\"_ER\",1) == 0 ? 0 : 1) + ([FINDSTRING](#" + vColumn.LineageID + ",\"_AG\",1) == 0 ? 0 : 1)) == 0 ? NULL(DT_I4) : (DT_I4)[SUBSTRING](#" + vColumn.LineageID + ",1 + [FINDSTRING](#" + vColumn.LineageID + ",\"_\",2 + ([FINDSTRING](#" + vColumn.LineageID + ",\"_ER\",1) == 0 ? 0 : 1) + ([FINDSTRING](#" + vColumn.LineageID + ",\"_AG\",1) == 0 ? 0 : 1)),-1 + [FINDSTRING](#" + vColumn.LineageID + ",\"_\",3 + ([FINDSTRING](#" + vColumn.LineageID + ",\"_ER\",1) == 0 ? 0 : 1) + ([FINDSTRING](#" + vColumn.LineageID + ",\"_AG\",1) == 0 ? 0 : 1)) - [FINDSTRING](#" + vColumn.LineageID + ",\"_\",2 + ([FINDSTRING](#" + vColumn.LineageID + ",\"_ER\",1) == 0 ? 0 : 1) + ([FINDSTRING](#" + vColumn.LineageID + ",\"_AG\",1) == 0 ? 0 : 1)))";
                                    property.ContainsID = true;
                                    property = outputColumn.CustomPropertyCollection.New();
                                    property.Name = "FriendlyExpression";
                                    property.Value = "FINDSTRING(GROUP,\"_\",3 + (FINDSTRING(GROUP,\"_ER\",1) == 0 ? 0 : 1) + (FINDSTRING(GROUP,\"_AG\",1) == 0 ? 0 : 1)) == 0 ? NULL(DT_I4) : (DT_I4)SUBSTRING(GROUP,1 + FINDSTRING(GROUP,\"_\",2 + (FINDSTRING(GROUP,\"_ER\",1) == 0 ? 0 : 1) + (FINDSTRING(GROUP,\"_AG\",1) == 0 ? 0 : 1)),-1 + FINDSTRING(GROUP,\"_\",3 + (FINDSTRING(GROUP,\"_ER\",1) == 0 ? 0 : 1) + (FINDSTRING(GROUP,\"_AG\",1) == 0 ? 0 : 1)) - FINDSTRING(GROUP,\"_\",2 + (FINDSTRING(GROUP,\"_ER\",1) == 0 ? 0 : 1) + (FINDSTRING(GROUP,\"_AG\",1) == 0 ? 0 : 1)))";
                                    property.ContainsID = true;
                                    property.ExpressionType = DTSCustomPropertyExpressionType.CPET_NOTIFY;
                                    derivedColumnLineageIDs[outputColumn.LineageID] = outputColumn.Name;
                                    break;
                                case "PERIOD":
                                    //set it so we can read the columns
                                    derivedColumns.SetUsageType(dInput.ID, vdInput, vColumn.LineageID, DTSUsageType.UT_READONLY);
                                    outputColumn = derived.OutputCollection[0].OutputColumnCollection.New();
                                    outputColumn.SetDataTypeProperties(Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_I4, 0, 0, 0, 0);
                                    outputColumn.TruncationRowDisposition = DTSRowDisposition.RD_FailComponent;
                                    outputColumn.ErrorRowDisposition = DTSRowDisposition.RD_FailComponent;
                                    outputColumn.ErrorOrTruncationOperation = "Computation";
                                    outputColumn.Name = "year";
                                    property = outputColumn.CustomPropertyCollection.New();
                                    property.Name = "Expression";
                                    property.Value = "(DT_I4)[RIGHT](#" + vColumn.LineageID + ",[LEN](#" + vColumn.LineageID + ") - 4)";
                                    property.ContainsID = true;
                                    property = outputColumn.CustomPropertyCollection.New();
                                    property.Name = "FriendlyExpression";
                                    property.Value = "(DT_I4)RIGHT(PERIOD,LEN(PERIOD) - 4)";
                                    property.ContainsID = true;
                                    property.ExpressionType = DTSCustomPropertyExpressionType.CPET_NOTIFY;
                                    derivedColumnLineageIDs[outputColumn.LineageID] = outputColumn.Name;
                                    break;
                                case "TRAN_ORIG":
                                    //set it so we can read the columns
                                    derivedColumns.SetUsageType(dInput.ID, vdInput, vColumn.LineageID, DTSUsageType.UT_READONLY);
                                    outputColumn = derived.OutputCollection[0].OutputColumnCollection.New();
                                    outputColumn.SetDataTypeProperties(Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_I4, 0, 0, 0, 0);
                                    outputColumn.TruncationRowDisposition = DTSRowDisposition.RD_FailComponent;
                                    outputColumn.ErrorRowDisposition = DTSRowDisposition.RD_FailComponent;
                                    outputColumn.ErrorOrTruncationOperation = "Computation";
                                    outputColumn.Name = "tranche";
                                    property = outputColumn.CustomPropertyCollection.New();
                                    property.Name = "Expression";
                                    property.Value = "#" + vColumn.LineageID;
                                    property.ContainsID = true;
                                    property = outputColumn.CustomPropertyCollection.New();
                                    property.Name = "FriendlyExpression";
                                    property.Value = "TRAN_ORIG";
                                    property.ContainsID = true;
                                    property.ExpressionType = DTSCustomPropertyExpressionType.CPET_NOTIFY;
                                    derivedColumnLineageIDs[outputColumn.LineageID] = outputColumn.Name;
                                    break;
                                //What we are doing here is basically ditching any columns from the input that we don't want!!
                                //This is done by not adding them to the derivedColumnLineageIDs collection.
                                /* ############# VERY IMPORTANT - WE ALSO REMOVE OTHER COLUMNS FROM THE INPUT Collection by SETTING DTSUsageType to UT_IGNORED  ##############*/
                                case "PRODUCT":                                    
                                    derivedColumns.SetUsageType(dInput.ID, vdInput, vColumn.LineageID, DTSUsageType.UT_IGNORED);
                                    break;
                                case "PURPOSE":                                    
                                    derivedColumns.SetUsageType(dInput.ID, vdInput, vColumn.LineageID, DTSUsageType.UT_IGNORED);
                                    break;
                                case "DISC_RATE":                                    
                                    derivedColumns.SetUsageType(dInput.ID, vdInput, vColumn.LineageID, DTSUsageType.UT_IGNORED);
                                    break;
                                case "TIME":
                                    derivedColumns.SetUsageType(dInput.ID, vdInput, vColumn.LineageID, DTSUsageType.UT_IGNORED);
                                    break;
                                case "T_FROM":
                                    derivedColumns.SetUsageType(dInput.ID, vdInput, vColumn.LineageID, DTSUsageType.UT_IGNORED);
                                    break;
                                case "T_TO":
                                    derivedColumns.SetUsageType(dInput.ID, vdInput, vColumn.LineageID, DTSUsageType.UT_IGNORED);
                                    break;
                                case "INPUT_VAR":
                                    derivedColumns.SetUsageType(dInput.ID, vdInput, vColumn.LineageID, DTSUsageType.UT_IGNORED);
                                    break;
                                default:
                                    //just want the lineageIDs passed through from the original to our new collection
                                    derivedColumnLineageIDs[vColumn.LineageID] = vColumn.Name;
                                    derivedColumns.SetUsageType(dInput.ID, vdInput, vColumn.LineageID, DTSUsageType.UT_IGNORED);
                                    break;
                            }
                        }
                    }                                    
                    derivedColumns.ReleaseConnections();
 
                    /* ################################################# */
                    /* NOW ADD OUR DESTINATION COMPONENT TO THE DATAFLOW */
                    /* ################################################# */
 
                    //Creates the SQL destination connection
                    ConnectionManager cm2 = package.Connections.Add("OLEDB");
                    cm2.Name = "MS SQL Server 2005 Exodus Cash Flows DB";
                    cm2.ConnectionString = @"Provider=SQLNCLI.1;Data Source=HRGLADEV07;Initial Catalog=ExodusCashFlow;Integrated Security=SSPI;Auto Translate=false;";
 
                    //Creates the SQL destination component and instantiates it
                    IDTSComponentMetaData90 destination = dataFlowTask.ComponentMetaDataCollection.New();
                    destination.Name = "SQL Server Destination";
                    destination.ComponentClassID = "{E2568105-9550-4F71-A638-B7FE42E66922}";
                    CManagedComponentWrapper destDesignTime = destination.Instantiate();
                    destDesignTime.ProvideComponentProperties();
 
                    //Specify the connection manager for the component
                    if (destination.RuntimeConnectionCollection.Count > 0)
                    {
                        destination.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(package.Connections[1]);
                        destination.RuntimeConnectionCollection[0].ConnectionManagerID = package.Connections[1].ID;
                    }
 
                    //Set custom properties
                    destDesignTime.SetComponentProperty("AccessMode", 3);
                    destDesignTime.SetComponentProperty("OpenRowset", "[ExodusCashFlow].[dbo].[Cashflow]");
                    destDesignTime.SetComponentProperty("FastLoadOptions", "TABLOCK");
                    destDesignTime.SetComponentProperty("DefaultCodePage", 1252);
                    destDesignTime.SetComponentProperty("AlwaysUseDefaultCodePage", false);
 
                    //Create the path from transformation to destination
                    IDTSPath90 path = dataFlowTask.PathCollection.New();
                    //Get the output collection of one component and send it to another
                    path.AttachPathAndPropagateNotifications(derived.OutputCollection[0], destination.InputCollection[0]);
                    destDesignTime.AcquireConnections(null);
                    destDesignTime.ReinitializeMetaData();
                    //Get the inputs and virtual inputs
                    input = destination.InputCollection[0];
                    vInput = input.GetVirtualInput();
 
                    //Now as above - we should see 130 columns in the VirtualInputColumnCollection that is:
                    //the 117 original + 6 data transformed + 7 that we derived above
                    foreach (IDTSVirtualInputColumn90 vColumn in vInput.VirtualInputColumnCollection)
                    {
                        //as before, we only want columns which are in our destination. So what we actually have in the derivedColumnLineageIDs
                        //is 114. This is 117 - 10 columns we removed in the derived(remember ones that you derive directly from are removed)
                        // + 7 the ones we created in the derived column component. This is correct amount.
                        if (derivedColumnLineageIDs.ContainsKey(vColumn.LineageID))
                        {
                            destDesignTime.SetUsageType(input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READONLY);
                        }
                        else
                        {
                            /* ############# VERY IMPORTANT - TO REMOVE OTHER COLUMNS FROM THE INPUT SET DTSUsageType to UT_IGNORED the columns will then be removed from the input collection. ##############*/
                            destDesignTime.SetUsageType(input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_IGNORED);
                        }
                    }
                    
 
                    IDTSExternalMetadataColumn90 exColumn;
                    //now loop through our destination input collection - mapping the columns to the table in the DB
                    foreach (IDTSInputColumn90 inColumn in destination.InputCollection[0].InputColumnCollection)
                    {
                        exColumn = destination.InputCollection[0].ExternalMetadataColumnCollection[inColumn.Name.ToUpper()];
                        destDesignTime.MapInputColumn(destination.InputCollection[0].ID, inColumn.ID, exColumn.ID);
                    }
                    destDesignTime.ReleaseConnections();
 
                    //Set up the logging and error handling stuff
                    ErrorEvents errors = new ErrorEvents();
                    Logging log = new Logging();
 
                    //VALIDATE THE PACKAGE
                    DTSExecResult validated = package.Validate(null, null, errors, log);
 
                    if (validated == DTSExecResult.Success)
                    {
                        long start = DateTime.Now.Ticks;
                        DTSExecResult result = package.Execute(null, null, errors, null, null);
                        long end = DateTime.Now.Ticks;
                        lblResult.Text = result.ToString() + " TIME TAKEN: " + TimeSpan.FromTicks(end - start).TotalSeconds.ToString();
                    }
                    else
                    {
                        lblResult.Text = validated.ToString();
                    }

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:
80:
81:
82:
83:
84:
85:
86:
87:
88:
89:
90:
91:
92:
93:
94:
95:
96:
97:
98:
99:
100:
101:
102:
103:
104:
105:
106:
107:
108:
109:
110:
111:
112:
113:
114:
115:
116:
117:
118:
119:
120:
121:
122:
123:
124:
125:
126:
127:
128:
129:
130:
131:
132:
133:
134:
135:
136:
137:
138:
139:
140:
141:
142:
143:
144:
145:
146:
147:
148:
149:
150:
151:
152:
153:
154:
155:
156:
157:
158:
159:
160:
161:
162:
163:
164:
165:
166:
167:
168:
169:
170:
171:
172:
173:
174:
175:
176:
177:
178:
179:
180:
181:
182:
183:
184:
185:
186:
187:
188:
189:
190:
191:
192:
193:
194:
195:
196:
197:
198:
199:
200:
201:
202:
203:
204:
205:
206:
207:
208:
209:
210:
211:
212:
213:
214:
215:
216:
217:
218:
219:
220:
221:
222:
223:
224:
225:
226:
227:
228:
229:
230:
231:
232:
233:
234:
235:
236:
237:
238:
239:
240:
241:
242:
243:
244:
245:
246:
247:
248:
249:
250:
251:
252:
253:
254:
255:
256:
257:
258:
259:
260:
261:
262:
263:
264:
265:
266:
267:
268:
269:
270:
271:
272:
273:
274:
275:
276:
277:
278:
279:
280:
281:
282:
283:
284:
285:
286:
287:
288:
289:
290:
291:
292:
293:
294:
295:
296:
297:
298:
299:
300:
301:
302:
303:
304:
305:
306:
307:
308:
309:
310:
311:
312:
313:
314:
315:
316:
317:
318:
319:
320:
321:
322:
323:
324:
325:
326:
327:
328:
329:
330:
331:
332:
333:
334:
335:
336:
337:
338:
339:
340:
341:
342:
343:
344:
345:
346:
347:
348:
349:
350:
351:
352:
353:
354:
355:
356:
357:
358:
359:
360:
361:
362:
363:
364:
365:
366:
367:
368:
369:
370:
371:
372:
373:
374:
375:
376:
377:
378:
379:
380:
381:
382:
383:
384:
385:
386:
387:
388:
389:
390:
391:
392:
393:
394:
395:
396:
397:
398:
399:
400:
401:
402:
403:
404:
405:
406:
407:
408:
409:
410:
411:
412:
413:
414:
415:
416:
417:
418:
419:
420:
421:
422:
423:
424:
425:
426:

Select all Open in new window

by: HoggZillaPosted on 2008-12-12 at 05:24:37ID: 23156939

Great! I can't wait to read the article.

20120131-EE-VQP-002

3 Ways to Join

30-Day Free Trial

For individual users
Instant access to solutions
Ask your tech questions
Start your 30-day Free Trial

Business Accounts

Perfect for organizations
Multiple users
Save over 36%
Create a Business Account

Answer for Membership

Earn free access
Showcase your knowledge
No credit card required
Sign Up to Answer

The Experts

98% positive feedback on 31,087 answers since March 2000. angeliii is a Microsoft Most Valuable Professional for his work with MS SQL Server & Develoment.

He has also proven his knowledge of Visual Basic Programming, PHP Scripting and Oracle Databases.

The Experts

97% positive feedback on 10,752 answers since July 2000. lrmoore has more than 18 years experience in the networking industry.

The six-time Mircosoft MVPs specialties include firewalls, virtual private networking, and network management.

Testimonials

"...and excellent source for support... Kind of like having your very own IT dept." Electriciansnet

Testimonials

"I was apprehensive at signing up at first. However... it has already made my life as an IT administrator much easier." JaCrews

Testimonials

"WOW! You guys have great, active, and knowledgeable people on here." moore50

Business Clients

In the Press

"If you’ve got a question... Experts Exchange can supply an answer.”

In the Press

"...an invaluable aid for both IT professionals and those who require tech support."

In the Press

"where IT professionals provide quick answers on just about any topic"

Business Account Plans

Loading Advertisement...

Question

Problem with SSIS derived column component programmatically importing data

Subscribe now for full access to Experts Exchange and get

Instant Access to this Solution

Trusted by hundreds of thousands everyday for fast, accurate and reliable tech support.

See what Experts Exchange can do for you.

Got a question?

We've got the answer.

Need individual assistance?

Our experts are ready to help.

Want to learn from the best?

Read articles from industry experts.

Working on a long term project?

Store your work and research.

What Makes Experts Exchange Unique?

Trusted by the world's most respected brands.

Faithfully serving IT professionals since 1996.

Related Solutions

Free Tech Articles

Cloud Class Webinars

Join the Community

Give a Little. Get a Lot.

Answers

3 Ways to Join

The Experts

The Experts

Testimonials

Testimonials

Testimonials

Business Clients

Business Clients

In the Press

In the Press

In the Press