Jan 102015
 

CodePlex has a great new toolbox item for SSIS projects in Visual Studio 2012.  It’s called the SSIS Excel Refresh Task. It allows you to refresh a whole Excel file or single queries of your Excel sheet.  It works for PowerPivot models as well.

There isn’t a lot of instruction on how to install or use it, so I will blog it here. 

INSTALLATION
  1. Close Visual Studio 2012
  2. Install the SSDT Business Intelligence project templates for Visual Studio 2012 if you don’t already have them.  http://www.microsoft.com/en-us/download/details.aspx?id=36843  This will allow you to create an SSIS project.
  3. Download the SSIS Excel Refresh task from here https://ssisexcelrefresh.codeplex.com/
  4. Open Visual Studio 2012 and create an Integration Services project and create a package.
  5. You won’t see the new SSIS Excel Refresh Task in the toolbox the first time.  You have to right click inside the SSIS toolbox and select Refresh Toolbox.  The task will appear in the Common section of the toolbox.

image

PREPARING YOUR EXCEL WORKBOOK CONNECTION FOR AUTO REFRESH

If you are using Windows Authentication in your Excel Data Connection, then you need to ensure that the user driving the SSIS update will authenticate to your data source. If you are using SQL Authentication, then you need to set the password to be saved, so that SSIS won’t get a nasty message box looking for the password.  This means that anyone opening the Excel file will have access to the data and to the password, since the password is stored without encryption in the workbook.

  1. In the Data tab, select Connections.
  2. Open up the properties for each connection which will be refreshed automatically by the refresh task. 
  3. Click on the Defintion tab and check the Save Password check box

SNAGHTML77a40aa0

USING THE TASK
  1. Drag a sequence container onto the Control Flow.
  2. Drag the Refresh Microsoft Excel Task into the sequence container

image

3. MAKE SURE the Excel file you want to refresh is closed.  Check that it is not locked by another user, otherwise the next step will hang.  Test this by opening and closing the file before continuing.

4. Double click on the Refresh Microsoft Excel Task to edit the properties.  Click the NEW button to create a connection to your Excel file.

image

 

5. Once you create the connection, there is a short wait while the component finds the OLEDB Connections.  From here you can do one of two things: 

a)  You can refresh ALL of the connections in the workbook

image

or b) You can refresh specific connections in the workbook

image

6. The component isn’t notified when the update process is finished, so a timeout needs to be manually defined, or dynamically based on the file size.  To have it dynamically defined select the Dynamic Timeout Calculation. This is the best option for growing files.  Be aware that the calculation is not guaranteed, and refresh may still not be complete after the timeout. You’ll want to be aware of this and keep an eye on it. 

image

7. You need to set the IgnoreTimeoutWarning explicitly to turn off the warning on the task.  This is to remind you of the risk of incomplete updates due to the timeout issue described above.

image

8.  Selecting ‘Create a backup before refreshing will create a copy of your spreadsheet in the same folder where the spreadsheet is, and on successful update it will delete the copy.

image

The tricky bit is, you don’t get an error message if the update is not successful, due to a timeout or some other reason. 

That’s all there is to it.  Simple and effective.  As evidenced by an internet search on this task, it is much easier than writing custom VB scripts to do this for you.

Aug 142014
 

I was thrilled to discover the functionality to compare database data and schemas is available in Microsoft Visual Studio.  It not only allows you to compare two databases, but it will update the target database or write an update script for you to allow you to update the target database.

Most of this functionality has existed in Visual Studio Team System Database Edition since 2005.  Team System Database edition is used by database developers and administrators and includes advanced tools for database change management and testing.  Database schema comparison is now available in Visual Studio Premium and Visual Studio Ultimate since 2010.  Unfortunately it’s not in Professional or Express.

Having the database schema compare functionality in Visual Studio is awesome for developers who straddle several worlds. It’s nice to be able to build an application that affects or relies on a database, and also be able to compare the database schemas among environments, even if building the database doesn’t fall within your purview, all within one tool. 

The comparison is really easy to do. Look in Tools->SQL Server

SNAGHTML12d0a324

Select your source and target database

SNAGHTML12d22eb6

Click Compare and it will compare the databases.  Here I have compared the schemas of two databases.

SNAGHTML12d43ca6

The tool highlights the differences. In this case a new field called [newfield] exists in the table [Threads] in the source database but not the destination database.

Click the script icon to generate a SQL script for the changes to the destination database.

SNAGHTML1339c331

Or click the Update button to actually do the update of the destination database. 

How sweet is that? I know I’ll be using this regularly in all kinds of development.

Wordpress SEO Plugin by SEOPressor