.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Dynamic Column mapping by using Reference Mapping table

Posted By:      Posted Date: May 22, 2011    Points: 0   Category :

Please tell me if I can create SSIS package for the below requirement:

I have source data in 2 excel files. Data from both these excel files should be loaded to the same single Fact table. The column names in excel files and table are not same. I have a Reference table which has the column mappings between excel and Fact Table.
I have to refer this Reference Tabel for column mappings, plus i have to add some derived columns (Created_Date) to load the Fact_Table.

I have given a sample data structure below:

Source Data

OrderNumber     OrderQuantity     OrderDate
Order10001             100               01-01-2011

CustomerNumber      CustomerName     CustomerAddress
Customer0001          CCPrivateLtd             India

View Complete Post

More Related Resource Links

The external column "CLIENT" (2639) has no mapping to the XML schema

Hi, I am having validation errors when moving between test and production servers.  I have a package which is reading and XML file and loading the data into staging tables.  The XML source is configured to use a variable for the file name (which is getting populated through a foreach loop.  The default value points to a file that was on the production server when the package was created).  The XSD is stored on a network share.  This all looks fine in test.  When I deploy it to production there are no validation errors, but when I run it, I get errors about the first few columns in the schema complaining: "The external column "<Column Name>" (2639) has no mapping to the XML schema" If I open the package while pointing to the production server, I can double click the XML Source and clear the errors.  I can then redeploy it and the package will run without issue in production.  However, if I open it back up and the package is again pointing to the test server, these metadata errors come up again.  I have rebuilt this component and the XSD a dozen times, thinking that the component was corrupted, but nothing seems to help.  I even rebuilt the entire dataflow.  Any ideas on to make this transition from test to production more smooth?  Is there a more reliable way that I can configure t

Retrieving users from mapping/pivot table

I have three tables.CustomProfileUserID (pk)DisplayNameCompanyIDTeamsTeamRecordID (Pk)TeamIDCompanyIDEmployeesInTeamsUserIDTeamIDI want to assign employees to teams via a gridview control with a templated checkbox.I have a dropdownlist control to select the TeamID.I am having problems with the select statement.The gridview needs to select employees that are not assigned to any teams as well as assigned to other teams. An employee can be assigned to many teams.What I have been trying is:cp.UserID, cp.DisplayName, et.TeamIntID, cp.CompanyID FROM            EmployeesInTeams AS et CROSS JOIN                          CustomProfile AS cp WHERE        (et.TeamIntID <> @TeamIntID) AND (NOT EXISTS                              (SELECT        TeamIntID, UserID                                FROM            EmployeesInTeams                                WHERE        (cp.UserID = UserID))) AND (cp.CompanyID = @companyid)Select cp.UserID, cp.DisplayName, et.TeamIntID, cp.C

Table mapping

 Hi friend, I want to know what is the use of table mappings.At what scenario we will use table mapping. Thanks  

Pivot table query dynamic column.


Hello All,


I want to export my data to excel sheet. The logic to export data in excel is clear.

Now my query is I have 3 tables and I want to generate the data jst like below.



Company Name

Company Email


Create Date


Fax No














And so on.....


The data will come from 3 tables I.e


  1. Master Table for Company

  2. Data Flows in SSIS - Mapping Multiple source tables to Destination table **Newb question**

    Hi I am new to SSIS and had a basic question. I have around 30+ tables in a db that needs to be migrated to a newer schema in the DB. The data flow task seems to be ideally suited for my requirement. My question is do I need to create 30+ different data flow tasks for this which will get executed one after the other or is there a better way to migrate large number of tables. Also how are referential constraints taken care of during such migration. Thanks and Regards, Ganesh Ranganathan
    Ganesh Ranganathan
    [Please mark the post as answer if it answers your question]

Mapping custom values and columns to destination table **Newb question**



I am copying data from one source to destination SQL CE table using the data flow task. I have two extra requirements which I am not sure how to achieve.

1.)  One source column needs to be mapped to two destination columns. In the column mapping dropdown the column name disappears after mapping it to one destination column.

2) some custom values for columns need to be added, which are not present in source. For e.g. a default value for some destination columns which is not present.

Any pointers would be extremely appreciated. Thanks,

Ganesh Ranganathan
[Please mark the post as answer if it answers your question]

Programmatically Mapping Custom SharePoint Column on Non-browser Enabled InfoPath Form


I have an InfoPath form, which, due to control requirements, is not browser enabled. It is being programmatically added as a content type to SharePoint (MOSS 2007). All users have Office 2007 installed so they will be able to open the form. I also have a custom multi-line column in the SharePoint document library. I can manually publish the form and map the column to a field of the form, but I need to be able to do this programmatically.

We are currently deploying the forms by modifying the publish url in the xsf during install. We have tried to modify the site coulmn GUID in the template xsf during install without success.

Does anyone have any ideas as to how we can programmatically promote the InfoPath field so it is visible in SharePoint?

Mapping table without primary key in Nhibernate


How can we map a table without having primary key in nHibernate.

I have a table which does not contain any primary key, so how can i map this table in .hbm.xml file.

Please advice.

Mapping of CSV files to sql table using execute sql task


I need to exctract data from multiple CSV files and store the data in SQL database table.

structure of data is not in same in CSV files. Number of columns and order of columns is different in CSV files. Same way, structure for all the destination database tables will not be the same. It will be according to the corresponding CSV file.

I am using execute sql task approach. I mean to use a foreach loop to loop through csv files and put an execute sql task inside foreach loop and make a BCP command dynamically to load data from csv file to sql server destination table. As using BCP we don't need to worry about structure of data.

But, I am seeing few issues:

Execute SQL task runs the sql query. Then, how can we create a common query to get the data from csv files.


SharePoint custom column not appearing in the add mapping popup


Have different Custom Columns created at various level:

  • Site Column
  • Doc. Lib
  • Content type

Added the above in document library.

The doc. lib is also populated with relevant data.

  1. created a content source for the Web App
  2. Did full crawl
  3. went to create managed property

But custom columns not appearing in the add mapping popup except the one used in Content Type?






Derived column fails when mapping


Hi All,

I have my OLEDB source and I have attached it to a "Derived Column" and then attached this to "Import Column". I intorduced the Derived Column because linking straight to the Import column from the source errored with unsupported types when trying to map.

I have altered 1 column without problem, but I have a second column where the source type is double-precision float [DT_R8]

I have derived the column and told it to replace. The expression I have used is: (DT_STR,15,1252)(col1)

No complaints from the Derived Column Transformation editor.

When I go to the "Import Column" and select my derived col1, it still says it's an unsupported data type and must be either DT_STR or DT_WSTR ???? 

Dynamic Column filter on table variable or temp table



I created a  procedure which first populates a table variable /temp table with data. Then based on input parameter (say, @ColumnNames) I want to filter the result set.

Here is the snippet I have created, -

create PROCEDURE [dbo].[GetAlerts]
	@roleId varchar(20),
	@fields sysname
declare @temp table(
	ID int,
	role_id varchar(20),
	CRITICALINFO varchar(1000));

    insert into @temp select * from dbo.commonalertdata where role_id= @roleId);

	declare @sql varchar(300);
	set @sql='select ' + @fields + ' from @temp';
	exec dbo.sp_executesql @sql; 

Then i tried to run it as follows, -


And It gave the following error.

Msg 1087, Level 15, State 2, Line 1

Must declare the table variable "@temp".

Now, I tried using table variable or temp table or even UDF with no good. Can anybody help me in getting dynamic columns from a calculated table valued result set like temp table / table variable / inline table valued UDF?

mapping webpart control with lookup column on pages library


I have created two look up column in my pages library mapping with two list say c1 and c2.In the front end I  have added a webpart which renders a treeview and display the content of c1 and c2 list.
Now when the user select or modify any option in the tree view and publish the page then the values selcted should be updated in look up columns created in library.

I wrote the updated and updating event handler but in that I am unable to get the treeview control.

So can any one tell how can I map the selection in treeview to the look up column created in pages library

Display column from child table. Possible ?



I'am using dnamic entity with EF4. On a list page of a table, I would like to display a column containing information from a child table of the current element. Sample :

Order List :

Order Data | Required Date | Shipped Date | etc... | ... | Customer Name (foreign key with tostring() method override) | Customer Postal Code (Column that I want to add) |

I don't known how to do that. Is it possible ? Maybe I have to to create my own Metadata proxy that will add dynamicly a column on the MetaColumn list of the table.

Does someone have an easier or better idea ?

Thank you for any help.

Toolbox: Distributed Caching, Object-Object Mapping, Blogs and More


This column covers Web application performance with distributed caching, a utility for object-object mapping, and a look at interesting blogs.

Scott Mitchell

MSDN Magazine September 2009

Netting C++: Mapping Templates to Generics


In this installment, Stanley Lippman continues to port his ISO-C++ Text Query Language application to the Microsoft .NET Framework and C++/CLI.

Stanley Lippman

MSDN Magazine April 2007

Netting C++: Mapping Native C++ to the Common Type System


This month Stanley Lippman begins translating the Text Query Language Query class hierarchy from C++ to the .NET Common Type System.

Stanley B. Lippman

MSDN Magazine February 2007

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend