Aug 282012
 

As a continuation of Creating an SSIS Package Template with Predefined Package Configurations I wanted to share with you another good habit for your package template.  I like to add logging to my packages for the purposes of troubleshooting, especially when there are complex processes with multiple packages running at once. It helps me track how long packages take to run and gives me the ability to see if there are changes over time, as well as the obvious error logging.

It is very simple to turn on the logging.  Simply right click on your Control Flow canvas and select Logging.

image

This is another place where having a sequence containers within your package are a good idea.  I generally have the entire package within one master sequence container, and then divide up the tasks within other sequence containers as necessary.  You can set the logging for the main Sequence Container within your package and you will notice that the other items within that container will inherit the logging configuration.  You can deselect certain tasks or containers if required.

image

I like to log errors in SQL Server.  To do this, change the Provider Type to SSIS log provider for SQL Server.

image

Click the Add button.  In the Configuration column use the dropdown to select the connection to the SQL database where you would like to store the logs.

image

Check the box beside the Package_Template container and then check the box beside the log you have just created.

image

Select the Details tab.  Specify which events you would like to capture.

image

Keep in mind that each event will take up space in your database.  Depending on how many packages you are logging and how often, you may with to only track important events, or you may wish to delete some or all of the events on a scheduled basis. 

Here are a few views I like to create for use when checking the logs.

CURRENT ERRORS

CREATE VIEW [dbo].[ssislog_ErrorsCurrent]
AS
SELECT     TOP (100) PERCENT PKG.PackageName, PKG.starttime AS PackageStartTime, LG.source AS TaskName, LG.starttime AS StartTime, LG.endtime AS EndTime,
                      LG.message
FROM         dbo.sysssislog AS LG INNER JOIN
                          (SELECT     LG1.executionid, LG1.source AS PackageName, LG1.starttime
                            FROM          dbo.sysssislog AS LG1 INNER JOIN
                                                       (SELECT     source, MAX(starttime) AS starttime
                                                         FROM          dbo.sysssislog
                                                         WHERE      (event = ‘PackageStart’)
                                                         GROUP BY source
                                                         HAVING      (MAX(starttime) > DATEADD(dd, – 1, GETDATE()))) AS CUR ON CUR.source = LG1.source AND CUR.starttime = LG1.starttime
                            WHERE      (LG1.event = ‘PackageStart’)) AS PKG ON LG.executionid = PKG.executionid
WHERE     (LG.event IN (‘OnError’))
ORDER BY PackageStartTime DESC, StartTime

 

CURRENT LOG ENTRIES

CREATE VIEW [dbo].[ssislog_LogEntriesCurrent]
AS
SELECT     TOP (100) PERCENT PKG.PackageName, PKG.starttime AS PackageStartTime, LG.source AS TaskName, LG.starttime, LG.endtime, LG.message
FROM         dbo.sysssislog AS LG INNER JOIN
                          (SELECT     LG1.executionid, LG1.source AS PackageName, LG1.starttime
                            FROM          dbo.sysssislog AS LG1 INNER JOIN
                                                       (SELECT     source, MAX(starttime) AS starttime
                                                         FROM          dbo.sysssislog
                                                         WHERE      (event = ‘PackageStart’)
                                                         GROUP BY source
                                                         HAVING      (MAX(starttime) > DATEADD(dd, – 1, GETDATE()))) AS CUR ON CUR.source = LG1.source AND CUR.starttime = LG1.starttime
                            WHERE      (LG1.event = ‘PackageStart’)) AS PKG ON LG.executionid = PKG.executionid
ORDER BY LG.endtime DESC

PACKAGE DURATION

CREATE VIEW [dbo].[ssislog_PackageDurationCurrent]
AS
SELECT     TOP (100) PERCENT source AS PackageName, MIN(starttime) AS StartTime, MAX(starttime) AS EndTime, DATEDIFF(MI, MIN(starttime), MAX(starttime))
                      AS DurationInRoundedMinutes, DATEDIFF(ss, MIN(starttime), MAX(starttime)) AS DurationInTotalSeconds
FROM         dbo.sysssislog
WHERE     (event IN (‘PackageEnd’, ‘PackageStart’)) AND (starttime > DATEADD(dd, – 1, GETDATE()))
GROUP BY executionid, source
ORDER BY starttime DESC

I hope you find this useful.

Jul 232012
 

There is a great blog post on Connecting to a PostgreSQL Instance Using SQL Server Integration Services which you’ll want to read.  I will recap the steps directly from this post:

Installation of both drivers (32-bit & 64-bit) is identical. Here are the basic steps to get the driver working:

  1. Download the latest .msi file for Windows from this location: http://www.postgresql.org/ftp/odbc/versions/msi/
  2. Run the msi file on your SQL Server.
  3. Launch the ODBC Administrator Utility and choose the type of data source you need; File, System or User and click Add.
  4. The ODBC Administrator will present a list of drivers. Scroll to the bottom and you will see two options for PostreSQL; ANSI and Unicode. Select the version you need and click Finish.
  5. The Administrator will present a screen on which you must supply a database name, server name, user name and password.
  6. After you have supplied values for these fields, click the Datasource button and make sure the Use Declare/Fetch box is checked. The driver will fail to retrieve larger datasets if you do not check this box. I have not yet found a satisfactory answer for why this is so.

Now you are ready to build a new connection manager in SSIS and hook it to the PostGRES data source you just created. Use the following settings when building out the connection manager:

  1. Select the .Net ProvidersODBC Data Provider.
  2. Select the “Use connection string” radio button. Using the values you configured in the ODBC Administrator, build a connection string as follows: Dsn=PostgreSQL35W;uid=User1
  3. Enter the User name and Password in the fields provided.
  4. Test the connection and you should be ready to go.

 

Here are a few bits of information I’d like to add to this excellent blog post, mainly for my own purposes should I run into this again:

I noticed on the PostgreSQL msi page that there were several versions of the drivers.  I took the most recent of the 32bit, psqlodbc_09_01_0100-1.zip.  The 64 bit drivers have “64 bit” in the name.

The 32-bit ODBC drivers for PostgreSQL get installed here C:Program Files (x86)psqlODBC

After installing the 32 bit driver per the instructions, in your Visual Studio solution be sure to change your Project Properties – Debugging – Run64BitRuntime property to False. This will force it to run in 32-bit mode.   

If you are triggering your package from SQL Server Agent, set the SQL Server Agent job to run in 32-bit mode

For SQL2012, be sure to put the password in a Project Parameter so it is retained in the package on deployment to Integration Services, and doesn’t get stripped out due to package protection.

Apr 192012
 

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.

image

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. 

image

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.

image

image

Leave the template as Blank and click the Create button.

image

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

image

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

image

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’

 

image

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

image

TRIGGER THE WORKFLOW MANUALLY

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

image

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.

image

Click the start button to trigger the workflow.

image

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. 

image

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.

image

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

Feb 222012
 

 

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

image

Select Advanced System settings

On the Advanced tab, click on the Environment Variables button

image

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

image

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.

image

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.

image

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]”

image

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.

image

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.

image

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.

image

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

Jan 312012
 

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.

image

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.

image

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. 

image

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

image

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

Wordpress SEO Plugin by SEOPressor