Let Reporting Services use the User’s regional settings

US format by default

When designing a report currencies and dates use the US format, e.g. MM/dd/yyyy The user would like to read his report in his local format, e.g. dd/MM/yyyy.

Change the report for the user’s regional settings

In the report designer change the report property Language to Expression: =User!Language

A more elaborate explanation can be found at this link Reporting services tip about user regional settings

Report parameter default value to 1st and last day of previous month

Problem

For a report that selects data in a period with a start and end date, I needed to create a subscription that is sending an email the beginning of each month with the data from the previous month.

The report has 2 parameters @FromDate and @ToDate. The dataset of the report uses a select statement with a where condition: … WHERE Reportdate BETWEEN @FromDate and @ToDate.

The ReportDate is a DATE field (not a Datetime field)

when the user opens the report, he will be shown the 2 parameter fields with a Datetime picker to select a date from a calendar. But with a subscription the parameter fields need to have a default value. In this case the default value must be the 1st day and the last day of the previous month.

Solution with SQL Date field

The FromDate parameter has an expression as default value. The expression calculates the 1st day of the previous month:

=DateSerial(Year(DateAdd(DateInterval.Month, -1, Today())), Month(DateAdd(DateInterval.Month, -1, Today())), 1)

The ToDate paramet has a similar expression as default value. The expression calculates the last day of the previous month (no matter how many days). It does so by calculating the first day of the current month with DateSerial and then it subtracts one day with DateAdd.

=DateAdd(DateInterval.Day, -1, DateSerial(Year(Today()), Month(Today()), 1))

In case of SQL Datetime field

2 possibilities: either use the CAST function in the select statement, and keep the parameters and default expressions as they are for the Date field:

… WHERE CAST(Reportdate AS Date) BETWEEN @FromDate and @ToDate

Or leave the select statement as it is and change the default value of the ToDate parameter, by not subtracting one Day but the smallest necessary timeinterval. Howver the smallest interval in Reporting Services code is ‘Second’. If your datatime fields contain millisceconds you cannot give an absolute guarantee that it will catch all possible values. So only use this if you will never have values between 23:59:59.000 and 23:59:59.999

=DateAdd(DateInterval.Second, -1, DateSerial(Year(Today()), Month(Today()), 1))

See What Other Users Have Checked Out in TFS

I found the answer on Adam Prescott's blog

Prerequisites

Visual Studio Team Foundation Power Tools – With Visual Studio 2010 – in the Tools menu, select Extension manager. Download the Power tools and install (close VS 2010)

See what others have checked out

After the installation of the tools has finished:

• run VS 2010
• open the Source control explorer window
• In the VS 2010 main menu, select File / Source control / Find In Source Control / Status…
  
• In the dialog box, leave the path to $/ to find checked out sources in all workspaces, click on ‘find’
  
• You’ll be presented a list of files with the user names that are checked out

An easy way to rename oledb connection in a series of slowly changing dimensions

Problem

Over time we had developed many SSIS packages on a SQL 2008 server that transformed data over several databases. Each package used its own configuration file in which the same connections were defined again and again. To standardize the use of the database connections in all packages and to ease the use in development, testing and production environment, we decided to remove the connection definitions from the package configuration and put them in a separate configuration file. To achieve this, standardization of the connection manager names is required as well.

Instead of having one dtsConfig file for each package with:

• variables
• connection strings
  • the connection definition is repeated in different packages
  • the connection name is not standard

<?xml version="1.0"?>

<DTSConfiguration>

  <DTSConfigurationHeading>

    <DTSConfigurationFileInfo GeneratedBy="Mydomain\jdhondt" GeneratedFromPackageName="SomePackage" GeneratedFromPackageID="{86BF1AAA-9397-4DC5-A45A-CC89D6B478F0}" GeneratedDate="25/10/2011 12:07:28" />

  </DTSConfigurationHeading>

  <Configuration ConfiguredType="Property" Path="\Package.Connections[Sales].Properties[ConnectionString]" ValueType="String">

    <ConfiguredValue>Data Source=DBSERVER;User ID=DBuser;Initial Catalog=Sales;Provider=SQLNCLI10.1;Persist Security Info=True;Application Name=SSIS_Import;</ConfiguredValue>

  </Configuration>

  <Configuration ConfiguredType="Property" Path="\Package.Connections[Sales.ADO.NET].Properties[ConnectionString]" ValueType="String">

    <ConfiguredValue>Data Source=DBSERVER;User ID=DBuser;Initial Catalog=Sales;Application Name=SSIS_Import;</ConfiguredValue>

  </Configuration>

  <Configuration ConfiguredType="Property" Path="\Package.Variables[User::Value1].Properties[Value]" ValueType="Int32">

    <ConfiguredValue>10</ConfiguredValue>

  </Configuration>

  <Configuration ConfiguredType="Property" Path="\Package.Variables[User::Value2].Properties[Value]" ValueType="Int32">

    <ConfiguredValue>200</ConfiguredValue>

  </Configuration>

</DTSConfiguration>

We opted for several dtsConfig files per package:

• dtsConfig with variables specific to the connection

<?xml version="1.0"?>

<DTSConfiguration>

  <DTSConfigurationHeading>

    <DTSConfigurationFileInfo GeneratedBy="Mydomain\jdhondt" GeneratedFromPackageName="SomePackage" GeneratedFromPackageID="{86BF1AAA-9397-4DC5-A45A-CC89D6B478F0}" GeneratedDate="25/10/2011 12:07:28" />

  </DTSConfigurationHeading>

  <Configuration ConfiguredType="Property" Path="\Package.Variables[User::Value1].Properties[Value]" ValueType="Int32">

    <ConfiguredValue>10</ConfiguredValue>

  </Configuration>

  <Configuration ConfiguredType="Property" Path="\Package.Variables[User::Value2].Properties[Value]" ValueType="Int32">

    <ConfiguredValue>200</ConfiguredValue>

  </Configuration>

</DTSConfiguration>

• dtsConfig with connections specific to a database
  
  
  
  • sometimes an oledb and an ADO connection
    
  • the connection are renamed accordingly ( .._oledb, .._ADO)
    
  • the same dtsConfig file can be used by more than one SSIS package

<?xml version="1.0"?>

<DTSConfiguration>

  <DTSConfigurationHeading>

    <DTSConfigurationFileInfo GeneratedBy="Mydomain\jdhondt" GeneratedFromPackageName="SomePackage" GeneratedFromPackageID="{86BF1AAA-9397-4DC5-A45A-CC89D6B478F0}" GeneratedDate="25/10/2011 12:07:28" />

  </DTSConfigurationHeading>

  <Configuration ConfiguredType="Property" Path="\Package.Connections[Sales].Properties[ConnectionString]" ValueType="String">

    <ConfiguredValue>Data Source=DBSERVER;User ID=DBuser;Initial Catalog=Sales;Provider=SQLNCLI10.1;Persist Security Info=True;Application Name=SSIS_Import;</ConfiguredValue>

  </Configuration>

  <Configuration ConfiguredType="Property" Path="\Package.Connections[Sales.ADO.NET].Properties[ConnectionString]" ValueType="String">

    <ConfiguredValue>Data Source=DBSERVER;User ID=DBuser;Initial Catalog=Sales;Application Name=SSIS_Import;</ConfiguredValue>

  </Configuration>

</DTSConfiguration>

The big problem lies with the use of the package designer to rename a connection in existing Data Flow Sources or Slowly Changing Dimensions. Each time you dare to edit one of these and change the connection manager, the column names are lost and you have to redefine every one of them. The other problem is that you cannot easily find where the connection managers are being used.

Solution

• Make a backup of your package file.
  
• Use Visual Studio 2008
  
• With the package editor, add the new connection names, keep the old ones.
  
• Save the package
  
• close the designer
  
• In the solution explorer, right click on the package and select ‘view code’ in the dropdown menu.
  
• In the xml code, look for the ConnectionManager definition of the old connection and take note of the DTSID value
  
  
  
  

  
  

  <DTS:ConnectionManager>

  <DTS:Property DTS:Name="DelayValidation">-1</DTS:Property>

  <DTS:Property DTS:Name="ObjectName">Sales</DTS:Property>

  <DTS:Property DTS:Name="DTSID">{495F9DDC-1119-4A10-B67C-BEE6F3C35E72}</DTS:Property>

  
  
  

• now look for the ConnectionManager definition of the new connection and take note of the DTSID value
  
  
  
  

  <DTS:Property DTS:Name="DelayValidation">-1</DTS:Property>

  <DTS:Property DTS:Name="ObjectName">Sales_oledb</DTS:Property>

  <DTS:Property DTS:Name="DTSID">{06ACAA06-FE6F-4F94-B8D2-4164E9D33F9E}</DTS:Property>

  
  
  
  
• Find and replace the old DTSID everywhere in the code (except on the connectionManager definition), e.g. somehwere in the code where a slowly changing dimension is used:
  
  
  
  

  <connections>

  <connection id="10" name="OleDbConnection" description="The OLE DB runtime connection used to access the database." connectionManagerID="{06ACAA06-FE6F-4F94-B8D2-4164E9D33F9E}"/>

  </connections>

  
  
  
  
• Save the package file and close the code editor window.
  
• Open the package with the package designer. Verify the connections in the Data flow Sources or slowly Changing Dimensions, The new name should be in there.
  
• Remove the old connection from the connection manager.
  
• Save and close the package.
  
• Edit the old dtsconfig file and delete the old connection definition.
  
• Open the package.
  
• Setup the package configuration to use the new database dtsconfig file.