How to Migrate from SQL 2005 to 2014

On April 12, 2016 Microsoft will no longer offer extended support for SQL Server 2005.  This means no more security updates or hotfixes.  However, the bigger reason to upgrade is the fantastic performance improvements offered in SQL 2014.  SQL Server 2014 has been shown to be 13X faster than 2005, and that is before taking advantage of the in-memory OLTP.  In addition there are features such as AlwaysOn availability groups, updateable columnstore indexes, T-SQL Intellisense and backups directly to the cloud, just to name a few.  For a more complete listing read What’s New in SQL Server 2014 since SQL Server 2005.

Preparation is key.  Microsoft has provided a number of tools to make migrating your databases easier.  You’ll want to use these tools to assess and plan your migration well ahead of time.  Support for Windows Server 2003 is also ending soon, which may impact your decision making.  Part of your assessment will be to ensure your environment is suitable for SQL Server 2014 before migrating your databases. SQL Server 2014, of course, has minimum hardware and software requirements.   Microsoft recommends a minimum of 4GB of memory, to be increased as database size increases, and processor speed of 2.0GHz or faster. The OS depends on which version of SQL Server you are installing. 

There are two ways to migrate from SQL Server 2005 to SQL Server 2014.  You can do a clean install and restore from backup, or you can do an in-place upgrade. The clean install may sound appealing from a Windows registry and system stability standpoint.  However it means restoring all of your databases, and any custom settings and properties you may have changed.  Over 10 years a lot of tweaks to settings can have been made, and it could be very time consuming to restore them all.  It may even mean purchasing another OS license.

You can do an in-place upgrade to SQL Server 2014 from any version as far back as SQL Server 2005.  You cannot do the in-place upgrade for versions prior to that.  You would need to upgrade a prior version to 2005 or 2008 and then upgrade again to 2014. You do need to consider the edition you are upgrading from, Standard, Developer, Enterprise, etc to determine the supported upgrade path.  With the SQL Server 2014 in-place upgrade you will be able to upgrade SSIS packages, SSAS and SSRS.

I highly recommend you install and run the Upgrade Advisor prior to doing the in-place upgrade.  This tool will analyze components of your previous version and identify issues you may need to address before upgrading to SQL Server 2014.  Upgrade Advisor can be installed remotely to analyze all supported components of SQL Server, except SSRS. The remote computer where you are installing Upgrade Advisor must meet the Upgrade Advisor prerequisites.  If you want to analyze SSRS you must install Upgrade Advisor on the report server itself.

You can install the Upgrade Advisor from the the SQL Server setup, or download it from Microsoft. 

image

In addition to the Upgrade Advisor, I found this great resource, Upgrading to SQL Server 2014: A Dozen Things to Check, written by Thomas LaRock. It is not specific to migrating from SQL Server 2005, but it has some great tips for preparing for a smooth SQL upgrade.

Once you have addressed any pre-installation issues, you are ready to perform the upgrade.  I won’t run through the step-by-step as there are many articles documenting this, such as this one.

There are many great reasons to upgrade your SQL Server, and with the end of extended support in sight, now seems like a good time.  Preparation for your upgrade is key, and Microsoft has provided lots of tools to make it easier.

Loop through dynamic SQL statements in SSIS

This is a situation where the data from a number of tables with the same structure needs to be imported into a single table.  Rather than hard coding multiple data flows from source to destination, you can loop through each SQL statement using a single data flow by building a dynamic SQL statement.  This simple package loops through each table name to accomplish just that.  It also has a secondary loop to pivot some hard coded week numbers in the field names.

image

The data source is a series of tables with the same structure. Each one holds a different set of planning data.  The fields have the fiscal month hard coded into the name, rather than have Week Number as an attribute.

image

The destination not only needs to map multiple tables to a single table, but it also needs to pivot the fiscal month weeks.

image

 

LOOP THROUGH TABLES

To accomplish this I hard coded the table names into the For Each loop, but an object variable could just as easily done this. 

image

 

image

 
LOOP THROUGH FIELD NAMES (WeekNo)

 

image

image

 
VB SCRIPT TASK TO WRITE THE DYNAMIC SQL STATEMENT

image

In the script portion, write a simple script which uses the variables and embeds them in the SQL statement you are writing.  This outputs to your SQLStatement variable to be used in the data flow.

    Public Sub Main()
        '

        Dim WeekNo As Integer
        Dim TableName As String
        Dim SQLStmt As Object

        WeekNo = Dts.Variables("WeekNo").Value
        TableName = Dts.Variables("TableName").Value
        SQLStmt = "SELECT [Version Code] as Plan_Version, [Fiscal Year] as FiscalYear,[Fiscal Month Sequence] FiscalMonth, " & WeekNo & " as FiscalWeek, convert(numeric(38,20),[SLS NET $ W" & WeekNo & "]) as Sales, FROM " & TableName
        Dts.Variables("SQLStatement").Value = SQLStmt

        Dts.TaskResult = ScriptResults.Success
    End Sub

To set up the data flow, you must first enter a valid SQL Statement in the SQLStatement variable.  Your variable will then be replaced with a new one during each loop. 

image

I hope you find this useful.

Exporting InfoPath repeating table data with Nintex Workflow

A client wanted to store the data from an InfoPath form with a repeating table as a step in a Nintex Workflow.  Users were submitting expenses in SharePoint and could have multiple expenses in the form. The data was pulled out of the InfoPath form by querying the XML, and was stored in the workflow variables for use further in the workflow. The relevant portions of the workflow look like this:

imageimage

image
GET LINE ITEMS

Add a Query XML action to your Nintex workflow called “Get line items”.  Set up a variable call LineItems which is of Collection type.  Configure the action as follows:

image

If you don’t know the XPath you can look at your form in InfoPath, right click on the field you are interested in, and select Copy XPath.  This will copy it to your clipboard, and you can paste it into your action.

image

FOR EACH LINE ITEM

Add a For Each action to your workflow called “For each line item”. This will loop through each of the items queried in Get Line Items, above, and store the current line in a text variable. Add a variable to your workflow called LineItem which is a Single line of text.  Configure the action as follows:

image

EXTRACT DATA FROM LINE ITEMS

Add the relevant logic to your workflow and incorporate the Query XML actions to pull the individual fields you want from your InfoPath form.  From the screenshot of the flow, above, you would configure Extract Submit Date as follows:

image

The relevant data points you have queried in your Query XML actions are stored in the workflow variables, and are ready to be used to build and execute a SQL Insert statement during each loop.  (***UDATE 2014-08-11:  A reader has pointed out that the XML source in this screenshot should actually be the string variable “Line Item”.  I will check this at a later date, but am updating the post to point readers in the right direction.***)

BUILD SQL INSERT STATEMENT

Drag a Build String action into your workflow, within the For Each loop.  Add a variable called InsertStatementForSQL of type Single line of text .  Build your insert statement in the Text portion of the action and store it in the InsertStatementForSQL variable.  Use the Insert Reference button to insert the workflow variables.  Note that if you are inserting a string value from a variable into the SQL table you need to surround it with single quotes so SQL will recognize it as a string.

image

EXECUTE SQL STATEMENT

Drag an Execute SQL action onto your workflow, within the For Each loop.  In this case I’ve used a Workflow Constant for the connection.  You can configure your connection string as necessary.  In the query, click on the Insert Reference button and select the InsertStatementForSQL variable you populated in the previous step.

image

Voila, your workflow will loop through each repeating line of your form and build and execute a SQL statement inserting that data into a table.

In this case the client also want the data displayed in an email.  My next post talks about that – Creating an HTML Table in a Nintex Workflow

How To Use a SharePoint List as a Data Source in your SSIS Package.

Sometimes you run into a situation where you want to allow users to update data in a simple fashion, and incorporate the data into a data warehouse.  For example the client’s ERP system maintains the data element called Departments but doesn’t have the ability to store Department Groups for reporting since it is not relevant to the ERP system.  Using a SharePoint list as a data source can be an easy solution.  Users can update it very simply, your ETL package can add or update new Departments from the ERP system via the data warehouse, and you can pull the user entered data back into the data warehouse.  There are some drawbacks to it, such as lack of data integrity checks on the SharePoint side.  But if you build your ETL package right, you can correct any integrity issues which might be created by user error. 

Here is what I will demonstrate, using a SharePoint list to manage the applying Department Groups to Departments which are maintained in the accounting system:

PART 1 – DOWNLOAD AND INSTALL SSIS TOOLBOX ITEMS

PART 2 – USE THE SHAREPOINT LIST AS A DATA SOURCE

PART 3 – INSERT RECORDS INTO THE SHAREPOINT LIST

PART 4 – UPDATE RECORDS INTO THE SHAREPOINT LIST

PART 5 – DELETE RECORDS INTO THE SHAREPOINT LIST

PART 6 – BUILD DATA INTEGRITY INTO YOUR PACKAGE

PART 1 – DOWNLOAD AND INSTALL SSIS TOOLBOX ITEMS

1. From the server where you will be building the SSIS solution, open your browser and navigate to this URL. http://sqlsrvintegrationsrv.codeplex.com/releases/view/17652

2. Download, run and follow the instructions to install SharePointListAdaptersSetup.msi (2008) or SharePointListAdaptersSetupForSqlServer2005.msi

image

3. Close BIDS if it is open

4. Open BIDS from the Tools menu select Choose Toolbox Items.  Go to the SSIS Data Flow Items tab. – **See SQL Server 2012 Update at the end of this post.

image

5. Add a checkmark to the SharePoint List Destination and SharePoint List Source items.

6. Open up or create a new Integration Services solution. Go to the Data Flow tab.

7. Open up the Toolbox

8. Drag SharePoint List Source from the General section…

image

…to the Data Flow Sources section.

image

9. Drag SharePoint List Destination from the General section…

image

…to the Data Flow Destinations section.

image

Now you are ready to use a SharePoint List as a source or destination in your packages.

PART 2 – USE THE SHAREPOINT LIST AS A DATA SOURCE

1. Create a Data Flow in your package.

2. Go to the Data Flow tab and drag the SharePoint List Source into the data flow as your source.

image

3. Double click on the source.  Scroll down to the bottom where it says SiteURL.

image

4. Paste the URL for the SharePoint site into the SiteURL (exclude anything from the word Lists onward in the URL)

Example: if your SharePoint site URL for Lists is

 http://ca-shpt01/Reporting/Lists/Department%20Groups/AllItems.aspx

then only type

http://ca-shpt01/Reporting/

5. Type the SiteListName.  For example if your List name is Departments,

image

then type “Departments”.

image

6. If you are wanting to import data from a particular view of this list in SharePoint, other than the default view, “MyView” for example:

image

then add the name of the view in the SiteListViewName, otherwise leave it blank.

image

7. Click the Refresh button at the bottom of the box to load the SharePoint List metadata into your Data Flow.

image

8. Then click on the Column Mappings tab to see the columns available from your SharePoint list.

image

9. Drag a Data Flow Destination onto your data flow and proceed as normal to pull data into your destination from the SharePoint list.

You’ll need to know a little about SharePoint lists to understand what data lives in which column.  You’ll need the ID column if you will be doing any updating from the data warehouse into the SharePoint list.  The Title is whatever item is the linked item in the SharePoint list – meaning if a user clicks on one of the items in the SharePoint list, which field is set up as the hyperlink to the item (usually the first column visible in the list).

image

Because there is often a  push and a pull of data to and from the SharePoint list I tend to create a table in the data warehouse  specifically for the data from the SharePoint list and I let the source data dictate the table structure by clicking on the New button for the “Name of the Table or view”.  In this way, when the data gets pushed back to SharePoint there are no data type issues.

PART 3 – INSERT RECORDS INTO THE SHAREPOINT LIST

Keep in mind that the SharePoint list does not manage any data integrity, so if you add duplicate records with the same linked “Title”, Department Name in this case, you will get duplicate records in your list.  Be sure to configure your data source to insert only new records, not existing records. Here we will add any new Departments that have been added to the ERP system (if any) and push them up to SharePoint.

1. Add a Data Flow to your package.

2. Go to the Data Flow tab and drag an OLE DB source as your source. Configure your source to pull any new Departments that you want to insert into the SharePoint list.  In this example SP_Department is the data which has been imported from the SharePoint list, and DIM_Department holds the Departments imported from the ERP system:

SELECT   �
DP.Department_Code,
DP.Department_Description
FROM  SP_Department as SP RIGHT OUTER JOIN
dbo.DIM_Department as DP ON SP.Department_Code = DP.Department_Code�
    WHERE      SP.ID IS NULL

3. Drag the SharePoint List Destination into the data flow as your destination.

image

4.  Double click on the SharePoint List Destination and configure the SiteUrl, SiteListName and SiteListViewName the same way you did in Part 2 steps 4-6 above.  In addition, ensure that the BatchType is set to Modification.  “Modification” is used for Updates and Inserts, “Deletion” is of course for Deletions.

image

5. Click on the Refresh button in the bottom left of the Advanced Editor window and select the Input Columns tab.  Map the Source columns to the Destination columns.  Since we are INSERTING records, be sure NOT to map to the ID field. Doing so would have the data flow expect an update rather than an insert.

image

Note that the Department_Description is being mapped to the Title field, which is the linked SharePoint field described in Part 2 – item 9, above.

6. Run your data flow task and check the SharePoint list for the added records.

PART 4 – UPDATE RECORDS INTO THE SHAREPOINT LIST

Here we want to update the Department descriptions in SharePoint, in case a user has decided to edit it.  The only item the user should be editing is the Department Group.

1. Add a Data Flow to your package.

2. Add an OLE DB Source to the Data Flow tab and configure the source with the correct query to update the Department descriptions for Departments that already exist in the SharePoint list.  For example:

SELECT   �
SP.ID,
DP.Department_Code,
DP.Department_Description
FROM  SP_Department as SP INNER JOIN
dbo.DIM_Department as DP ON SP.Department_Code = DP.Department_Code 

3. Drag the SharePoint List Destination into the data flow as your destination.

image

4.  Double click on the SharePoint List Destination and configure the SiteUrl, SiteListName and SiteListViewName the same way you did in Part 2 steps 4-6 above.  Esure that the BatchType is set to Modification. 

image

5. Click on the Refresh button in the bottom left of the Advanced Editor window and select the Input Columns tab.  Map the Source columns to the Destination columns.  Since we are UPDATING records, BE SURE TO map to the ID field. Without the ID the data flow will implement an insert rather than an update.

image

6. Run your data flow task and check the SharePoint list for the updated Department Descriptions.

PART 5 – DELETE RECORDS INTO THE SHAREPOINT LIST

Here we want to delete any Department records from the SharePoint list that a user may have entered in error. The user training indicated to users that Departments should only be added from the ERP system and not by users.

1. Add a Data Flow to your package.

2. Add an OLE DB Source to the Data Flow tab and configure the source with the correct query to select the Departments for deletion from the SharePoint list.  The only field you need in the result is the ID field which maps to the ID in the SharePoint List.  If the ID is in this query it will get deleted from the SharePoint list.  In this example we will remove any duplicate records, and remove any Departments that don’t exist in the ERP system:

–DELETE DUPLICATE Department Codes.  Take the oldest record.
SELECT ID FROM SP_Department
WHERE Department_Code IN (
SELECT DP.Department_Code FROM  SP_Department as SP LEFT OUTER JOIN
dbo.DIM_Department as DP ON SP.Department_Code = DP.Department_Code�
GROUP BY DP.Department_Code HAVING COUNT (sp.id) > 1)
AND ID NOT IN
(SELECT MIN(ID) FROM SP_Department
WHERE Department_Code IN (
SELECT DP.Department_Code FROM  SP_Department as SP LEFT OUTER JOIN
dbo.DIM_Department as DP ON SP.Department_Code = DP.Department_Code�
GROUP BY DP.Department_Code HAVING COUNT (sp.id) > 1))

UNION
–DELETE Department Codes that don’t exist in ERPSELECT SP.ID FROM  SP_Department as SP LEFT OUTER JOIN
dbo.DIM_Department as DP ON SP.Department_Code = DP.Department_Code
WHERE      DP.Department_Code IS NULL

3. Drag the SharePoint List Destination into the data flow as your destination.

image

4.  Double click on the SharePoint List Destination and configure the SiteUrl, SiteListName and SiteListViewName the same way you did in Part 2 steps 4-6 above.  In addition, ensure that the BatchType is set to Deletion.

image

5. Click on the Refresh button in the bottom left of the Advanced Editor window and select the Input Columns tab.  Map the ID input column to the ID destination column.

image

6. Run your data flow task and check the SharePoint list for the deleted Departments.

PART 6 – BUILD DATA INTEGRITY INTO YOUR PACKAGE

Putting this all together will enforce the data integrity for this simple list.  Here is what this package looks like.

image

Here is what each step does.

1.  Truncate Tables: Truncates these tables –

DIM_Department
DIM_Department_Group
SP_Department

2. Populate DIM_Department – populates the DIM_Department table from the ERP system.

3. Retrieve Dept Group from SP – Populates the DIM_Department_Group table from another SharePoint List.  This is used as a drop down in the Department SP list for users to select the correct department.

4. Retrieve SP_Depts – Populates the SP_Department table from the SharePoint list. See Part 2 above.

5. Update SP Dept Descriptions – updates any modified Department descriptions.  See Part 4 above.

6.Add Missing Depts to SP – adds any new or missing Departments to the SharePoint list.  See Part 3 above.

7. Delete invalid Depts from SP – deletes any invalid Departments from the SharePoint list. See Part 5 above.

8.  Truncate SP_Depts – Truncate the SP_Department table in preparation for reload of corrected data.

9. Retrieve Updated SP_Depts – Populate SP_Department table from the updated SharePoint list. See Part 2 above.

10.  Update Dept Group Codes – Updates the DIM_Department table with the Department_Group_ID.

UPDATE DIM_Department
   SET Department_Group_ID = SP.Department_Group_ID
   FROM SP_Department AS SP INNER JOIN
DIM_Department as DP on DP.Department_Code = SP.Department_Code

This may seem like a lot of work, but it actually doesn’t take that much time once you get the hang of it.  Of course if you have more complex lists or require a lot of lists to manage this kind of data, you will want to look into other tools.  This is a good solution to the occasional one-off with a fairly simple data requirement, where the client doesn’t have another tool available to handle it.

Update April 24, 2012

If you are populating date fields, see this post.  http://thedataqueenblog.azurewebsites.net/2012/04/populating-date-fields-in-sharepoint-using-ssis/

Update May 2, 2012

SQL Server 2012:   All components that are installed on the local machine now automatically appear in the new SSIS Toolbox. When you install additional components, right-click inside the toolbox and then click Refresh Toolbox to add the components. The SharePoint List Destination and the SharePoint List Source appeared in the “Common” folder in the SSIS Toolbox when I refreshed the toolbox.