issues in SCD while migrating from sql 2008 to 2012 using ssis

Question

Hi all 

While migration SSIS package from SQL 2008 to 2012 we noticed that Slowly Changing Dimension configuration doesn’t get carried forward and we have to manually do the configuration in 2012.

Please let me know if there is any way to export the package to SQL 2012 without having to redo the configuration.

---

Thanks & Regards, Chaitali Thaker

Answer 1

Hi all 

While migration SSIS package from SQL 2008 to 2012 we noticed that Slowly Changing Dimension configuration doesn’t get carried forward and we have to manually do the configuration in 2012.

Please let me know if there is any way to export the package to SQL 2012 without having to redo the configuration

---

Thanks & Regards, Chaitali Thaker

Answer 2

Task Factory from Pragmatic Works has an SCD Merge component that is 100 times faster and lets you edit the configuration afterwards.  With the native slowly changing dimension component that comes with SSIS you can't edit it once it's set up so it doesn't suprise me that the configuration doesn't get carried forward.  I'd recommend heavy use of annotations if you're going to keep using that component.  I'm afraid there probably isn't a way to export the package to 2012 without having to redo the configuration; but, I could be wrong.

---

Convert DTS to SSIS | Document SSIS | 30+ SSIS Tasks | Real-time SSIS Monitoring | Quick Starts | BI Blitz

Answer 3

Hi,

I had the same issue from SSIS 2005 to SSIS 2008R2.

Package migration is a major SSIS pain, had the experience while migrating packages that included SCD and Konesans components.

Even a trivial "export SQL table to CSV file" package with a plain OLEdb SQL 2005 connection failed because SQL client changed from SQLCLI.1 to

SQLCLI.10 or something, so had to manually fix db connections across all my packages.

Lack of backward compatibility was another major annoyance : my development boxes are running SQL 2008 R2 while my production boxes are SQL 2005.

I learned the hard way that you can't export an SSIS 2008 R2 package to SSIS 2005 (at least not with standard MS tools).

After both experiences, I decided to stick to "old" hand-written ETL tools whenever possible (T-SQL, bcp, .NET code), while keeping SSIS for

tasks  (i.e. SCD) where other tools would prove to be too complex, expensive or unreliable

Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

Answer 4

Please see Previous Post

---

Convert DTS to SSIS | Document SSIS | 30+ SSIS Tasks | Real-time SSIS Monitoring | Quick Starts | BI Blitz