This project has moved. For the latest updates, please go here.

Any plans for the update to SSDT BI for Studio 2013?

May 28, 2014 at 8:56 PM
Edited May 28, 2014 at 8:57 PM
SSDT BI for Studio 2013 was just released here on 05/25. Just curious if there is any plan to support that version of the data tools?


Thanks,
Mike
Coordinator
May 29, 2014 at 1:31 AM
I wasn't expecting to have to do anything special for it. I was expecting SSDT BI, to check the SSIS folders for DLL's, as per the VS2010 and above BI toolsets.

I'll install it over the weekend, and see what's changed from the integration services perspective.

Keith
May 29, 2014 at 2:38 PM
I completely understand. I feel like this particular release of SSDT BI has been the most strange and complicated one to date, and I am not sure of the reason. It was released back in April and then pulled back and now re-released here a few days ago.

That said, your work on Multiple Hash is incredibly appreciated. I did not expect you to be able to respond this quickly and I truly appreciate your willingness to take a look at this. We have been using this for years and will continue to do so as it is working, because it just does exactly what we need it to. So thank you again for all of your efforts on this wonderful free task.
Coordinator
Jun 1, 2014 at 1:56 PM
I did a clean install of Windws 8.1, installed the SSDT BI 2013 version.
Multiple Hash's installer "detected" that SSIS wasn't there, and required a custom install.
Using the SQL 2014 custom install option, appears to place the DLL's in the right location. (I did notice that it x64 option is called x86 (oops)).

Loading an existing package caused the below error:

Error at Data Flow Task [Data Flow Task (Multiple Hash [106])]: The managed pipeline component "Martin.SQLServer.Dts.MultipleHash, MultipleHash2012, Version=1.0.0.0, Culture=neutral, PublicKeyToken=51c551904274ab44" could not be loaded. The exception was: Could not load file or assembly 'Microsoft.SqlServer.PipelineHost, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified..
Coordinator
Jun 1, 2014 at 1:57 PM
This discussion has been copied to a work item. Click here to go to the work item and continue the discussion.
Coordinator
Jun 3, 2014 at 1:57 PM
This issue has been fixed.
Awaiting regression testing.
The installer is available in release 1.6.1.3 if you wish to assist in testing.
Jun 4, 2014 at 8:59 PM
I will get it installed right away and let you know if I have any issues.

Thank you again. Your efforts are truly appreciated.
Jun 4, 2014 at 9:35 PM
I thought I would let you know that I did a few different things and all worked successfully.

I created a brand new package with a data flow and with Multiple Hash doing its thing. It worked without issue.

I took a package that I was upgrading from SQL 2008 SSIS and moving into SQL 2014. It upgraded the Multiple Hash Task without issue and everything ran through without issue.

The only thing that I would say is worth taking note of (and this does not negatively affect me in any way) is that the HashValue that was returned (in this case MD5) was different for the same data between the SQL 2008 SSIS Multiple Hash and the SQL 2014 SSDTBI Multiple Hash. To me this does not cause me any problems because once those values are set and stored, it is never an issue again. But if someone has a very large data set and they use this for comparison purposes and do updates or inserts based on those Hash Values, the initial run has the potential to update everything. This is no big deal for me, and it is working as well as I could have hoped but I figure it is worth mentioning based on the testing that I did.

Thank you again for your efforts on this. Easily one of the most useful tools that I have used for many years now.
Coordinator
Jun 5, 2014 at 1:17 AM
Edited Jun 5, 2014 at 5:59 AM
Not sure what's happening with different hash's being generated, as it's the same code base for the Hash generation.

That's a fatal bug from my perspective.

I'll diagnose and release another version.

Regression Test Commands, using a package from the SSIS2008TestMultipleHash project, which validates that the hash generated is the same as version 1.3.1 was... (also tests upgrade from V1.3.1 and to 2012 or 2014):

"C:\Program Files\Microsoft SQL Server\100\dts\Binn\DTExec.exe" /F EveryDataType_V1.3.1.dtsx /Rep EWI
"C:\Program Files\Microsoft SQL Server\110\dts\Binn\DTExec.exe" /F EveryDataType_V1.3.1.dtsx /Rep EWI
"C:\Program Files\Microsoft SQL Server\120\dts\Binn\DTExec.exe" /F EveryDataType_V1.3.1.dtsx /Rep EWI
"C:\Program Files (x86)\Microsoft SQL Server\100\dts\Binn\DTExec.exe" /F EveryDataType_V1.3.1.dtsx /Rep EWI
"C:\Program Files (x86)\Microsoft SQL Server\110\dts\Binn\DTExec.exe" /F EveryDataType_V1.3.1.dtsx /Rep EWI
"C:\Program Files (x86)\Microsoft SQL Server\120\dts\Binn\DTExec.exe" /F EveryDataType_V1.3.1.dtsx /Rep EWI

Keith.
Jun 5, 2014 at 3:19 PM
I would bet a great deal of money that this has nothing to do with your code and more to do with how SSIS is interpreting the inputs.

That said, just so you know the environment that I was working on the Original Hash was created on a server running Windows 2008 R2 with SQL 2008 as the backend and then the new hash was created on my local PC (Windows 7 X64) against that same SQL 2008 database. Sorry that I did not provide that the first time around.
Coordinator
Jun 6, 2014 at 4:40 AM
Edited Jun 6, 2014 at 8:08 AM
Could you please let me know what patch levels you have on your workstation?
And what install media you used to install it? (SQL 2012 with SP1 for example. SQL 2014 slipstreamed with CU1)

These commands will show what version of SSIS is installed...

reg query "HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\100\DTS\Setup" /v PatchLevel
reg query "HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\110\DTS\Setup" /v PatchLevel
reg query "HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\120\DTS\Setup" /v PatchLevel



I've now tested the following:

2014 on Windows 2012 R2 SSIS Only install
12.0.2000.8 - OK
12.0.2342.0 - OK (DTEXEC.exe still reports 12.0.2000.8)

2012 on Windows 2012 SSIS Only install
11.0.2100.60 - OK RTM
11.1.3000.0 - OK SP1
11.1.3128.0 - OK SP1 HF
11.1.3381.0 - OK CU6
11.1.3393.0 - OK CU7
11.1.3401.0 - OK CU8
11.1.3412.0 - OK CU9
11.1.3431.0 - OK CU10

2012 on Windows 2012 Full Install
11.0.2100.60 - OK RTM

2012 on Windows 2012 Full Install SP1 and CU9.9 slipstreamed
11.1.3430.0 - Failed
11.1.3431.0 - Failed CU10
Jun 6, 2014 at 2:52 PM
I will definitely get you that information. Sadly, I am out of town for the weekend so I do not have easy access to my computer but as soon as I do I will get you this information.
Coordinator
Jun 7, 2014 at 1:17 PM
Edited Jun 7, 2014 at 1:41 PM
I have been chasing my tail.
The differences I am seeing is because my Test Machine, which is failing, has a different timezone setting.
This is causing the values being placed into the pipeline to be different for the DT_FILETIME datatype.
Which means that the input to the hash is different.

There are three characters different in these strings and I didn't see them.
   Description: columnToProcess 30, j = 19, columnToProcessID = 19, DataType = DT_FILETIME
   Description: Column 19 Value GzIwMTEtMDgtMjUgMTQ6MDA6MDAuMDAwMDAwMA==
   Description: Column 19 Value GzIwMTEtMDgtMjYgMDA6MDA6MDAuMDAwMDAwMA==
Is there any chance that you are using DT_FILETIME data types, and that the two computers have different region settings?


FYI:
Failing Test Script Code if region <> +10h UTC:
        GeneratedDataBuffer.Columnfiletimestamp = DateTime.Parse("2011-08-26");
Passing Test Script Code in any region:
        GeneratedDataBuffer.Columnfiletimestamp = DateTime.Parse("2011-08-25 14:00:00Z");
Jun 9, 2014 at 7:06 PM
Sorry for my slow response to this. I am guessing based on your findings here that my patch level is not as relevant, but here it is for their respective versions


For my Test machine
PatchLevel REG_SZ 10.3.5500.0
PatchLevel REG_SZ 11.1.3128.0
PatchLevel REG_SZ 12.0.2342.0

And from the server where the original hash was created

PatchLevel REG_SZ 10.3.5512.0
(No SQL 2012 or 2014 on this server yet)


In terms of installation media on the server, we installed SQL 2008 with SP1 slipstreamed. We then later installed SP2 and then SP3.

On my test machine the installation media was actually the RTM of each version followed by the various SP's.

For the time zones (which seems like a legit possibility for why this stuff is happening), both my test machine and the server appear to be set to the same Time Zone (UTC-06:00) Central Time (US & Canada). But because of your comments, I will run some additional tests to see if it will create a new hash the same in the following scenarios
  1. No dates used. Integer and character data only
  2. Datetimes involved. Various data including datetime
I will let you know what I find.
Jun 18, 2014 at 5:16 PM
Once again, sorry for the delayed response.

I ran the two tests that I mentioned above where I ran the process on both machines and did so in scenario one where no dates were used and then in the second scenario where dates were used along with other data.

Scenario 1 - passed. Both machines came up with the same hash values down the line.
Scenario 2 - failed. Different hash values were created when the date value was used.

For the test, I just did a very basic select against Northwind

Scenario 1
Select EmployeeID, LastName, FirstName, Extension
from Employees

--Hashed on Last, First, Extension

Scenario 2
Select EmployeeID, LastName, FirstName, Extension , HireDate
from Employees

--Hashed on Last, First, Extension, HireDate

Both scenarios were run on both machines and results were compared.

It seems like the dates certainly do have impact. Hopefully this is useful.

Thanks,
Mike
Coordinator
Jun 19, 2014 at 12:34 PM
Mike,

Which version of Multiple Hash was used to create the package that runs on SQL 2008? Was it prior to 1.6?
Was a new package created for the package on SQL 2012/SQL 2014, or was it the package created on SQL 2008?

There was a change in version 1.6 of Multiple Hash that added handling for Milliseconds on all date/time data type hashing. By default it is ON for new packages, but OFF for upgrades from an old package to a new package (ie. old package predates Millisecond handling.) to ensure compatability in the hash between versions.

I ask this as I have just installed the Northwind database onto a SQL 2008 R2 Server and an SQL 2014 Server, in different timezones, and not had a problem. This was expected as Northwind is using DateTime as the data type (at least the version of Northwind I am using). Northwind Download

When I tick the timestamp tick box, the hash changes.

Can you please check this for me?

Keith
Jun 23, 2014 at 9:02 PM
Sorry for the delayed response, yet again.

The version of Multiple Hash that was used to create the package that runs on SQL 2008 was version 1.3 (so yes, I am on an older version).

For the initial tasks that I was running, I was using the same package that was created on SQL 2008 and just simply upgraded the package.

For the latter tests, I have created new packages on each (SQL 2008 - 1.3, SQL 2014 version beta version you sent). On the new version I have the timestamp tick box checked but 1.3 did not have that, and this may well be my issue here.

Sorry for the runaround on this. I am guessing in the end it is because this was an older version that I am seeing this but hopefully this will let you know one way or another for sure.
Coordinator
Jun 24, 2014 at 12:12 PM
That explains the Northwind Issue.
Multiple Hash versions prior to 1.6 were not using milliseconds in the date type value hashing, and new packages default to enabling milliseconds for 1.6 and greater. This will generate a different hash.

It doesn't explain why an upgraded package would return a different value.
The upgrade will disable milliseconds when going from before 1.6 to 1.6 or greater for backwards compatability reasons.
Is there any chance that you turned the milliseconds after the upgrade of the package from SQL 2008 to SQL 2014?
I'm hoping yes as that then nails this as a documentation issue on my part.

Please note that my testing includes a test of a package from 1.3.1 to the latest version to confirm that backwards compatability is working.

Keith.
Jun 25, 2014 at 10:39 PM
Yes, it does indeed look like I turned the milliseconds on after the upgrade of the package from SQL 2008 to 2014.

Really sorry. I do believe I have probably wasted a fair amount of your time on this.
Coordinator
Jun 25, 2014 at 11:22 PM
No problems.
I found a bug in my unit tests as a result of chasing this down, which is a good thing.

I'll update the site wiki to make it much clearer about the millisecond setting.
I'm glad to know that the component is working correctly.

The real 2014 version should be released this week...

Keith
Jun 26, 2014 at 3:54 AM
Thank you again for all of your work. This is really a fantastic tool, and we use it heavily.
Jul 4, 2014 at 10:40 AM
Hi.

I tried the latest version (1.6.1.2) but on install i get the error:
_
Error opening file for writing:
PipelineComponents\Multiplehash2012.dll

Click aport.....etc etc etc._

Im running windows server 2012, SQL server 2014 Enterprise & BIDS 2013. is there a know issue or is further information required to troubleshoot this?


Thanks!
Coordinator
Jul 7, 2014 at 4:29 AM
Sorry for the delay.
I've had internet problems so unable to post updated version.
Hope to address this tonight.

Keith
Coordinator
Jul 8, 2014 at 2:08 PM
Release 1.6.3.4 has been made.
This addresses the detection of BIDS and SSDT's, and installs the correct DLL for SQL 2014.

schmintan,
This release should correct your install issue. If it does not, please log an issue and I will address it.

Keith
Marked as answer by kmartin on 7/8/2014 at 6:08 AM