Populating Date fields in SharePoint using SSIS

This is an addendum to my previous blog post How to use a SharePoint list as a data source in your SSIS package.

If you are populating a SharePoint lists with a field of date type, you need to format the date as text, in the following format, in order for SharePoint to accept the input from SSIS.

‘yyyy-mm-dd’ or for one digit months or days ‘yyyy-m-d’

So if you’re using a SQL Query you can write something really ugly, like this:

SELECT �
CAST(DATEPART(Year, GETDATE()) AS varchar(4)) + ‘-‘ + CAST(DATEPART(Month, GETDATE()) AS varchar(2)) + ‘-‘ + CAST(DATEPART(Day, GETDATE()) AS varchar(2)) AS [Adjusment Date]

Trigger an SSIS Package from SharePoint using Nintex Workflow

There are many times when my clients want to be able to refresh data on demand, without having to wait for a nightly data warehouse refresh. Quite often this occurs when they are updating data in SharePoint lists which get fed into the data warehouse and from there into cubes and reports. There is a very simple way to allow a user to trigger the data refresh of just their specific piece of data, using Nintex Workflow. If you don’t know about Nintex, it is a third party product which adds a drag-and-drop workflow designer and advanced workflow features to SharePoint. It is inexpensive, and a great way to empower users to manage their business processes.

In this example a user has updated a series of SharePoint lists with current vendor scorecard data and would like to see it reflected in the cube and reports.

SSIS PACKAGES BEST PRACTICES

You will need to create an SSIS package which triggers the data refresh desired by the user. When building my SSIS packages which populate a data warehouse I like to keep it modular, with related actions in individual packages, and then use a master package to trigger the packages in the correct order. In this way I can trigger smaller jobs simply by creating additional master packages to run the relevant packages which already exist. This is best practice since I only have to maintain the logic in one package, which can then be used by multiple master packages. I normally create one package for each of my dimension (attribute) tables and one for each of my fact (transactional) tables wherever possible, unless they are interdependent. I try to keep each package as modular as possible so I can trigger them in different sequences where necessary.

Using a master package to trigger a sequence of packages has many advantages over doing multiple steps in SQL Server Agent. I only trigger one master package using a SQL Server Agent Job. It allows me to keep the order of running a sequence of packages in SSIS where I can add annotations to remind myself why certain packages need to be run before others. This helps me remember the interdependencies when I am making changes. It also allows me to control the checkpoints, which gives me the ability to rollback multiple steps where necessary. And it helps me when reviewing the SSIS logs. I can see how long the master package takes to run from beginning to end, and compare that over time.

CREATE A NEW SSIS MASTER PACKAGE

For this example I have a master package which populates the entire data warehouse and refreshes the data cube at the end. This is run nightly. I will add an additional master package, to be triggered by the user, which only refreshed the vendor scorecard data from the SharePoint lists into the data warehouse and refreshes the data cube. We’ll call it MASTER Vendor Scorecard Refresh.

CREATE A SQL SERVER AGENT JOB

Now create a SQL Server Agent Job with one step which triggers the SSIS package you just created. Do not schedule this job, since it will be triggered manually by the user.

You will want to add a notification to email you when the job completes while you are doing your testing. You could change the notification to when the job fails once you know it’s working correctly.

CREATE A SHAREPOINT LIST AND WORKFLOW

Create a SharePoint list, called Vendor Scorecard Data Refresh. Add one item to the list. You could name the item Trigger Vendor Scorecard Data Refresh Workflow. In your List tool bar select Workflow Settings, Create a Workflow in Nintex Workflow.

Leave the template as Blank and click the Create button.

In the bottom left of your screen choose the workflow action type of Integration.

From the resulting actions which appear in the Workflow Actions pane, drag the Execute SQL action onto your workflow.

Double click in the center of the Execute SQL icon to configure the action. Select OLEDB, key in your connection string.

Data Source=yourserver;Initial Catalog=yourdatabase;Provider=SQLNCLI10.1;Integrated Security=SSPI;

check the box for Use Windows authentication. Key in the username and password that has permissions to to trigger the SQL Server Agent Job. Enter the query which will trigger the job.

EXEC msdb.dbo.sp_start_job N’Vendor Scorecard Refresh’

Click on Save to save the action. Publish the workflow by clicking on the Publish button.

TRIGGER THE WORKFLOW MANUALLY

Navigate to your Vendor Scorecard Data Refresh SharePoint list. Click on the dropdown beside your one item and select Workflows.

Select the workflow you want to start. You can store more than one workflow on a SharePoint list, but this can get confusing to users so I recommend you keep it to one workflow per SharePoint list unless there is a very good reason to do otherwise.

Click the start button to trigger the workflow.

If you set up notifications on the SQL Server Agent job you will receive an email when the job completes. The email will tell you if the job was successful.

If you did not set up notifications you can check the history of the job. The history will not appear on the job until the job completes, so you will need to wait a few minutes for the steps in your SSIS package to complete before you can check if it ran successfully. In SSMS right click on the SQL Server Agent Job and select View History. You will see a green checkmark beside the date and time if the job ran successfully. If it fails you will see a red X.

That’s it, your done. Users can now trigger their own data updates as required.

Dynamically filter a Drop Down on your SharePoint list by another User Selected Value

This is one in a series of posts on using InfoPath. If you are new to SharePoint lists and InfoPath you might want to start with the Best Practices.

Best Practices for Cascading SharePoint Lists using InfoPath Forms

Use a SQL Server data source for an InfoPath pick list

Continuing with the same example I used in the Best Practices post, I’m going to make a change to an existing InfoPath form. Navigate to the SharePoint list called Supply Chain Scorecard Metrics and add a new item. In the Section drop down, notice that I’ve included the Goal as part of the Section description (Goals are Improve, Maintain and Track).

What I could do is have the user choose the goal, and then filter the Section list to show only those Sections which have that Goal. Here’s how we can do that.

ADD THE GOAL TO YOUR LIST

Navigate to your Supply Chain Scorecard Metrics list and in the List menu click on the List Settings. Add a column called Goal_ID with type of Number. Require that the column contains information. When the user selects a Goal we will store the Goal ID in the Metrics list, and then use that to filter the drop down list of available Sections the user can choose from.

ADD THE GOAL TO YOUR FORM

Navigate to your Supply Chain Scorecard Metrics list and in the List menu click on the Customize Form button to open up the InfoPath form.

Click OK and enter a password if necessary. Click Yes when it asks if you want to update the fields from the SharePoint list.

Add a row to your InfoPath form above Section by highlighting the Section row, right click and select Insert –> Rows Above.

From the Fields window, drag the Goal_ID field into the right hand cell in the row you just created.

Delete the label that says Goal_ID:, and type in Goal in the cell to the left of the Goal_ID textbox.

Right click on the Goal_ID text box and select Change Control –> Drop-Down List Box

Right click again and select Drop-Down List Box Properties.

Select Get choices from an external data source and then from the data source drop-down choose Supply Chain Scorecard Section.

Set Value = Goal_ID and Display name = Goal. Check the box that says Show only entries with unique display names. Click OK.

FILTER THE SECTION CHOICES BY THE GOAL SELECTED

Right click on the Section drop down list box and select Drop-Down List Box Properties.Beside the Entries box, click on the tree icon.

Click on the Filter Data button.

Click on the Add button.

Now we will specify on what to filter the Section list. In the first drop down select Goal ID. Leave the second drop down as “is equal to”. In the third drop down select “Select a field or group”

Change the data connection in the drop down to Main. Navigate to dataFields and then to Goal_ID.

Click all of the OK buttons. Publish your InfoPath form.

Add a new item. Select Improve as your Goal. Notice that the Section drop down has only those Sections that have a Goal of Improve.

And that is how you filter a drop down from another value in your list.

Use a SQL Server data source for an InfoPath pick list

This is one in a series of posts on using InfoPath. If you are new to SharePoint lists and InfoPath you might want to start with the Best Practices. The example I will be using here is a continuation of the one started in the Best Practices post.

Best Practices for Cascading SharePoint Lists using InfoPath Forms

Dynamically filter a drop down on your SharePoint list by another user selected value

Before you even start, be aware that using this method allows the password for the SQL connection to be stored in text format directly in the data connection. Be sure you put security measures in place to disallow others from navigating to the connection file in the SharePoint library, and create a database login with a low level of read-only security to information which is not sensitive. We are using the data connections for simple pick lists, so this shouldn’t be an issue.

Create a SharePoint library to store the data connections for InfoPath. The Type of library is a Data Connection library. Call it DataConnectionsInfoPath. The data connection information for the InfoPath connections will be stored in text format so be sure that security to this folder is limited to only administrators.

Create a SQL login to the database to which you will be connecting to get the data used in the pick lists for SharePoint. The login information will be stored in the data connection as text, so be sure to create a separate login with read only capabilities to only those tables required for the InfoPath pick lists used in SharePoint. Choose a password that is not commonly used by other admin connections. (C’mon, you know you reuse passwords).

Create a view in your database from which the pick list will be fed. The pick list can only be sourced from one table, so by creating a view you can control the values that show up in that table. If you need to filter out, change or add any values in the future, you can use the view to do it without having to change the data connection or the InfoPath form.

Navigate to the SharePoint list which has already been set up, in this case Supply Chain Scorecard Metrics from my previous blog post. Open the InfoPath form by clicking on the Customize Form button in the List menu.

To get the pick list for Site from a SQL Server view do the following.

Right click on the Site text box on the form and change the control to a drop-down list box.

Right click again and edit the properties.

Select Get choices from an external data source. Click the Add button beside Data source.

Create a new connection to Receive data.

Receive data from a Database (Microsoft SQL Server only)

Click on the Select Database button.

Click on the New Source button

Select Microsoft SQL Server

Enter your server name and use the login you created at the beginning of this exercise.

Select the database where your pick list data is stored, and select the view you created at the beginning of this exercise. The InfoPath data connection that you will be sharing in the SharePoint library will have the query imbedded in it, so you can’t just have a connection to the database. You need to choose a specific table.

Save your connection file.

Re-enter you database password. Click Yes to save your password into the InfoPath connection file in plain text.

Click Next.

Click Next, Next, Finish.

Leave the Value as @Site_ID and change the Display name to @Site by clicking on the folder tree to the right and selecting Site.

Click OK.

PUBLISH YOUR INFOPATH DATA CONNECTION TO SHAREPOINT

From the Data menu, select Data Connections.

Select vwSitePicklist and click on the Convert to Connection File button.

Key in the location of your InfoPath Connection library you created earlier, and add a name for the data connection you are creating. Give it a suffix of .udcx. Click OK. It will take a minute or two to create the connection.

Click Close.

Navigate to your SharePoint library that contains the InfoPath data connections. You will see your new data connection here. Notice that the Approval Status is “Pending”. You need to change it to Approved before you can use the data connection.

Click on the dropdown beside your new data connection. Select Approve/Reject.

Select Approved. Click OK.

Now go back to InfoPath and publish your InfoPath form. Navigate to your SharePoint list and Add a new item. You can choose a Site from the Site pick list which is pulling directly from SQL Server.

Pretty cool, right? No need to push data into SharePoint and keep it synchronized, just pull directly from SQL Server. I love it!

Best Practices for Cascading SharePoint Lists using InfoPath Forms

This is the first in a series of posts on using InfoPath. You will find links to the others at the end of this post.

I find on many occasions that users have a need to capture some pieces of information but don’t want to build an application to capture it. SharePoint lists can be a useful way to capture this data, if you plan carefully and use best practices. At times users need to control several different pieces of related information, and would like to use information from one SharePoint list or SQL Server table as a pick list or drop down in another SharePoint list. This can be effective if you put the right controls in place to keep the data in synch and to maintain data integrity.

You could use a SharePoint Lookup column in your list to keep this data in synch, but doing so can have complications. I avoid Lookup columns as a rule, since they store the underlying values as a mash up of SharePoint ID and description, looking something like 12;#Description. When you pull this into your database you have to do some work to get the data into useable form. And if you want to refer to or display any other information from the lookup list into the cascaded list using InfoPath you can’t do it. What I prefer to do store is the ID of the item (sometimes the SharePoint ID and sometimes the underlying ID from the data warehouse), and use InfoPath to get the description of the item and any other relevant fields. I then use the ID for any joins on the data warehouse side of things, and I use SSIS to keep the descriptions updated across the cascading lists. So if Vendor Name got updated in a lookup list, I would join on the ID and use SSIS to update the description in any cascaded lists, since we are not using a SharePoint Lookup column to do this for us.

A combination of SharePoint column settings, InfoPath and SSIS is a great way to put the required controls in place. It takes a bit to get the hang of it, but it has a lot of flexibility to enforce data integrity and to keep the descriptions in synch between lists. The only drawback to using InfoPath forms is that users can no longer use the Datasheet View to enter and update data. With large lists this can be a problem for users, so you need to decide if Datasheet View is a requirement for your users before using InfoPath forms.

When I design a cascading list scenario, I like to build the underlying SQL tables and report first, get the structures exactly as needed, and then build the SharePoint lists based on the existing table structures. Without doing this, a lot of changes can be discovered along the way, and having to go back and change the SP List, SSIS Package, SQL tables and reports is not an efficient use of time. Best to build the tables and report, hammer out the correct structures and build the SharePoint lists and InfoPath forms on top of that.

For the step-by-step I will build 4 lists where users can enter data to control a department scorecard. These 4 lists will be used to populate these 5 tables which have already been defined:

DIM_Supply_Chain_Goal

DIM_Supply_Chain_Section

DIM_Supply_Chain_Metrics

FACT_Supply_Chain_Target

FACT_Supply_Chain_Actual

I won’t be showing you the SSIS packages to populate these tables, as that is another subject. I am simply demonstrating how to can use SharePoint lists to capture data with a good user experience. You can then use these lists to populate underlying tables of a different structure using SSIS. For more details on pulling the data out of SharePoint lists using SSIS please see my previous post – How to use a SharePoint list as a data source in your SSIS package.

Users will be able to update the values for these tables from SharePoint using InfoPath forms. SSIS will be used to import the data into SQL Server and populate these tables. The end result will be a department scorecard where users can control the values for actuals and targets, as well as the sort order of all of the goals, sections and metrics. Some of the actuals will be calculated by SSIS using data from an ERP system, and some will be user controlled. The targets are all user controlled. And the descriptions and sort order of Goals, Sections and Metrics are user controlled.

The lists are designed with the best user experience in mind, rather than building them to match the underlying tables. Some data transformation will be done by SSIS to extract the data from the lists and load it in the correct format into the tables.

SharePoint List creation, best practices:

Column Settings: The first step is to create the SharePoint lists which will feed the tables above. It is important to match the data types in the list to those in your underlying tables as closely as possible, but only for those columns that the user will be editing. For example if you are expecting the Goal field to have no more than 50 characters, then put that restriction on the column in your list.

For any calculated columns which are concatenations of fields, leave the default of 255 characters, since you may not know exactly which fields you will be concatenating yet.

For user Sort Order fields, make them Number type with 0 decimal places and choose a maximum value so you know how many leading zeros you will need to add to the ListSort column. More on that later.

When it comes to storing something like Year, you’ll want to make this a text field, since the display of Number will put a comma for thousands. So 2012 would look like 2,012. You could either have 2 fields for year in your list, one for display and one for the value to be sent to the data warehouse which makes sense in the case where a user could key in the number, or you could use a dropdown to control what the user can choose – store the value as a string, and then convert it on the way into the data warehouse.

Column naming convention: When naming your fields don’t put any spaces, The underlying name for that column captured behind the scenes will have special characters added in place of the spaces. When you get to SSIS this can be annoying. Avoid spaces when first naming the field, and then go back and rename the field with the space. The original value will be retained underneath the hood, but users will to see the space on the List View making the user experience better. for example, name the column Sort_Order when you first create it, and then go back and change it to Sort Order. You only need to rename those columns which will be visible in the List View, since this is strictly for user experience.

Built-in columns for display descriptions, sort order, item uniqueness and item title: Some additional best practices I have adopted for data integrity and for flexibility in changing logic without changing underlying structures is to add some calculated columns.

ItemDescription – The description stored in this column can be pulled into cascading lists to get the full description displaying in the list downstream. I have InfoPath populate it with whatever combination of descriptors make the most complete description of that list item to be displayed downstream.

UniqueItem – Be sure to check off Enforce Unique Values when creating the UniqueItem column. Since we are using InfoPath to control the user experience it makes sense to put all the logic to populate these fields in InfoPath, rather than creating calculated columns in our list. We will concatenate the appropriate combination of data to define a unique list item, and the Enforce Unique Values property will throw an error if a user tries to create a duplicate item.

ListSort – I also add a column called ListSort in which I can place the combination of values by which the list view should be sorted. SharePoint only allows you to sort a list view by up to two values. By concatenating the combination of values into one field it makes it very easy to sort your list. If you will also be including a numeric Sort_Order field which users can control, be sure to set a maximum value that they can enter. This will be important when you use this Sort_Order to populate your ListSort fields downstream in the cascading lists. For example if you are sorting your Section list by the Goal sort and then the Section name, you will need to concatenate these fields. SharePoint sorts numbers stored in a text field as text, so the numbers 1,50 and 100 would be sorted as 1, 100, 50. Since we are using the ListSort text field as our best practice for all lists, we will want to add some leading zeros to the Sort_Order when populating the ListSort. The maximum value will tell you how many leading zeros to add. If your maximum value is 999, you will need to add up to 2 leading zeros. As a best practice make the maximum value for your Sort_Order 999,999 and concatenate 6 leading zeros. This will become clear in our example.

Title – I also prefer to leave the Title column as a calculated field, to be a combination of one or more of the values a user will enter into the list. This gives me complete flexibility to have the Title reflect a good description of the list item without restricting that description to only one field in the list which a user may key in. Keep this at default size of 255 since it may be a concatenation of fields.

You end up with 4 additional calculated columns in your list, and although in some cases they may all contain the same information, you have built in the flexibility to change display descriptions, sort order, item uniqueness and item Title without have to change the structure of your list or the SSIS packages. You can change the logic in the InfoPath form without having to touch the underlying structures.

Create the SharePoint Lists:

List 1. Supply Chain Scorecard Goal

Change the Title field to Goal, and add a column called Sort_Order of type Number with 0 decimal places. To enforce the data integrity, require this field to have information and Enforce unique values.

After you save the Sort_Order column, go back and rename it to Sort Order (with a space). The end result is a list with 4 columns, Item (title), Goal, Sort Order, and UniqueItem. While this particular list may not need the addition of Item and UniqueItem, since Item, Goal and UniqueItem may all end up being the same value – it is good design to include all three columns to give you the flexibility to change it later.

List 2. Supply Chain Scorecard Section

List 3. Supply Chain Scorecard Metrics

List 4. Supply Chain Scorecard Actuals

Modify the List View:

Go back and remove any columns from the List View which you do not want the users to see – including UniqueItem and ListSort, reorder the columns as required, and set the view to sort by ListSort.

Do this by selecting your list, clicking on the List tab in the SharePoint menu and selecting Modify View.

Deselect any items you don’t want to show up in the view. Reorder by changing the Position from Left dropdown.

In the “First Sort by the column:” drop down select the ListSort column.

Do this for all of the lists.

Edit the First InfoPath Form:

Start with the first pick list in the cascade of lists. The one that does not depend on any of the other lists. It would be the highest level in the hierarchy of the cascade, in this case Supply Chain Scorecard Goal. This list does not choose a value from any other list, but the Supply Chain Scorecard Section list will use the Goal list as a pick list.

InfoPath Form 1. Supply Chain Scorecard Goal

Although this list does not depend on any of the others, we would like to use InfoPath to allow users to edit the two editable fields – Goal and Sort Order, and have InfoPath populate the underlying calculated fields of Title, ItemDescription, UniqueItem and ListSort. In this case the list is a very simple one, so each of these 4 calculated fields will contain just one value – Goal. The list will be sorted by Goal, the uniqueness of the list will allow only one row for each Goal description, the title will display the Goal and the description passed on to the cascading lists will be Goal.

Open your list. In the SharePoint menu go to the List tab and select Customize Form.

A dialog box will ask if you would like to open the form in Microsoft InfoPath. Click OK. InfoPath will open, and the default form for your list will appear with all of the underlying fields on it.

For the calculated fields which the user does not need to see, highlight the row

and click delete.

Now only the two fields remain, which the user will populate.

UNIQUEITEM

To populate the calculated fields look at the Fields pane on the right hand side of the screen. Right click on the UniqueItem field and select Field Properties.

Beside the Default Value click on the expression editor button

Click on the Insert Field or Group button

Select the Goal field and click OK.

Your formula now looks like this:

Click OK. Make sure the checkbox is checked which says “Refresh value when formula is recalculated”. This will ensure that each time the Goal description is changed, the UniqueItem value will also be updated to the new Goal description.

Click OK. For now, repeat this for each of ItemDescription, Title and ListSort. Be sure that you populate all four calculated fields correctly before going to the next step, since we have required that these fields be populated. If they are blank you will not be able to add any items to your list.

Click on the InfoPath File menu and click the Quick Publish button. This will publish your changes back to SharePoint.

Now you can add some items to your list. You will need to do this to have some data for the next step, which is to edit the InfoPath form for the cascading SharePoint list. You will want to test that it is working, so you need to have at least one item in the Goal list. Add some items – add Goal = Improve, Sort Order = 1. Now add Goal = Improve, Sort Order = 2. Notice that you get an error message stating that there is a duplicate value for UniqueItem. We selected Enforce unique values on the UniqueItem column to ensure users do not accidentally enter duplicate items. UniqueItem lets you control what constitutes a unique value.

Click OK and change the Goal = Maintain. Save the item. Add one more item – Goal = Track, Sort Order = 3. Your list is now complete.

TITLE

Take a look at the List View. Notice how the Title is aligned to the left and Sort Order is to the extreme right. This doesn’t seem like the best user experience.

Let’s change the logic in our InfoPath form to make the Title calculation a concatenation of Sort Order and Goal. Then we can remove the Sort Order field from our List View.

Go back to the InfoPath form you edited. It should still be open. In the Fields pane on the right hand side of the screen right click on the Title field. Select Field Properties. Click on the expression editor button for the Default Value. Delete the current formula which says Goal. Click on the Insert Function Button.

Select the Text Category and then select the concat Function.

Click OK. You can double click where it tells you, but we are going to delete everything after concat( and then click on the Insert Field or Group button. Select the Sort Order field. Type a comma. Type “ – “. Type a comma. Click the Insert Field or Group button. Select the Goal field. Type a close bracket. You should end up with this formula.

Click OK.

Publish the Form. File –> Info –> Quick Publish.

Go back to your SharePoint list. The changes to the title will not take effect until the Goals are edited. Open the Edit window for each of the three items in your list and save the items. The Title has changed to the new value.

Modify the view and remove the Sort Order field from the View, since it is now part of the Title.

ITEMDESCRIPTION

In your downstream cascading lists you will want a good description of your item. It may be the same as Title, but it may not. By having this field in place as a best practice, you can control how it will look.

For now we will leave the ItemDescription = Goal.

LISTSORT

Since we want the List to sort by the numeric value of Sort Order, and want to keep with our best practices of using ListSort as our standard sorting mechanism which will also be used downstream, we need to edit the calculation for ListSort. If we leave the view to sort by ListSort as it is now, it will sort alphabetically by Goal.

Open your InfoPath form if it is not already open. Right click on the ListSort field and select Field Properties. Click on the expression editor button for the Default Value. Click on the Insert Field or Group button and choose Sort Order. Click OK. Your formula should look like this.

Click OK, OK and publish the form. Go to your list and for each item in turn select Edit Item, and then Save. The ListSort values will not get updated with the new value until the items are edited, since the value is only refreshed when the formula gets recalculated. Add a new item to your list, Goal = Test Sort Order = 11. Since ListSort is a text field the list is sorting alphabetically by the Sort Order value we put in the ListSort field. This does not give the desired results since numbers stored in a text field get sorted like this:

At this point you might decide to just change the sort order on your list to use the Sort Order field, but when you want to use this downstream in your cascading lists you are going to want to be able to sort by a concatenation of Goal sort order and other sort orders which may not be numeric. For example you might want to sort the Section list by the Goal sort order and then the Section description. We can do this by adding some leading zeros to the Sort Order value when storing it in the ListSort.

Go back to your InfoPath form. Right click on the ListSort field and select Field Properties. Click on the expression editor button for the Default Value. Create the following formula using the Insert Function button and the Insert Field or Group button. You can type in the functions, but must select the Sort Order field using the Insert Field or Group button.

substring(concat("000000", Sort Order), string-length(string(Sort Order)) + 1, 6)

Notice that we are adding 6 leading zeros to the Sort Order value and then choosing the last 6 characters of the resulting value. Your formula should look like this.

Notice that we didn’t have to change the data type or structure to implement this change to the value in ListSort, but simply changed the logic. Now go back and Edit and Save each of the items in the list. Notice that they sort correctly now.

Use the First SharePoint list as a Pick List in the Second Cascaded List:

Close InfoPath if it is still open, and navigate to your second list – Supply Chain Scorecard Section. This list will use the Goal SharePoint list as a pick list.

Open the list, navigate to the List tab and click on the Customize Form button on the SharePoint menu. Click OK on the message. Highlight and delete the rows in the form for Title, Goal, ItemDescription, UniqueItem and ListSort. Edit the label on the form for Goal ID to Goal, and Display Toggle to Display. You should end up with a form that looks like this:

LOOKUP AND STORE ID FROM ANOTHER LIST

In the text box for Goal ID on the form, right click and select Change Control –> Drop-Down List Box.

Right click on it again and select Drop-Down List Box Properties.

Click on the radio button Get choices from an external data source. Click on the Add button beside the Data Source.

Leave the default values of Create a new connection to Receive data.

Click Next. Select SharePoint library or list.

Click Next. Using Internet Explorer navigate to your Goal list. Copy the URL and scrape out anything to the right of and including Lists. This is the SharePoint site where the list is stored. Enter this value in the Data Connection box.

Click Next. Select the Supply Chain Scorecard Goal list.

Click Next. Select all of the fields you created, and sort by ListSort. You will be using more than one field from this list, and it is easiest if you have already included the fields in your data connection.

Click Next, Next and Finish. In the Value field of the Drop-Down List Box Properties window click on the Tree button. Select ID. You will be storing the SharePoint ID of the Goal in the Goal_ID field of your Section list. Change the Display name to ItemDescription.

Click OK. This will store the ID, but display the ItemDescription to the user in the InfoPath form.

LOOK UP DESCRIPTIONS FROM THAT LIST

We would also like to store the ItemDescription so we can display it in our list, and for that we need a calculated field to go and fetch the description matching the ID now stored in the Goal_ID field.

In the Fields pane right click on the Goal field and select Field Properties. Click on the expression editor beside the Default Value. Click on the Insert Field or Group button. Click on the Show advanced view link.

In the Fields drop down change the data connection to the Supply Chain Scorecard Goal connection you created when populating the Goal ID.

Open up the dataFields toggle and the SharePointListItem_RW toggle. Select ItemDescription. This is where the best practices come in handy, since we already know that this field was created specifically to display a useful description in cascaded lists. Now, DON’T CLICK OK. This is something you have to get used to. You need to filter the data first to get the corresponding Goal description which goes with the Goal ID already stored in your list. If you make a mistake and click okay now, you will have to go back and navigate the data connections again. It doesn’t remember where you were in the tree. Click on the Filter Data button.

Click on the Add button to add a filter. In the first drop down select the ID column. You are building an expression that will find the ItemDescription that corresponds to the ID stored in your main data connection. Leave the middle drop down as ‘is equal to’. Select the third drop down. Click on ‘Select a field or group’.

In the Fields drop down navigate to the Main data connection.

Open up the dataFields toggle and the SharePointListItem_RW toggle. Select the Goal ID field.

Click OK. Your filter condition looks like this:

Click OK. Your Filter Data looks like this:

Click OK and OK again. Your formula looks like this.

Please note that you couldn’t just type this expression into the box because the context of the data connection is stored within the filter. If you are interested you can select the Edit Xpath checkbox to can see the underlying code.

Click OK. The field properties looks like this.

Be sure that the Refresh value checkbox is selected. Click OK.

POPULATE THE CALCULATED FIELDS

Just as we did for the Goal SharePoint list, we also need to populate the calculated fields in this list.

A UniqueItem for this list will be a combination of Goal ID and Section name, since we could potentially have a Section of the same name in more than one Goal.

Title will be just Section

ItemDescription, to be used downstream, will be Goal plus Section

ListSort needs to incorporate the ListSort of Goal as well as the ListSort for Section. If a user were to change the sort order of the goals, we would want the Section list to be reordered accordingly. So we need to go and get the ListSort for Goal, and then concatenate it with our formula ListSort.

Right click on the ListSort field and paste our formula into the Formula box.

substring(concat("000000", Sort Order), string-length(string(Sort Order)) + 1, 6)

Delete the first reference to Sort Order and replace it by clicking on the Insert Field or Group and selecting Sort Order. This will put the XPath context of Sort Order into the formula.

Do the same with the second instance of Sort Order. Your formula should look like this.

We want to concatenate the ListSort value from our Goal in front of this formula. Type “concat( , “ at the beginning of the Formula window and add a “)” bracket at the end of the formula. Put the cursor after the “(“ . You are about to insert the ListSort value for Goal into the formula so the cursor must be in the right position. Your formula looks like this – cursor is highlighted in red.

Click on Insert Field or Group. We are going to follow the same steps we used to get the Goal description. In the Select a Field or Group window select Show advanced view.

In the Fields drop down change the data connection to the Supply Chain Scorecard Goal connection you created earlier.

Open up the dataFields toggle and the SharePointListItem_RW toggle. Select ListSort. This is another spot where the best practices come in handy, since we already know that this field was created specifically to sort the goal list. Now remember, DON’T CLICK OK. You need to filter the data first to get the corresponding Goal description which goes with the Goal ID already stored in your list. If you make a mistake and click okay now, you will have to go back and navigate the data connections again. It doesn’t remember where you were in the tree. Click on the Filter Data button.

Click on the Add button to add a filter. In the first drop down select the ID column. You are building an expression that will find the ListSort that corresponds to the Goal ID stored in your main data connection. Leave the middle drop down as ‘is equal to’. Select the third drop down. Click on ‘Select a field or group’.

In the Fields drop down navigate to the Main data connection.

Open up the dataFields toggle and the SharePointListItem_RW toggle. Select the Goal ID field.

Click OK. Your filter condition looks like this:

Click OK. Your Filter Data looks like this:

Click OK and OK again. Your formula looks like this.

Click OK and OK again. Publish your form.

Go to your Section list and add a few items. Notice that even if you put a lower Sort Order with a Maintain goal it gets sorted after the Improve goal.

Use the Second SharePoint list as a Pick List in the Third Cascaded List:

I’m not going to do as much detail on the screenshots for this one, since a lot of it is rinse and repeat. I’ll tell you the steps.

Navigate to the third SharePoint list – Supply Chain Scorecard Metrics.
Open the InfoPath form by clicking on the Customize Form button in the List menu.
Remove all the rows in the form except for Metric, Section ID, Site, UOM and Sort Order.
Set up the Section ID lookup:
1. Edit the Section ID label to say “Section” and right click on the textbox and Change the Control to a Drop-Down List Box.
2. Right click again and select Drop-Down List Box Properties. Select Get choices from an external data source
3. Click the Add button beside the Data source.
4. Create a new connection to Receive data.
5. From a SharePoint library or list
6. Enter your SharePoint site URL
7. Select the Supply Chain Scorecard Section list.
8. Select all of the fields that you created. Sort by ListSort.
9. Next and Finish.
10. Change the Value field to ID and the Display name to ItemDescription. Click OK.
Edit the Section field properties.
1. Right click Section, Field Properties.
2. Click on the expression builder button beside Value.
3. Click on Insert Field or Group. Click on Show Advanced View.
4. Select Supply Chain Scorecard Section. Navigate to dataFields and then to ItemDescription.
5. Click on Filter Data.
6. Click on Add.
7. Select ID in the first drop down, leave “is equal to” in the second drop down.
8. In the third drop down choose Select a field or group. Change the data connection in the drop down to Main. Navigate to Section ID. Click all of the OKs.
Edit the Title field properties. Concatenate the Metric and Site field.

concat(Metric, " ",Site)

Edit the ItemDescription field properties. Concatenate Section and Metric and Site.
- concat(Section, " – ", Metric, " ", Site)
Edit the UniqueItem field properties. Concatenate the Metric and Site field.

concat(Metric, " ",Site)

Edit the ListSort field properties. It will end up looking like this: concat( ListSort[ID = Section_ID] ,substring(concat("000000", Sort Order), string-length(string(Sort Order)) + 1, 6))
1. Enter the formula for the List Sort and set up to concatenate the Section List Sort field to the beginning of it.
  - concat( <insert Section List Sort here> ,substring(concat("000000", Sort Order), string-length(string(Sort Order)) + 1, 6))
2. Show advanced view. Select the Supply Chain Scorecard Section data connection.
3. Navigate to dataFields and select ListSort.
4. Select the Filter Data button. Click Add.
5. Select ID in the first drop down, leave “is equal to” in the second drop down.
6. In the third drop down choose Select a field or group. Change the data connection in the drop down to Main. Navigate to Section ID. Click all of the OKs.
Publish the InfoPath form.

Navigate to your list and add some items. All should be working as expected.

Use the Third SharePoint list as a Pick List in the Fourth Cascaded List.

This is almost a repeat of the last section. The difference is that the user will not be choosing a sort order. The Supply Chain Scorecard Actuals list will contain the Metrics for each year. This means the ListSort will be based on Year and Metric ListSort. UniqueItem will be based on Year and Metric ID. ItemDescription won’t be important because there will be no other lists downstream from this one. I’ll write out the steps just as a refresher.

Navigate to the fourth SharePoint list – Supply Chain Scorecard Actuals.
Open the InfoPath form by clicking on the Customize Form button in the List menu.
Remove the following rows from the form: Title, Metric, UOM, ItemDescription, UniqueItem, ListSort.
Set up the Metric ID lookup:
1. Edit the Metric ID label to say “Section” and right click on the textbox and Change the Control to a Drop-Down List Box.
2. Right click again and select Drop-Down List Box Properties. Select Get choices from an external data source
3. Click the Add button beside the Data source.
4. Create a new connection to Receive data.
5. From a SharePoint library or list
6. Enter your SharePoint site URL
7. Select the Supply Chain Scorecard Metrics list.
8. Select all of the fields that you created. Sort by ListSort.
9. Next and Finish.
10. Change the Value field to ID and the Display name to ItemDescription. Click OK.
Edit the Metric field properties.
1. Right click Metric, Field Properties.
2. Click on the expression builder button beside Value.
3. Click on Insert Field or Group. Click on Show Advanced View.
4. Select Supply Chain Scorecard Metrics. Navigate to dataFields and then to ItemDescription.
5. Click on Filter Data.
6. Click on Add.
7. Select ID in the first drop down, leave “is equal to” in the second drop down.
8. In the third drop down choose Select a field or group. Change the data connection in the drop down to Main. Navigate to dataFields and then to Metric ID. Click all of the OKs.
Edit the Title field properties. Set it to equal Metric.
Edit the ItemDescription field properties. Set it to equal Metric.
- concat(Section, " – " , Metric)
Edit the UniqueItem field properties. Set it equal to the Year + Metric ID field.
Edit the ListSort field properties. It will end up looking like this: concat(Year, ListSort[ID = Metric_ID])
1. Enter the formula “concat (“, click on the Insert Field or Group button and select the Year field. Key in a comma some space and a “)”. Place the cursor right before the close bracket. You will insert the Metrics ListSort field here.
  - concat( Year, <insert Metric ListSort here> )
2. Show advanced view. Select the Supply Chain Scorecard Metrics data connection.
3. Navigate to dataFields and select ListSort.
4. Select the Filter Data button. Click Add.
5. Select ID in the first drop down, leave “is equal to” in the second drop down.
6. In the third drop down choose Select a field or group. Change the data connection in the drop down to Main. Navigate to Metric ID. Click all of the OKs.
Publish the InfoPath form.

Navigate to the list. Add an item.

Display a Look up field on the Form:

You notice that it would enhance the user experience to be able to see the UOM beside the Target, Thresholds and Monthly Actuals on the form when editing or adding an item. We will look up the UOM and keep it on the form as a ‘display only’ so when the user is entering the targets and actuals they will be aware of the UOM for the Metric.

Highlight the entire right hand column, right click and select Insert –> Columns to the Right.

Drag the left edge of the new column to make it smaller. Drag the UOM field from your Fields list onto the form, in the cell beside Target. Delete the Label that says UOM beside the textbox you dragged onto the form.

Right click on the textbox and select Text Box Properties. On the Data tab click on the expression editor button.

Click on the Insert Field or Group button. Click on Show advanced view, and in the dropdown at the top select the Supply Chain Scorecard Metrics data connection.

Navigate to dataFields and then to UOM. Click on Filter Data. Click on Add.

Select ID in the first drop down, leave “is equal to” in the second drop down. In the third drop down choose Select a field or group.

Change the data connection in the drop down to Main. Navigate to Metric ID.

Click all of the OKs until you get back to the Text Box Properties window. Click on the Display tab. Check off the Read-only box. Click OK.

Right click on the UOM text box you created and select Borders and Shading. Change Borders to None. Change the shading to No Color. This will make it look like part of the form, instead of as a text box which might be editable.

Right click on the UOM text box you created, and select Copy. Paste it into each of the cells where you would like to display the UOM on the form.

Publish the InfoPath form. Navigate to the list and add an item. Notice that as soon as you select a Metric the UOM appears beside all the textboxes to be populated with that metric by the user.

There are lots of other things you can do with InfoPath, but I think this is more than enough to get you started.

I have also blogged a few other related subjects while writing this post, since this one already covered so much. These two are a continuation of the example started here.

Use a SQL Server data source for an InfoPath pick list

Dynamically filter a drop down on your SharePoint list by another user selected value

You might also find this interesting:

How to use a SharePoint list as a Data Source in your SSIS Package

Maintaining Data Integrity of a SharePoint list with SSIS

Maintaining Data Integrity of a SharePoint List with SSIS

I like using InfoPath to maintain data integrity as it has a much finer control than SSIS. I will blog about that later. But it can be very useful to stick with SSIS to maintain the data integrity on a simple list which synchronizes data between a database and a SharePoint list. For example if users do not have an available interface to define groups for their customers, they can use a SharePoint list to house the customers and define the groups.

In this example the customers are coming from an ERP system and so the account names and ids must be correctly maintained in the SharePoint list. It’s the typical three tasks required for maintenance: insert, update and delete. Any new customers should automatically be added to the list, any descriptions which have changed should be updated, and any customers who have been incorrectly added to the list should be deleted. I find the simplest way to manage this is to have a separate table in the data warehouse strictly for the data coming from the SharePoint list. This way you can match up to your ERP tables to see what data management is needed.

Create your SharePoint list

Have the field sizes match the ERP field sizes, to ensure you don’t run into any size conflicts. Best practices for creating a SharePoint list is to name your list and your field names without any spaces in them. Then go back and change them to add any spaces that you might want for readability. SharePoint doesn’t like spaces and will add strange characters internally to fill the gaps. When you go to do your SSIS package it makes things less readable. So, in this example a column in the list will be called “Account Status”. So I first name it “AccountStatus”, and then go back and change it to “Account Status”. Internally it will still be called “AccountStatus” without the space.

Here is my list for this example. It has 4 columns, Debtor Name, Debtor ID, Account Status and Debtor Group. All of these columns except Debtor Group will be maintained by the SSIS package. Users will key in the Debtor Group.

Populate the data warehouse with the most current data from business system

This example assumes a data warehouse situation. Create a package in your SSIS project. Keep in mind where in your project you need to run this package. It should come after the Debtor table has been updated from your ERP system. It’s best to put the two related items in the same package, to ensure that the order of operations does not get lost in a master package somewhere. So first step is to add the SSIS tasks which populate your Debtor table. Put them in a sequence container.

Insert, Update and Delete data in SharePoint list

There are series of 8 SSIS tasks which will “synchronize”, at a particular point in time, your SharePoint list with the data warehouse. To do this create a Sequence Container called “Update Debtor Group SharePoint list, and add and connect these 8 consecutive tasks.

1. Delete from SP_Debtor_Group: Add an Execute SQL taks to your container called “Delete from SP_Debtor_Group”. I always prefix any working SharePoint tables with SP for ease of identification. We haven’t created this table yet, but it will be automatically created in the next step. For now just add the task which will execute the sql statement “Delete from SP_Debtor_Group”

2. Populate SP_Debtor_Group: Add a Data Flow task to your container called “Populate SP_Debtor_Group”. This will populate your new table with whatever data currently exists in the list in SharePoint. Right now there is probably no data in the list, but let’s create the step. Add a SharePoint List Source to the Data Flow work surface, Edit the Properties of the source for SiteUrl and SiteListName to connect to your Debtor Group SharePoint list. (For instructions on how to do this, view my blog post here.) Add an OLE DB Destination and connect to your data warehouse, set Data Access mode to “Table or view – fast load”, and click on the NEW button beside the “Name of the table or the view” to create the new table. The initial statement will look like this, but edit it to give the table the chosen name, and move or delete fields as required.

Keep in mind that you need to keep the ID field in order to do any updates in SharePoint, and the Title field is whatever is the field which shows up as a hyperlink in your SharePoint list, in this case Debtor Name. I prefer to keep the field types as whatever SharePoint has allowed, since when we are sending data back up to SharePoint it’s easiest to have it already in the correct data type. If I need the data type to be something different in the data warehouse I will do the conversion before populating the data warehouse table. Your Create Table script should look like this.

Click on the Mappings tab and map the Input Columns from the SharePoint list to the Destination Columns in your new table.

3. Delete duplicate records from SharePoint: A business decision was made to delete the newest record from SharePoint in cases where duplicates exist. The only reason duplicates might exist is if a user added it, and this SharePoint List is only supposed to be updated by the SSIS packages. Add a Data Flow Task to your container, called “Delete duplicates from SharePoint”. Add an OLE DB Source to your Data Flow work surface.

SELECT MAX(ID) as ID FROM SP_Debtor_Group
WHERE DebtorID IN
(
SELECT DebtorID
FROM SP_Debtor_Group
GROUP BY DebtorID
HAVING COUNT(*)>1)
GROUP BY DebtorID

We only need the ID in order to identify which records to delete. Write the appropriate query to identify those records which are duplicates. Add the query to your OLE DB Source. Place a SharePoint List Destination on your work surface. Edit the Properties of the destination for SiteUrl and SiteListName. Be sure to change the BatchType to “Deletion”. Click the Refresh button. Go to the Column Mappings tab and ensure that the ID is mapped on the Input Column and the Destination Column. Click OK.

4. Delete invalid records from SharePoint: Add a Data Flow Task to your container, called “Delete from SharePoint”. Add an OLE DB Source to your Data Flow work surface.

SELECT SP.ID
FROM SP_Debtor_Group as SP LEFT OUTER JOIN
DIM_Debtor as DB ON SP.DebtorID = DB.Debtor_ID
WHERE DB.Debtor_ID IS NULL

We only need the ID in order to identify which records to delete. Write the appropriate query to identify those records. Add the query to your OLE DB Source. Place a SharePoint List Destination on your work surface. Edit the Properties of the destination for SiteUrl and SiteListName. Be sure to change the BatchType to “Deletion”. Click the Refresh button. Go to the Column Mappings tab and ensure that the ID is mapped on the Input Column and the Destination Column. Click OK.

5. Update descriptions in SharePoint: Add a Data Flow task to your container. Call it “Update SharePoint descriptions”. Add an OLE DB Source to your Data Flow surface. Write the SQL statement that you would like to use to ensure that any descriptions are update appropriately. Remember that DebtorGroup is owned by the users, so we don’t want to overwrite anything in SharePoint. We won’t update that field. And we need the SharePoint ID field in our query in order to identify which SharePoint record to update. The fields we would like to update are Debtor and Account Status, to ensure that any changes are uploaded to SharePoint. Here is the query.

SELECT SP.ID,
DB.Account_Name as Debtor,
DB.Account_Status as AccountStatus
FROM SP_Debtor_Group as SP INNER JOIN
DIM_Debtor as DB ON SP.DebtorID = DB.Debtor_ID

Drag a SharePoint List Destination onto your Data Flow surface. Connect the OLE DB Source to the destination. Edit the destination with the appropriate SiteUrl and SiteListName and be sure the BatchType is Modification, since we will be modifying the SharePoint records. Click the Refresh button. Then select the Column Mappings tab. Map the Input and Destination columns. Remember that Title is whichever field has a hyperlink in your list (Debtor) and that you have to include the ID column in order to identify which record to update.

6. Insert new records into SharePoint: Add a Data Flow Task to your container, called “Add records to SharePoint”. Add an OLE DB Source to your Data Flow work surface.

SELECT
DB.Debtor_ID as DebtorID,
DB.Account_Name as Debtor,
DB.Account_Status as AccountStatus,
DB.Account_Name as DebtorGroup

FROM SP_Debtor_Group as SP RIGHT OUTER JOIN
DIM_Debtor as DB ON SP.DebtorID = DB.Debtor_ID
WHERE SP.DebtorID IS NULL

Write a query to populate new records into SharePoint. In this case the Debtor Group should default to the Debtor Name until the users can update it. Add the query to your OLE DB Source. Place a SharePoint List Destination on your work surface. Edit the Properties of the destination for SiteUrl and SiteListName. Be sure that the BatchType is “Modification”. Click the Refresh button. Go to the Column Mappings tab and ensure that the fields are correctly mapped on the Input Column and the Destination Column, especially Title. Click OK.

7. Delete from SP_Debtor_Group again: Add another Execute SQL taks to your container called “Delete from SP_Debtor_Group again”, to refresh the data in your data warehouse table with the newly cleansed data from SharePoint. Add the task which will execute the sql statement “Delete from SP_Debtor_Group”

8. Populate SP_Debtor_Group again: Add a Data Flow task to your container called “Populate SP_Debtor_Group”. This will populate your table with the cleansed data from SharePoint. Add a SharePoint List Source to the Data Flow work surface, Edit the Properties of the source for SiteUrl and SiteListName to connect to your Debtor Group SharePoint list.Click Refresh. Add an OLE DB Destination to the Data Flow surface. Select the SP_Debtor_Group table as the destination.

Click on the Mappings tab and map the Input Columns from the SharePoint list to the Destination Columns in your new table.

These 8 steps will periodically clean the data in your SharePoint list. It will depend how often you run the package, how clean the data will stay. And of course how careful the users are. It may seem cumbersome, but these steps can save a lot of headaches on bad data when using the end result in reporting.

Creating an SSIS Package Template with Predefined Package Configurations

Best practices suggest creating a package template with the Package Configurations already predefined for your project, and then copying and the template package as a starting point for each additional package in your solution. This way you won’t have to add the configurations to each package, but only do it once for your solution and copy the configurations by using a copy of the template for each new package in your solution. Here is one way to to this.

1. ADD AN ENVIRONMENT VARIABLE TO THE SERVER TO POINT TO THE CONFIGURATION DATABASE

On the server that will be running the SSIS packages, add an Environment Variable called “SSIS_CONFIG_DB” to hold the value of connection string for the Configuration database that will store all the other connection strings.

All Programs – Computer – right click on Computer and select Properties

Select Advanced System settings

On the Advanced tab, click on the Environment Variables button

Add a new System Variable by clicking the New button and filling the Variable name SSIS_CONFIG_DB and the variable value in the format of:

Provider=SQLNCLI10.1;Data Source=servername;Integrated Security=SSPI;Initial Catalog=databasename

2. (CLOSE AND) OPEN BIDS

Open BIDS solution AFTER the variable is created, so that your variable will show up in the dropdown.

3. REMOVE ANY SHARED DATA SOURCES IN YOUR PROJECT SOLUTION

Open or create a new solution. If you are using Shared Data Sources, delete them from the Data Sources folder in the Solution Explorer now. Your packages will retain their individual connection managers. Shared Data Sources and Package Configurations don’t mix very well, and here’s a good write up on why.

http://msdn.microsoft.com/en-us/library/cc671619.aspx

4. CREATE A NEW PACKAGE

Add a new package to your solution called Package_Template.

5. ADD A CONNECTION TO YOUR PACKAGE FOR THE CONFIGURATION DATABASE

Add a Connection to your package which connects to the database you have chosen to hold all your configuration strings in Step 1. To do this, right click in the Connection Managers pane in your Package_Template package and set up the appropriate connection.

6. ADD THE ENVIRONMENT VARIABLE CONFIGURATION TO YOUR PACKAGE

This Configuration needs to be first in the list of Configurations defined in your package, since the other Configurations will be referencing it.

Right click on Control Flow, select Package Configuration. Check the Enable package configurations checkbox. Then click the Add button, select Configuration Type “Environment Variable” and select your SSIS_CONFIG environment variable from the dropdown.

Click Next. Select the Target Property by navigating to the Connection Manager for the Configuration Database which you set up in step 5, and selecting the ConnectionString property.

Click Next and give your Configuration a name “ConfigConn”

7. ADD A SQL SERVER CONFIGURATION TO YOUR PACKAGE

Add another configuration of type “SQL Server” by clicking the Add button. Select SQL Server from the Configuration Type drop down. Select the connection to your DW (the one you are pointing to in your environment variable) and if this is the first SQL configuration you are adding to this database, click New to create the table in the DW where the connection strings will be stored. Or, if this is not the first time, then select the name of your configuration table from the drop down [dbo].[SSIS Configurations]”

If this is the first time you are creating an entry for this connection, create a Configuration filter by typing in an appropriate name for your connection string, which will identify that connection in the table. Otherwise select the name of your existing entry from the dropdown.

If this is the first time you are creating a Configuration to this database, you will need to navigate to the appropriate Connection Manager and select the Connection String property.

If this is not the first time you have used this particular Configuration filter a message box will ask you if you want to reuse the existing configuration or overwrite. Select Reuse Existing, so the information in the database about this connection will not be overwritten.

Give your connection Configuration a name. Example “NAVConn”

You can go ahead and add the rest of your package connections as SQL Server configurations. Take a look at the table “SSIS Configurations” in the Configuration database you defined, to see the connection strings being entered. When you update the connection string in this table, your packages will point to the updated database. Try it by updating the value and reopening your package.

If you want to add logging to your package template at this point you can look at this post – Configure SSIS Logs

Thanks to these two sites for giving me some tips on this subject:

http://www.rafael-salas.com/2007/01/ssis-package-configurations-using-sql.html

http://www.mssqltips.com/tip.asp?tip=1405

SSIS 2012 – Package Configurations Menu Option Missing

I’m going to blog this in case anyone else has run into the same issue. I recently tried to create SSIS Package Configurations using SQL Server Data Tools (SSDT) in a SQL Server 2012 environment in Cloudshare.

As always, I right clicked on the Control Flow designer for my package expecting to see the Package Configurations option on the menu. The option was not there.

So I searched msdn I found the instructions for 2012 tell me to select Package Configurations from the SSIS menu. http://msdn.microsoft.com/en-us/library/ms141132(v=sql.110).aspx I did that, but it’s not there either.

I haven’t been able to find a solution to getting this option back into my menu, but I did find a workaround. In my Package Properties window there is an option for Configurations.

When I click on the ellipsis I get the Package Configurations Organizer window I am looking for.

Just click on the Enable package configurations checkbox and away you go as normal.

I will be posting a blog shortly on some best practices for defining and reusing Package Configurations across your solution using a Package Template.

As an aside, I did also come across this interesting blog post on Parameters in Denali. As he states, they don’t replace Package Configurations, but it is another way to go.

http://sqlblog.com/blogs/jamie_thomson/archive/2010/11/11/parameters-in-ssis-in-denali.aspx

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

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.

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…

…to the Data Flow Sources section.

9. Drag SharePoint List Destination from the General section…

…to the Data Flow Destinations section.

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.

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

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,

then type “Departments”.

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:

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

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

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

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).

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.