Mappings question in OLE DB Destination

Hi,

I have a situation where I want to map a column from a flat file to TWO columns in a table.

However, in the mappings tab, you can only select the "Input Column" once. Once a column has been used, it no longer appears in the drop down list.

I am wondering if there's a way to override this behavior, and if not, what is the best way to handle this type of situation?

I have added an EXECUTE SQL task to update the second column with the inserted column values, but I would like to know if the default mapping behavior can be changed, as it seems so limited.

Thanks

Add a derived column right before the destination and select the column that you want to use more than once and drag it to the expression box. Adjust the name of the new column accordingly.

Then in the OLE DB Destination you can select the column you just added.

Feel free to suggest new features over at http://connect.microsoft.com/sqlserver/feedback|||

Great, thanks

sql

Mappings in the packages

Hi all,

Is it possible to get information of the mappings availabe in the packages?, like the column mappings in the OLE Destination control, column mappings in the merge join control etc.,. I want the information of source and destination in the mappings(name of the DB, table). I want that to be in some file like excel file. Inform me is there a way to get it?

Thanks in advance,

Saravanan.W.S

Currently there is not way to export that kind of mapping metadata to Excel. Depending on your needs, however, you may find the SQL Server 2005 Business Intelligence Metadata Samples Toolkit useful. You can download it here ...

http://www.microsoft.com/downloads/details.aspx?FamilyID=11DAA4D1-196D-4F2A-B18F-891579C364F4&displaylang=en

Donald

Mapping Source and Destination columns

can somebody show an example of how to map source and destination columns when uploading a file to sql server?

Also, please send me the mapping when i want to map source to different destination columns.

I think you have to to be a bit more specific to get answers that will help you.

Mapping columns in custom Destination component

I am working on a destination component where the columns in the destination are already set. I want to give the user the option to map columns, similar to the way the Excel destination component does it. So, the Available Input Columns could be mapped to Available Destination Columns. The Available Input Columns would come from a connection from some other component (either a source or a transformation component), and the Available Destination Columns would be generated from the Data Source.

Is there a way to do this without creating a custom ui?

Yes, you can get this for free in the advanced UI, if you store your Available Destination Columns to the ExternalMetadataColumnCollection on the destination input and set IsUsed flag on this collection to true.

HTH.

|||Man, I suspected it had to do with the external metadata columns, but I couldn't get them to show in the UI. Setting the IsUsed property to "true" was the answer. Thanks a lot Bob.

Mapping Columns Automatically?

New to SSIS...

I created a new package with a source and destination and manually created the output column with data type, etc. Works. The issue is say the table has 200 columns to export.. I dont want to create these by hand. How can I just say export them all to csv format and not have to specify and map each and every column?

Use the Export Data Wizard in SSMS.

-Jamie

|||Thats fine and dandy when starting from scratch. But if you have spent a lot of time building scripts and other actions in an existing package... it seems that it should be simple to add all columns to an existing text export. This seems like it would be such a common issue there has to be a solution.|||

You could replace your existing source adapter with a new one. The default behaviour is to select all columns which by the sound of it is what you want.

The new columns will automatically appear in the metadata of downstream components.

-Jamie

|||Thanks.. I will try that and see how it goes.

Map One generic Input column to multiple Destination column

I have a stored proc I am updating in an OLEDB Command from the results of a Transform Script Component. The Stored Proc has over 65 input parameters, most of them have a NULL passed in. I dont want to create output columns in the Transform Script Component for all of them to map them from the "Available Input Columns" to "Available Destination Columns".

I want to create 3 or 4 generic Output columns for their data type - say IntegerOutput (datatype Int), DateTimeOut (datatype datetime) and so on. The I want to map these generic columns in the OLEDB Command as Available Input Columns" to multiple "Available Destination Columns" - wherever the datatype matches the input column.

But the OLEDB Command Column Mappings let me map One to One only. This will create a huge and unnecessary workload for me to develop and maintain - when I tell you I have 3 such stored procedures, all of whose interfaces are exactly same and for which I can create similar Output columns in the Transform Script Component.

So how do I go about doing this the smart way?

thanks in advance!

Hi,

You can use "Copy Column" transformation component to copy one input column to multiple output columns. If you have to perform some computing between original and new columns, you can use "Derive Column" transformation.

Jean-Pierre Riehl

http://blog.djeepy1.net

http://www.bewise.fr

|||Sorry not very elegant, this is more work than creating all the output columns one by one. I want to create one DataType_NULL Column which I want to reuse to map to the destination columns.

|||For what you are describing I would probably just call the stored procedure from inside the script component. As you have seen, the OLE Command doesn't really support this, so script, Copy Column, or Derived Column are the only way to do this that I am aware of.