Load Foreign Key table and get ID

SQL Lion » SSIS

Load Foreign Key table and get ID

(6 posts) (3 voices)

Started 1 year ago by ghp161
Latest reply from sqltech56

Activate e-mail notification in your profile settings to get updates. [Help ?]

Tags:

ghp161
Member

Hi,

I am building dtsx package dynamically based on the column mappings done through GUI interface and then executing that package to do the data transfer.

The Source is excel file and destination is SQL Server 2005.

There is a foreign key field referencing Table-B in the destination table Table-A. I need to insert the selected source column record into Table-B first if not exists or update if exists and get the primary key value from Table-B and then insert the values in Table-A along with primary key value of Table-B into foreign key column in Table-A.

Tried on the web on how to do this programatically, but could not find any.

Please help.

regards,
hari.

Posted 1 year ago #
anikonave
Member

Hi hari,
Just have a quick question,
Are you trying to build the SSIS package on the basis of the logic,
or want to implement the above logic programmatically?
And please tell me what programming language you are using for this, I guess C# .net.
And to my understanding, you are trying to build an SSIS package with the said logic by C# .net.

Posted 1 year ago #
ghp161
Member

Hi,

Yes, the programming language is c# and creating the SSIS package with the logic in a windows application.

Based on the schema of the selected destination table, if any of the column is a foreign key then I need to populate that table first and then use that primary key in the selected destination table.

Thanks for the help in advance.

regards,
hari.

Posted 1 year ago #
sqltech56
Member

@Hari,
i still didn't understand your requirement of building SSIS package by C# .net.
What I understand is you have an windows application that needs to deploy your logic.

This you can achieve very simply by C# language with common SMO classes.(not by DTS related classes)

But if you want your application to build SSIS packages with the above logic, then first you need to develop the SSIS package in BIDS with the above mentioned logic of yours. And then try to decode each component and transformation to its corresponding DTS wrapper.

But if you want someone to give you the code, then its a huge task, and will take some time. I will try to implement it but don't expect the code to be posted in near future.

Thanks,

Posted 1 year ago #
ghp161
Member

Hi,

Let me explain my scenario. We have an application which needs to populate data from an third party applications used by our clients. Mostly these clients will give us data in excel, but some times we need to connect to sql server / oracle / mysql to get the data to our system.

So, I am planning to build a import and export data transformation services module to connect to any type of third party applications to get the required data to our system. From client to client, the data mappings will change, so I donnot want to use BIDS as I need to map the columns dynamically and create the package.

I have already completed the mappings part and creating package and executing it. If it is one to one table transformation then it is working fine. Now I need to add the logic for populating the foreign key values.

What is the best way of populating the masters data while processing the transaction data. Should I populate masters first from a separate sheet if the source is excel and then start the processing of transaction and do a lookup on master data. Or while processing transaction records, check the master for every record if not exist create and get primary key information.

thanks,
hari.

Posted 1 year ago #
sqltech56
Member

@hari,
Regarding your question about loading master data, I would like to say that it actually depends on the situation of your sources.
1) If your master information and transactional information are in the same source, then I would suggest you to process the transactional data first, check the existence of key column in the master table (insert if not exist or update, whatever), pull the primary key from the master table for that record (if necessary) and finally load the fact table (transaction info). In this way there is no need to pull the source again and again (i.e. once for master and once for fact).
2) But if your master information source and transactional information source are different. Then load the master data first and subsequently load the transactional data by having a lookup with master table.

Posted 1 year ago #

RSS feed for this topic

Reply

You must log in to post.