SSRS exporing as Excel 97-2003 (.xls) and Excel 2012 (.xlsx)

Format should be excel, for downloading as .xlsx file format.

http://tocgjtim1pv.bns.bns/Reportserver?/RFS%20Reports/Major+Projects+-+Singleview&rs:Format=excel


Format should be EXCELOPENXML, for downloading as .xlsx file format.

http://localhost/Reportserver?/RFS%20Reports/Major+Projects+-+Singleview&rs:Format=EXCELOPENXML

Extreme wait-time when taking a SQL Server database offline

When it takes long time to make a database offline try with following sql.

ALTER DATABASE <dbname> SET OFFLINE WITH ROLLBACK IMMEDIATE

If it does not help, kill the session.

First find out the spid using following command.

EXEC sp_who2

KILL <SPID>

Downgrading SSIS package from 2014 to 2012.

The Workaround (Step by Step)

1. If you haven’t done so, open DTSX Version 2012/01 schema
2. Open the SSDT-BI VS 2013 DTSX package in a text editor
3. Search for PackageFormatVersion. Change the value from 8 to 6
4. Replace all instances of Microsoft.Package with SSIS.Package.3
5. Replace all instances of Microsoft.Pipeline with SSIS.Pipeline.3
6. Search for all instances of DTS:ExecutableType=”
   
7. For each ExecutableType value (Microsoft.ExecuteSQLTask, etc.), go to theDTSX Version 2012/01 schema and search under

   <xs:simpleType name="AnyNonPackageExecutableTypeExecutableTypeEnum">

   for the correct ExecutableType. For example, Microsoft.ExecuteSQLTask in SSIS 2014 should be Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ExecuteSQLTask, Microsoft.SqlServer.SQLTask, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc9 in SSIS 2012.
8. Replace ALL instances (including CreationName attributes) of the ExecutableType value with the one you got from Step 7
9. Search for all instances of componentClassID=”Microsoft
   
10. For each componentClassID value (Microsoft.DerivedColumn, Microsoft.Lookup, etc.), go to the DTSX Version 2012/01 schema and search under

    <xs:simpleType name="PipelineComponentComponentClassIDEnum">

    for the corresponding GUID.
11. Replace ALL instances (including CreationName attributes) of the componentClassID value with the one from Step 10.
12. Now open the package in Visual Studio 2012. If your package does not have upgraded components, then it should open by now. Otherwise, read further.

The Exception

Some components, such as the Script Component in the Data Flow Task, have been upgraded from SSDT-BI 2012 to SSDT-BI 2014. In this case, it’s not only the property / attribute value that is different, but also the structure of the XML node. In this case, replace the Version 2014/01 schema value (e.g. componentClassID=”Microsoft.ManagedComponentHost”)  with any valid DTSX Version 2012/01 componentClassID. Then open the package in VS 2012. The component will be marked as with error, that’s OK. If you have followed all the steps above, your package should at least load the designer by now. Now remove this erroneous component and re-do it in VS 2012 itself.