locked
How can i get Mapping Information and Column Information? RRS feed

  • Question

  • Dear all,

    Normally SSIS use for DB to DB or File to DB or vice verse. I am working on SSIS Project having Requirement like we have to use Business layer of product to insert Data into DB. No direct input into DB allow.

    I have choose to make Custom Destination Component more like OLE DB Destination, which take one input, set some properties, do mappings of source and destination column and insert into DB using Business Layer of course based on mapping details.

    Design Time:
    I am done with UI, use Advance Editor for same. No Custom UI added. I have added my destination columns into ExternalMetadataColumnCollection to display "Available Destination Columns". Now user can do mappings based on requirements.

    Columns are added from "Acquire Connection".

    RunTime:
    When i Run Package.. Problems aries..

    - How do i get Mapping Details?

    - ExternalMetadataColumnCollection does not have any column which was added previously. How can i access those information?

    Unsuccessful Solution:
    To get Mapping Details.. I made one collection and store information of mapping from "Map Input Columns". but when i run package, that collection is null at time of "Process Input" :(

    Help Needed

    - How can i get Mapping information at runtime from APIs?

    - Do we have something which persist collection or properties?

    Thanks in Advance.
    Friday, March 12, 2010 8:16 AM

Answers

  • The metadata of the currently executing package (i.e. Tasks, components, mappings etc...) is not available to that same package at execution-time. What you CAN do is instantiate a package using a Script Task and manipulate it at will.

    The only place where collections and properties get persisted is in the package itself (i.e. a .dtsx file).

    -Jamie

    http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me
    • Marked as answer by Nimesh Parikh Wednesday, March 24, 2010 5:43 AM
    Friday, March 12, 2010 8:25 AM

All replies

  • The metadata of the currently executing package (i.e. Tasks, components, mappings etc...) is not available to that same package at execution-time. What you CAN do is instantiate a package using a Script Task and manipulate it at will.

    The only place where collections and properties get persisted is in the package itself (i.e. a .dtsx file).

    -Jamie

    http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me
    • Marked as answer by Nimesh Parikh Wednesday, March 24, 2010 5:43 AM
    Friday, March 12, 2010 8:25 AM
  • Okay.. Got your point. but can i Store/Retrieve collection into/from package..?
    Friday, March 12, 2010 8:53 AM
  • Okay.. Got your point. but can i Store/Retrieve collection into/from package..?

    Can you give an example of a collection that you would like to retrieve informaiton from?

    http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me
    Friday, March 12, 2010 9:16 AM