locked
SCD does not show OLEDB Connections to ORACLE database RRS feed

  • Question

  • When I use the SCD wizard the Connection Manager dialog only shows me OLE DB connections against SQL Server databases.  I have an Oracle connection using ORACLE provider for ORACLE but this is not shown in the dropdown. why,how to solve this?
    Wednesday, November 4, 2009 6:17 AM

Answers

  • The Slowly Changing Dimension Wizard only supports connections to SQL Server.

    Refer link.

    Alternatively you can use lookup transformation for SCD purpose. Follow as below,
    1. Create a Oracle source.
    2. place a lookup tranformation and select records from the destination table
    3. Place a condition split and split the records based on the lookup output. (Matched record means data already exists and update required. Unmatched records means new records and need to insert).
    4. On the unmatched path of the condition split place a ole db destination and insert the records to the destination table.
    5. on the matched path place a oledb command tasks and write an update to update the records.
    Thanks, Bharani M - Please mark the post as answered if it answers your question. - Please vote the post as Helpful if you find the post as helpful.
    • Proposed as answer by Nitesh Rai Wednesday, November 4, 2009 6:33 AM
    • Marked as answer by ytu_michael Wednesday, November 4, 2009 9:06 AM
    Wednesday, November 4, 2009 6:28 AM

All replies

  • Hi,
    Please check the following same thread here.
    http://social.msdn.microsoft.com/forums/en-US/sqlintegrationservices/thread/115617b2-ddd5-4df7-8ab4-59e09fab06b6/

    Thanks-
    Let us TRY this | Don’t forget to mark the post(s) that answered your question
    Wednesday, November 4, 2009 6:23 AM
  • The Slowly Changing Dimension Wizard only supports connections to SQL Server.

    Refer link.

    Alternatively you can use lookup transformation for SCD purpose. Follow as below,
    1. Create a Oracle source.
    2. place a lookup tranformation and select records from the destination table
    3. Place a condition split and split the records based on the lookup output. (Matched record means data already exists and update required. Unmatched records means new records and need to insert).
    4. On the unmatched path of the condition split place a ole db destination and insert the records to the destination table.
    5. on the matched path place a oledb command tasks and write an update to update the records.
    Thanks, Bharani M - Please mark the post as answered if it answers your question. - Please vote the post as Helpful if you find the post as helpful.
    • Proposed as answer by Nitesh Rai Wednesday, November 4, 2009 6:33 AM
    • Marked as answer by ytu_michael Wednesday, November 4, 2009 9:06 AM
    Wednesday, November 4, 2009 6:28 AM
  • Hi,someone talk about kimballscd ,i want to know whether it is the mend for SSIS,can i use it?

    Wednesday, November 4, 2009 6:47 AM
  • Hi,
    defiantly you can use it with SSIS. please check the following link for more information.

    http://toddmcdermid.blogspot.com/2009/01/kimball-method-scd-project-updated.html
    http://kimballscd.codeplex.com/Thread/View.aspx?ThreadId=54016

    Thanks-
    Let us TRY this | Don’t forget to mark the post(s) that answered your question
    Wednesday, November 4, 2009 7:01 AM
  • Read more about KimballSCD here.

    There is weakness mentioned in the link "Memory consumption for this component is larger, as it's not doing row-by-row lookups." Keep this in mind and decide on whether to go for this or not.


    The steps mentioned in my earlier post is a workaround when you use other datasources as source or when the number of records needs to be processed by SCD is more.
    Thanks, Bharani M - Please mark the post as answered if it answers your question. - Please vote the post as Helpful if you find the post as helpful.
    Wednesday, November 4, 2009 7:05 AM
  • Ok,thanks , i will have a try.

    Wednesday, November 4, 2009 8:45 AM
  • thank you,if it is not doing row-by-row,there will be error appered.then i will follow the suggestion which you give above.thanks.
    Wednesday, November 4, 2009 8:48 AM
  • Welcome.
    Please mark the post as answered if it answers your question.
    Thanks, Bharani M - Please mark the post as answered if it answers your question. - Please vote the post as Helpful if you find the post as helpful.
    Wednesday, November 4, 2009 8:57 AM