Well, SQL Server SP2 Nov CTP is being released today so I hope you’re all ready for it. The CTP will officially open up today at 3pm PST. You can get it here: http://www.microsoft.com/sql/ctp.mspx I’m really hoping this will add up to a significant release for MS because while Yukon was an excellent release, the promise is that this SP will pick up a lot of the things that got left out. That’s kinda the problem Well, SQL Server SP2 Nov CTP is being released today so I hope you’re all ready for it. The CTP will officially open up today at 3pm PST. You can get it here: https://www.microsoft.com/sql/ctp.mspx I’m really hoping this will add up to a significant release for MS because while Yukon was an excellent release, the promise is that this SP will pick up a lot of the things that got left out. That’s kinda the problem you have when you rewrite something basically from scratch. It’s hard to make sure that everything gets in there. Though I do find it hard to believe sometimes that some of these things didn’t get noticed before, the point is they’re getting noticed now. I don’t have a complete list of the enhancements, but I’m working on it. As well, I’ll be making a series out of this and as I find things out, I’ll do my best to upload videos demonstrating some of the new features so you can see firsthand what’s going on. However, some of the more important fixes revolve around the GUI. SSMS has had tons of features added that may actually make it a complete environment. I can’t say yet how complete the feature set is going to be, but with features like finally being able to add our own SSRS reports to SSMS, the future looks bright. They’ve also broken up the disk usage report into smaller reports. Personally, I that’s one that I welcome the most because I have some DBs with some pretty big tables (the biggest being 10bill rows), and that disk usage report just never returns. So I can’t wait to sample that one.There are other significant changes that I’ll be talking about more as the CTP cycle goes on, and I’ll of course be monitoring the progress of bug reports and such. There is one more feature I’ve really got my eye on, and that’s the common criteria compliance enabled option. I’m going to dedicate some significant time to this in the future and I’ll be explaining it in pretty good detail, so stay tuned.OK… an extremely abridged fix list can be found here: https://go.microsoft.com/fwlink/?LinkId=71711 However, while I would ordinarily print it for you here, I’ve got another special surprise. I’ve got a pretty decent fix list for SSIS that’s already included in the SP. I don’t think you’ll find this anywhere else so enjoy.SSIS Fix List 433365 – Need to change SQLCE references to SQL Server Everywhere SQLCE has been re-branded as “SQL Server Everywhere”. All UI elements have been updated to reflect this change. 431678, 443197 – Improved SQLDumper functionality SSIS now always uses the native dumper instead of the managed implementation. In some circumstances dumps were not being submitted to Watson, this has been remedied. Furthermore it is now possible to specify the type of dump required using registry settings. Previously, SQLDumper was invoked for SSIS executables with only the SQLDUMPER_SEND_TO_WATSON flags specified. PSS needed the ability to configure SQLDumper for SSIS dumps to capture things like a full dump for debugging purposes. 449646 – Supportability — Include Error Constant along with HRESULT in logging Including the error constant in the log details (instead of just the HResult) for the most common errors helps both PSS and customers track down issues more efficiently. The following constants are affected: DTS_E_OLEDBERROR, DTS_E_THREADFAILED, DTS_E_PRIMEOUTPUTFAILED, DTS_E_THREADCANCELLED, DTS_W_MAXIMUMERRORCOUNTREACHED, DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER, DTS_E_INDUCEDTRANSFORMFAILUREONERROR, DTS_E_PROCESSINPUTFAILED, DTS_E_PRODUCTLEVELTOLOW 450793 – Import Wizard in SQL 2005 cannot import from text file that was exported from SQL 2000 The current UI enforces a maximum of 1000 sampled rows so there is no way for a customer to scan more data in order to effect a larger statistical sample. 428373, 478562 – Package fails to load after changing user locale and/or date/time locale settings In some instances, packages created on a machine with a specific date locale may fail to load if the package is opened on a machine with a different locale or date/time formats. 433051 – SQL Server Integration Services service failed to start after installing SP1 The service startup delay occurs because .NET verifies signature of the files and it needs to consult certificate revocation list (CRL) to check whether the certificate was revoked. If access to these URLs timeouts, service may timeout and fail to start. Please see this KB article which describes how you can configure the system to avoid this issue: http://support.microsoft.com/kb/918644 433396, 443017 – 64-bit runtime and pipeline interop assemblies don’t match 32-bit assemblies Certain interop assemblies were built as platform independent which meant that alignment on 64bit and 32bit platforms was different. The fix was to include platform-dependant interop assemblies. 433397 – Soft memory leak in service if continuously executing very short packages If packages are executed very fast (empty package in a loop), the objects that represent packages inside the service cannot be finalized quickly enough, which creates a memory leak until the pressure is removed. 433539 – Invalid sort order in legacy package list When attempting to edit a legacy (SQL 2000) DTS package with multiple versions from MSSMS, the versions listed in the SELECT PACKAGE dialog box are not sort chronologically (and appear to be sorted alphabetically). Caution must be used when opening the latest version because the latest version may not be at the top or bottom of the list as expected. 434497 – Certain keys not working when in DTS 2000 Package Designer Certain keys such as Home, End, Backspace and Enter do not work in SSMS after opening a legacy (DTS2000) package from SSMS. 434567 – The parameter is incorrect. (Microsoft OLE DB Provider for Visual FoxPro) after SP1 Regression: After installing SP1, connections to Visual FoxPro databases may not work correctly. This used to function as expected in RTM code. 434686 – Expression evaluator REPLACE function may lead to data corruption if source string is empty Under certain conditions when using the REPLACE function with specific input (all spaces) can result in a column in a row receiving a value from a previous row. 434878 – File System Task to create directory fails if directory is not empty If the Create Directory operation is selected and the UseDirectoryIfExists is set to True the task will fail with an error if the directory has a file in it. 435542 – Not all configurations have their paths updated when being deployed depending on their name in the deployment manifest In certain circumstances, not all configurations have their paths updated when being deployed depending on their name in the deployment manifest. 436221 – ExecuteSQL task has BypassPrepare set to true by default Many customers are hitting this problem when they have named parameters (denoted by “?”) in their queries. This causes the prepare() call to fail. Setting the bypassprepare property to true by default should take care of this problem without any functional effects on customers. 436793 – ForEach enumeration of ADO recordset can cause infinite loop when using checkpoints When using the SSIS ForEach loop to enumerate an ADO recordset, and checkpoints are enabled, the loop may iterate infinitely. 436921 – Combo box for variable selection in transform UIs A combo box is required for entering the VariableName property on various transformations such as the Recordset Destination adapter and the RowCount transformation. Currently it is free text. 436925 – Bad error for OLE DB Command and UPDATE Running an update statement through the OLE-DB Command component, such as is generated by the SCD transform, gives a poor and misleading error message when the UPDATE violates column nullability. 436926 – I/E wizard: Better error messages when “Optimize for many tables” option is used The wizard does not capture all the errors passed by the internal provider mechanisms, resulting in difficult to identity errors. 436942 – UnPivot: Remove a pass-thru column from upstream and fix unPivot afterwards saw a crash After a column is removed from an upstream component, opening the UnPivot transform will bring up the Fixup metadata window. Choosing to delete the invalid column can crash the environment. 437169 – Strings are not trimmed when they flow into a recordset destination If a recordset is populated with strings using the Recordset destination – those strings are padded with whitespace all the way to the full length of the data-flow column. 437339 – Potential caching of identical rows in Lookup reference buffer The code to cache rows for fully cached case may cache identical rows multiple times (performance problem) and fail to issue duplicate lookup row warning. 438001 – Cannot run a SSIS Package which is under Source Control but not checked out It’s not possible to run a package which is under source control without checking it out. With multiple developers working on these scripts having to check them out to run them is very disruptive as they are locked regularly. 438057 – Property description for package properties “VersionMajor” and “VersionMinor” are wrong The property description for the package property VersionMajor contains a typo. Moreover, the descriptions for the two properties are swapped. 438329 – Script Transform Wrapper ignores IsNull setting Trying to assign a False value to the _IsNull property of any column in the ScriptTransform will fail silently, and the value True will be assigned instead. Note that this is buy design since the user should assign a value to the actual property instead. The fix is to raise an error when this usage pattern is detected. 439132 – SSIS SmtpServer configuration property not set correctly Using a configuration file to change the SmtpServer property of the Smtp Connection Manager does not work. 440035, 485112 – Add Parameter Size Property to Execute SQL Task Execute SQL Task fails when using ADO.Net connection to execute a stored procedure with string output parameter. 440415 – Improved error messages related to offline operations in BIDS The old error message was ambiguous. The new error is “An error occurred due to no connection. A connection is required when requesting metadata. If you are working offline, uncheck Work Offline on the SSIS menu to enable the connection.” 440849 – ExecuteSQLTask: Data corrupted when using XML ResultSet Data can be corrupted when using ExecuteSQL task to save xml content to a variable. 441588 – Lookup component does not emit final cache row count The info messages output by the Lookup component do not show a final total of the number of rows cached (in full-cache mode). The fix should be a simple extra logging entry that displays the final count. Note that this is simple an informational change – the component does in fact cache all the rows correctly. 442309 – Data flow lines not displaying correctly. In some circumstances the lines between components hosted in containers may not be drawn correctly. 442740 – Derived Column: potential for data loss when overriding input columns In certain scenarios, intermediate results of calculations are not cached which can cause the final result to be erroneous. 442981 – Change in one path downstream from a multicast is seen in the other path In some circumstances a multicast followed by derived column on one path will show the same change being effected on the other path(s). 445853 – SSIS Packages fail with “Package has been cancelled” When running dtexec from a service, logging off of the box will cause dtexec to cancel. 445873 – SQL Task closes retained connections of type ADO.NET and ODBC ExecuteSql task needs to release the connection it is using at the end of the task. For Ado.net connections the retained property was not persisted. 447268, 484228 – Incorrect type metadata being passed to external systems resulting in loss of type fidelity and thus data corruption When you pass a value from an SSIS variable of type Int64 to a stored proc expecting a bigint, the value is corrupted. Several other bugs involving similar meta-data corruption issues. 447320 – Packages running concurrently may execute cached code from another package if the packages were copied using a file-based mechanism If one package is copied to another using the file system (or used as a template in VS.Net), and the original package contained either Script Tasks or Components, and the two packages are run concurrently, then under certain conditions the second package may run the code from the first due to duplicate project names leading to cached pointers. 448503 – Fast parse code for ISO dates calculates fractions of seconds incorrectly for DT_DBTIMESTAMP results When using fast parse to convert ISO8601 formatted strings into date types, the fractions portion of DT_DBTIMESTAMP is incorrectly treated as milliseconds, not billionths of a second. So for example, the representation for one and a half seconds should be 1.500000000 but instead will be 1.000000500 because the value 500 (the number of milliseconds in half a second) is set as the number of billionths of a second. This issue only occurs when parsing strings that use fractional representations of times (see ISO8601 section 5.3.1.3). 448716 – FTP task does not trim the file and may corrupt it when OverwriteFileAtDest is true FTP task does not trim the file and may corrupt it when OverwriteFileAtDest is true. 449370 – Script task ignores precompiled binaries if there are script breakpoints If there are breakpoints in a Script task, the task ignores a precompiled binary regardless of the debug mode. 20006520 – OLEDB Error connecting to Teradata Certain SSIS components attempt to set some optional properties on the Teradata OleDb driver which are unsupported. Since these properties are optional, the code should be checking the status of each property and only failing if one of the properties is not DBPROPSTATUS_OK, DBPROPSTATUS_NOTSUPPORTED, or DBPROPSTATUS_NOTSETTABLE. 486215 – Add Office2007 ACE support to connectivity options Office 2007 has released a new JET driver called ACE which we need to support in SSIS – Improve Error Logging of Connectivity APIs Improve our logging of OleDB calls in line with the feedback from CSS Databases