Solved

Bulk import from MDS using SSIS

Posted on 2016-11-02
4
61 Views
Last Modified: 2016-11-07
Hi

Please assist on ImportType (https://msdn.microsoft.com/en-us/library/ee633854) on MDS I can use to insert data to the <mds. stg table> without duplicating existing data?I have used 0,1,2 and all of them are giving me the same results,each time I run the SSIS package it insert new records to the table even the records that are already in the table.
0
Comment
Question by:devguru001
  • 2
  • 2
4 Comments
 
LVL 3

Expert Comment

by:Shailesh Shinde
ID: 41871624
Hi,
Each entity in SQL Server 2012 Master Data Services (MDS) will have it’s own staging table (stg.<name>_Leaf). Using this staging table, you can create, update, deactivate and delete left members in bulk.
Have you gone through with this post...


Thanks,
Shail
0
 
LVL 3

Expert Comment

by:Shailesh Shinde
ID: 41871625
0
 
LVL 14

Expert Comment

by:Megan Brooks
ID: 41871648
Are you saying that the data values for two different members (codes) are the same? Are you automatically generating codes? The import doesn't check for duplicate data for a given member.

The ImportType compares members using the Code attribute. You can specify that only new members (codes) are created (ImportType = 1), or have it also update existing members (identified by code) with new data, either treating NULL attribute data as 'don't change this attribute' (ImportType = 0) or as actual data (ImportType = 2).

(While I have worked extensively with MDS, it was 3+ years ago. Apologies in advance if I am not understanding the problem correctly.)
0
 
LVL 14

Accepted Solution

by:
Megan Brooks earned 500 total points
ID: 41871655
You may need to load an SSIS lookup with data from an MDS subscription view, and then check new incoming data against that before sending it to the staging table (so as to exclude the duplicate rows). It sounds, though, like there is an issue with your coding scheme, or that you are using MDS to store something other than attribute data, for which codes might not be meaningful.

If you can provide a little more background on what you are trying to do then I might be able to suggest something more.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a previous article I've shown you how to import data from an Excel sheet using the OPENROWSET() function (http://www.experts-exchange.com/A_3025.html).  And I concluded by stating that it's not the best option when automating your data import. …
Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now