Is there a version for Denali coming ... or a workaround?

Topics: General
Nov 17, 2011 at 5:56 PM

I've been a big user of this component in the past, but I'm now working on a project that is using SSIS from Denali.  I've tried installing the 2008 version, but it doesn't show up in the toolbox in Visual Studio 2010.  I've tried downloading the source to see if I can recompile or something too, but I have too many missing references and issues - it is beyond my ken to do this.  Soooo ... is there either a new version for Denali coming soon, or is there some kind of workaround I can do to get the 2008 component to show up in the new SSIS data flow toolbox?  I suspect the latter is not possible, but thought I'd ask anyway.

Cheers,

--DJ

Dec 11, 2011 at 4:10 AM

Ditto on this request.  Me too!  Me too!  I'm currently looking at the 2008 source, with several DLL references that can't be found, etc.  If someone's already done the work to get this running on denali/2012, please chime in!

Dec 11, 2011 at 5:15 PM

I might actually have a quick and dirty version working; one of the .net guys here managed to get it to work in SSIS 2012 RC0. Ping me directly at daj102 ‘AT’ gmail ‘dot’ com if you would like to see if it works for you too. We’re currently testing it; if it does work, does anyone know how to get it uploaded to Codeplex?

--DJ

From: joecole23 [email removed]
Sent: Saturday, December 10, 2011 23:11
To: daj102@gmail.com
Subject: Re: Is there a version for Denali coming ... or a workaround? [DimensionMergeSCD:279866]

From: joecole23

Ditto on this request. Me too! Me too! I'm currently looking at the 2008 source, with several DLL references that can't be found, etc. If someone's already done the work to get this running on denali/2012, please chime in!

Dec 17, 2011 at 6:32 AM

With help from daj102, I cleanly added new solution and project files to the source code I downloaded from CodePlex that can be used to build this component so it will work with SQL 2012 RC0.  Feel free to reach out if you'd like the zip file!

Coordinator
Mar 16, 2012 at 12:38 AM

Stellar!

I haven't had the time to play with 2012 yet - if you wouldn't mind sending me either the source and/or instructions on what you did (if your .net guy thinks is was tricky) please do.

If your .net guy says it was pretty straightforward, don't worry then - I'll figure it out when I get to it.  I can't wait that long to get it upgraded, we'll be doing some work in 2012 in the near future...

Mar 16, 2012 at 3:18 AM

Sure no problem!  I uploaded the source code to our google docs site as a temporary way to make it available to you (and anyone else that's interested) until you can get a 2012 version up on codeplex.

BTW - SSIS 2012 is a no-kidding QUANTUM LEAP compared to 2008.  Everything, from the design experience, to the new support for environments, to the simple (but MUCH NEEDED) undo/redo feature, it is simply impossible to think about going back to 2008 after working in 2012 for a couple months.

I would actually advise people NOT to play with 2012 until your organization is ready to support it, because it will be very difficult to put up with a 2008 development environment after experiencing all the new features in 2012.

And don't even get me started on all the new stuff in SQL 2012!

... but I digress.  Here's the link to the code:

Dimension Merge SCD 2012

Joe 

Mar 16, 2012 at 3:19 PM

Agree with Joe; 2012 is a very different *technical* experience than 2008.  The user interface isn't a huge leap, but there are lots of nice things in there now, like pasting tasks where you want them (not 6 feet to the right of your screen), better debug visuals, handling of data viewers, etc.

The biggest thing is that SSIS now runs in a database, not as a separate service.  You have to create a catalog in a database instance and deploy your packages *as a cohesive project* (it really doesn't want you to push individual packages) to that catalog.  Then all the configuration stuff goes away - you configure environments, variables and references from within SSMS now.  Big difference, but probably a good one for DBAs ... in 2008 and before, they didn't have visibility into what the packages did in terms of configuration.  Now, they will have control over a lot of that in the production environment.

Mar 17, 2012 at 6:42 AM

Hey Todd/DJ,

I'm getting this error A LOT while using the transform:

[Dimension Merge Slowly Changing Dimension] Error: Number of Existing Dimension columns selected (X) doesn't match expected count (Y) 

Getting rid of it usually requires some lucky combination of removing and re-doing the transform from scratch, removing the Existing Dimension connection and re-doing it from scratch, removing the Source Connection and re-doing it from scratch, etc.

Is there a "published" best way to reset the metadata on the transform?  I sometimes have to spend 30+ minutes playing around with it before it will pass validation.

Thanks,
Joe 

Apr 9, 2012 at 9:09 AM

SQL 2012 already released.

So please, i'm waiting for SCD component for 2012 too :)

May 12, 2012 at 7:26 PM

Please, add support for 2012, component is quite handy and we depend on it a lot. In case you need help with 2012 release, please let me know, I'd be happy to help. Could you please publish plans about future, it would be beneficial to know that for example it's not going to be release within next few months, so that we don't wait for it.

Best regards,

Radim

Jul 27, 2012 at 11:17 AM

+1!

We are planning to upgrade our SSRS/SSIS/SSAS solutions to 2012 and this might just be the one thing that would prevent us from doing so. Any hints on if, and if so, when this could be supported would be great!

@joecole23, thanks for providing the sources. I wouldn't mind working with the 'unofficial' version for now, but I have no idea on how to proceed. Can you give me some pointers on how to complile the sources or what steps I should take to be able to use this SCD component with SSDT en SQL 2012?

 

Kind regards,

Thijs

 

 

Jul 31, 2012 at 5:01 PM

I've been trying to find time to re-install this component on a fresh workstation and actually document all the steps required to get it working.  Unfortunately, I haven't been able to do that yet.  However, I just received an email from Thijs and he has also successfully gotten the component to work, albeit with the same VS_NEEDSNEWMETADATA frustrations that I was (and still am) experiencing.  Since it may help others, here's a snippet from our most recent email conversation:

------

Hey man,

 

Sorry, I wasn’t ignoring you.  I’ve been wanting to re-install the 2012 version on a fresh computer so I could document the process step-by-step and post it on the forum.  Maybe you did that while you were getting it working and you could share how you did it?

At any rate – no, I didn’t fix that issue.  HOWEVER, I have learned how to get it to go away, and I suspect that the 2008 version of this component might have suffered from the same metadata-related frustrations (although I wouldn’t know because I never used the 2008 version).

Here’s what I’ve learned about this transform:

1)      Any changes to the data flow (even just including another column on one of the outputs!) effectively “breaks” the metadata relationships in a way that can’t be resolved by going to Advanced Edit on all the components, doing refreshes, etc.  Whenever I need to make a change to a data flow that uses this transform, I delete all of the OLE DB sources and destinations, as well as the SCD Transform, and then recreate them (with whatever changes I needed) and reconnect everything.  Of course, this means I have to go through the SCD wizard and re-do all the SCD1/SCD2 settings, output settings, etc.  Needless to say, I’ve gotten really fast at re-doing all of that.

2)      Whenever you make changes, or even when you’re creating a package for the first time, VS 2010 will always give the error about VS_NEEDSNEWMETADATA.  When this happens, I save and close the package.  Then, I re-open it (which causes all the validation routines to run on the data flow), and then open the data flow, double-click on the transform, and step through all the pages in the wizard.  Then, I attempt to run the package.  If it still gets the metadata error, I repeat these steps again – save the package, close the package, open the package (validation runs), open the transform, step through the pages, and re-run the package.  Eventually, the package will actually run without getting the metadata error at validation.  I have no idea why it does this.  If it didn’t do it in the 2008 version, then there must be something in the business rules that’s not working right for SSIS 2012.  At any rate, I’ve always gotten it working – eventually.  WARNING – you can easily spend an entire day trying to refresh stuff on the Advanced Edit window of different components in the data flow, delete and re-create workflow connections, etc. without getting past this metadata error.  The key seems to be closing the package and re-opening it so all the validation related to the data flow is re-executed.

Hope that helps!  It’s always tougher to blaze a new trail.

From: notifications@codeplex.com [mailto:notifications@codeplex.com]

Sent: Tuesday, July 31, 2012 10:37 AM
To: joecole23@hotmail.com
Subject: Dimension Merge SCD number or columns error

 

User thijsnijhuis has contacted you through CodePlex using the Contact User form. Replying to this email will reply directly to the user. If you do not wish to allow being contacted, you may indicate so on your profile page.

The following is the message from thijsnijhuis:


Hi,

Sorry to contact you AGAIN!

Just wanted to let you know I figured out the problem with the edit dialog not showing up and fixed the problem. Unfortunately, I now run into the same problem you posted with error messages stating: [Dimension Merge Slowly Changing Dimension] Error: Number of Existing Dimension columns selected (X) doesn't match expected count (Y)

I was wondering, have you ever been able to fix this problem?

Kind regards,
Thijs

 

Aug 1, 2012 at 4:31 PM

Here's another email from Thijs where he's reporting that he got the component working!  He also walks through the process of compiling the .NET source I provided.  Hopefully this helps anyone else that's trying to get this invaluable transform running on SQL 2012 in advance of an "official" version being released.

-----

I think the original component had both DLLs in the pipeline folder.  Congrats on getting it working!

From: Thijs Nijhuis [mailto:thijs.nijhuis@infotopics.nl]
Sent: Wednesday, August 01, 2012 2:39 AM
To: Joe Cole
Subject: RE: Dimension Merge SCD number or columns error

 Hi Joe,

 Thanks for your response and sorry for mailing you three times about this. After my first question I found a ‘.net guy’ of my own who helped me a lot and the questions I had changed.

 I don’t have a documented step-by-step plan ready for you but I’ll talk you through the main issues we ran into. Perhaps that helps.

  1. The main problem we ran into was that I had sql12 installed on my machine but didn’t have the visual studio c#.net to be able to open the solution you provided.  The .net guy however did have the c# environment, but didn’t have sql2012 installed. This resulted in a dll hell with me sending him the version 11 dll’s from sql and he sending me back new dll’s to test. In the end we solved this by installing the vs2010 express c# version on a machine that has sql2012.
  2. The dll’s needed to be signed to be able to be added to the GAC. The solution didn’t have the key in it to do so, so we created one.
  3. We registered the dll’s in the gac and added it to the pipelineComponents folder under program files\sql server\110\sdk. This resulted in the component showing up in the toolbox. I could add it and connect sources which the component seem to recognize. The problem was, I couldn’t edit the component (the second mail I send you is about this issue ;) )
  4. The problem with the edit not showing up was on line 42 of DimensionMergeSCD.cs. The publicKeyToken field needed to be updated to the right key.

All that’s left now is the problem you ran into as well with the columns being out of sync. You second pointer is very helpful as a workaround. I saved the packaged, closed it, opened it, went through all of the tab pages on the dialog and then the component ran correctly! Thank you very much for this!! This is the first time I had the component running under sql2012 and will allow us to move on with the migration. I’ll look into the annoying ‘out-of-sync’ errors myself as well but I am not sure I’ll be able to fix it. It’s probably best that the designer of the component takes a look at it because this is all new for me.

 I have also been able to upgrade an existing package to the new component. What I do is let SSDT upgrade the package first; with the new dll’s registered it shouldn’t give any errors. Then I open the package in  ‘code mode’ , lookup the component tag and change the version attribute to 10. Then a bit lower, within the component tag you’ll find a ‘property’ tag with name ‘UserComponentTypeName’. In this I changed ‘DimensionMergeSCD100’ into ‘DimensionMergeSCD110’ and the publicKeyToken to the new token. Now when I open the package I can edit the component, click through all of the tab-pages and after that it will run in SSDT as well.

 One last thing, do you know if you have to register both dll’s that comes with the solution and put both in the pipelinefolder? I did because otherwise it wouldn’t work but the original component only has one dll I think. Maybe it’s just a build setting I need to change or something like that.

 Once again, thanks for your help so far. Kind regards,

Thijs

Aug 14, 2012 at 6:48 PM

I'm looking forward to a new release as well for 2012!  I'm going to have to delay our migration in the meantime :(

Aug 30, 2012 at 4:30 PM

***Disclaimer: I work for PragmaticWorks. ***

Hi. I just wanted to mention that the PragmaticWorks supported version of the component has been updated to fully support 2012. If you are interested in the commercial, supported version of the component, you can find more information here: http://pragmaticworks.com/Products/Business-Intelligence/TaskFactory/Features.aspx#TSDimensionMergeSCD.

 

Aug 30, 2012 at 6:20 PM

As a consultant, I move between clients and projects quite a lot, and I would just point out that the PragmaticWorks version of the component is the one I always recommend.  Having the support is a great idea if you are going to use this component in a production system, and it is very reasonable in terms of cost.  I highly recommend that you consider it if you want the SCD.

And I *don't* work for PW, but I have used their products a lot :-)

--DJ

Sep 5, 2012 at 6:08 PM

So we would have to pay to download this, is that what I’m seeing?

Shawn Larson

Applications Software Engineer

DIGI-KEY CORPORATION

701 Brooks Ave. South

Thief River Falls, MN 56701 USA

218.681.8000 x 1735

From: johnwelch [email removed]
Sent: Thursday, August 30, 2012 10:31 AM
To: Shawn Larson
Subject: Re: Is there a version for Denali coming ... or a workaround? [DimensionMergeSCD:279866]

From: johnwelch

***Disclaimer: I work for PragmaticWorks. ***

Hi. I just wanted to mention that the PragmaticWorks supported version of the component has been updated to fully support 2012. If you are interested in the commercial, supported version of the component, you can find more information here: http://pragmaticworks.com/Products/Business-Intelligence/TaskFactory/Features.aspx#TSDimensionMergeSCD.


This email and the associated attachments may contain information that
is proprietary, privileged, confidential or otherwise protected from
disclosure. If you are not the intended recipient or otherwise have
received this message in error, you are not authorized to read, print,
retain, copy or disseminate this message or any part of it. If you
are not the intended recipient or otherwise have received this message
in error, please notify us immediately, destroy any paper copies and
delete all electronic files of the message.
Thank you. Digi-Key Corporation.

Sep 5, 2012 at 6:28 PM
For the PragmaticWorks version, yes. They take the open source version, tweak it and enhance it, and provide support. For that you pay for the product. If you don't want to pay, you can just download the un-tweaked and un-enhanced version from CodePlex and use that at your own risk. I've done both on projects with success.

--DJ

On Wed, Sep 5, 2012 at 1:09 PM, larson311 <notifications@codeplex.com> wrote:

From: larson311

So we would have to pay to download this, is that what I’m seeing?

Shawn Larson

Applications Software Engineer

DIGI-KEY CORPORATION

701 Brooks Ave. South

Thief River Falls, MN 56701 USA

218.681.8000 x 1735

From: johnwelch [email removed]
Sent: Thursday, August 30, 2012 10:31 AM
To: Shawn Larson
Subject: Re: Is there a version for Denali coming ... or a workaround? [DimensionMergeSCD:279866]

From: johnwelch

***Disclaimer: I work for PragmaticWorks. ***

Hi. I just wanted to mention that the PragmaticWorks supported version of the component has been updated to fully support 2012. If you are interested in the commercial, supported version of the component, you can find more information here: http://pragmaticworks.com/Products/Business-Intelligence/TaskFactory/Features.aspx#TSDimensionMergeSCD.


This email and the associated attachments may contain information that
is proprietary, privileged, confidential or otherwise protected from
disclosure. If you are not the intended recipient or otherwise have
received this message in error, you are not authorized to read, print,
retain, copy or disseminate this message or any part of it. If you
are not the intended recipient or otherwise have received this message
in error, please notify us immediately, destroy any paper copies and
delete all electronic files of the message.
Thank you. Digi-Key Corporation.

Read the full discussion online.

To add a post to this discussion, reply to this email (DimensionMergeSCD@discussions.codeplex.com)

To start a new discussion for this project, email DimensionMergeSCD@discussions.codeplex.com

You are receiving this email because you subscribed to this discussion on CodePlex. You can unsubscribe on CodePlex.com.

Please note: Images and attachments will be removed from emails. Any posts to this discussion will also be available online at CodePlex.com


Jan 15 at 11:26 PM
Bumping this thread ... I followed the instructions above (create new key for signing, etc) too and am currently using the component on 2012, mostly successfully. I'm seeing the same issue in Visual Studio, though, where the Existing Dimensions column set seems to get screwed up. Shutting down the package and reloading doesn't seem to help either; upon validation it just finds different issues. What I have noticed is that if you have any columns that are Not Used, the problem is worse. Not sure if that helps ... :-/

Anyway, two things:
1) Todd (or anyone else) - do you want a copy of my source code so that you can upload to the Codeplex repository somehow?
2) Does anyone have the C# skills to have a look at the XML serialization of the columns sets? I would suspect it is caused by differences between .net v2 and v4 ... but I'm not experienced enough to work it out. :-(

Thanks, as always,

--DJ
Mar 24 at 6:07 PM
Another bump ... I've been able to get a .net developer colleague to have a look at the component, and he believes the core issues of the 'Existing Columns are not as expected' bug is around Lineage IDs, and the way that the component stores the mappings at design time. This article (by Todd :-)) actually describes the issue:

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/6425e61a-a7fe-4ce4-88fc-f90f544b25e9/lineageid-of-an-input-column-has-an-another-value-in-runtime

One way to work around the issue is to configure the component, remove it from the project and then add it as an existing item ... this process actually goes through and validates the lineage IDs and configuration, and it works first time after that. However, getting it to work after configuration without this workaround is a bit of a random act.

He's also found some issues with the refreshing of the component editor - I've noticed that when you tab across the various screens, some of the tabs (eg, the mapping tab) don't update based on the data you specify (ie, SCD 2 special columns shouldn't be mapped). So there is a little bit of work to do ... will update as we go.

If anyone from Pragmatic Works is on this thread ... does the PW component already have these updates in for 2012? Just wondering, because I would love for them to come back into the source code ... I know you provide extra value with the support, but as PW is so associated with this component, I thought more feedback into the code would be nice. Or is this part of the commercial aspect?

--DJ
Mar 24 at 6:21 PM

I paid the $1k for the PW Task Factory (really just to get a supported 2012 version of DimensionMergeSCD) over a year ago, and let me tell you, IT WORKS FLAWLESSLY. I haven’t had a single problem with it, and I used to HATE having to make changes with the version you’re using.

My advice (for what it’s worth) is to pay the money! J

Mar 24 at 6:31 PM
Thanks for the confirmation, Joe :-) I've used the paid version on previous SQL versions, but not 2012 ... I kinda expected it to be working fine, but I'm just a bit disappointed it hasn't been merged back in to this project :-/
Mar 24 at 8:40 PM
*** I work for Pragmatic Works ***

Joe - thanks for the feedback and glad to hear you are getting value from our product.

DJ - The code base for the commercial version and the open source version is pretty different now. Pragmatic Works purchased a snapshot of the open source code to jump start the development of the commercial component. Todd still owns, manages, and develops the open source project and the code bases have diverged over time.

Between our developers, QA, and support, we've put thousands of hours into improvements and upgrades. As we made changes to the commercial version, we used our standard libraries to implement them faster. The 2012 update, in particular, involved quite a few modifications and updates. At the core, yes, it is the lineage id change in 2012, but there were other significant changes that we had to make to fully support the upgrade. Because of the number of changes, and because some of them leverage our internal code, merging (in either direction) is very difficult to accomplish.

Hope that helps you understand where things are at currently. It's unlikely that the changes we've made in the commercial component will be migrated back into the open source version in the future.

John Welch
VP of Software Development
Pragmatic Works
Mar 24 at 9:02 PM
Thanks John ... as I've said, I've used the PW version on other projects at other clients, and have never been disappointed. Thanks for taking the time to reply here - makes sense (as one developer to another :-)) Looks like we will soldier on applying what fixes we can for lineage ID and see what that gets us. Cheers!