Working with environments on a deployed SSIS 2012 Project

When an SSIS project is deployed from SQL Server Data Tools to the Integration Services Catalog, the Project and Package connections and parameters are stored together with the project and the packages. Each parameter or connection property in de Catalog is reconfigurable by means of environments. With environments properly setup, developers do not need to worry about changing connections or parameters when they release an upgrade to the Production server. Because the production server environment will automatically replace the test values in the connections and in the parameters by values setup in the production environment.

Catalog folder after 1st time deployment

An example of a project in the catalog with 1 package. No Environments have been created yet.

Create Environments

In this example we will create 2 new environments with a sql script: Test and Production.

---- create environment

EXEC [SSISDB].[catalog].[create_environment] @environment_name=N'Production', @environment_description=N'', @folder_name=N'SSIS_2012_Test'

GO

EXEC [SSISDB].[catalog].[create_environment] @environment_name=N'Test', @environment_description=N'', @folder_name=N'SSIS_2012_Test'

GO

After the script is executed: the 2 new environments are visible in the Environments folder.

Create environment variables

For project parameters

There is a project parameter in the SSIS project ‘DaysToLoad’ with value 15. This is shown in the project configuration, right-click on the project and select ‘Configure…’

Or with a T-SQL script, create an environment variable ‘prjDaysToLoad’ in both environments:

DECLARE @var int = N'30'

EXEC [SSISDB].[catalog].[create_environment_variable] @variable_name=N'prjDaysToLoad', @sensitive=False, @description=N''

    , @environment_name=N'Production', @folder_name=N'SSIS_2012_Test', @value=@var, @data_type=N'Int32'

EXEC [SSISDB].[catalog].[create_environment_variable] @variable_name=N'prjDaysToLoad', @sensitive=False, @description=N''

    , @environment_name=N'Test', @folder_name=N'SSIS_2012_Test', @value=@var, @data_type=N'Int32'

GO

Add a reference to the environment in the reference

The project needs to be made aware of the available environments first.

You can do this by right-clicking on the project name and select ‘Configure…’ and add the reference in the References panel. Or you can do it with a T-SQL script

Declare @reference_id bigint

EXEC [SSISDB].[catalog].[create_environment_reference] @environment_name=N'Production', @reference_id=@reference_id OUTPUT, @project_name=N'SSIS_2012_TestOdata3', @folder_name=N'SSIS_2012_Test', @reference_type=R

EXEC [SSISDB].[catalog].[create_environment_reference] @environment_name=N'Test', @reference_id=@reference_id OUTPUT, @project_name=N'SSIS_2012_TestOdata3', @folder_name=N'SSIS_2012_Test', @reference_type=R

GO

The result in the configure dialog window looks like this

Create an environment variable in production and test environment

---- create env variable in the 2 environments

DECLARE @var int = N'30'

EXEC [SSISDB].[catalog].[create_environment_variable] @variable_name=N'prjDaysToLoad', @sensitive=False, @description=N''

    , @environment_name=N'Production', @folder_name=N'SSIS_2012_Test', @value=@var, @data_type=N'Int32'

EXEC [SSISDB].[catalog].[create_environment_variable] @variable_name=N'prjDaysToLoad', @sensitive=False, @description=N''

    , @environment_name=N'Test', @folder_name=N'SSIS_2012_Test', @value=@var, @data_type=N'Int32'

GO

See the result in the ‘Production’ environment

Reference the environment variable from a parameter in the Project

or do it with a T-SQL script

---- reference the environment from the project parameter

EXEC [SSISDB].[catalog].[set_object_parameter_value] @object_type=20, @parameter_name=N'DaysToLoad', @object_name=N'SSIS_2012_TestOdata3', @folder_name=N'SSIS_2012_Test', @project_name=N'SSIS_2012_TestOdata3', @value_type=R, @parameter_value=N'prjDaysToLoad'

GO

This is the result

Be careful: The name of the environment variable needs to be identical in both environments

Run the package manually from the Catalog with a reference to the environment

When a package in this project is run we add the reference to one of the environments ‘Production’ or ‘Test’.

Right-click on a package to Execute. The execute package will warn that no environment is selected for the parameter that has a reference:

Check the Environment checkbox and select one of the environments

When OK is clicked the package will use the value for the parameter that comes from the selected environment.

Run the package in a SQL Server Agent job with a reference to the environment

In a job step the procedure is similar, select the package and check the environment checkbox and select one of the environments.

Conclusion

Once SSIS projects are deployed from a developer environment to a production environment you do not need to change parameter or connection settings in your Visual Studio project before the deployment. In the Integration Services you can set up one or more environments with variables for parameters and connections that differ from the developer environment. The developer has no worries about deploying in the production environment (either directly or through a deployment script), because the DBA administrator can setup up an environment in the Catalog so that the packages connect to databases and use parameters adapted for this environment.

In a production environment I tend to create only the ‘production’ environment and on a test or QA server I create only the ‘test’ environment. But you can still use several different environments in a production environment the package may need to address ETL operations on several production servers.

SSIS 2012 package with Odata source–execution failed (part 3)

In post SSIS 2012 package with Odata source-failure (part 2) I was able to deploy my package with Odata source. But the package failed to run.

Change from Project deployment to Package Deployment

I have converted my project with package connections from project deployment to Package deployment. Then I deployed the package on the server in MSDB catalog from SSIS. I.e. I connect with SSMS to Integration Services as an administrator and under the MSDB folder I added a new folder for my package. I then imported the dtsx into the mdsb package store. The Import worked. Still from within SSMS and connected to the Integration Services, i right-clicked on the package in the MSDB store and selected 'Run package'. In the Execute Package dialog window, I clicked on 'Execute' and the package actually ran. The Odata connector was opened, it read the data and imported into my database.

This experience made me realize what the real culprit  was: The OData connector is a 32-bit connector in SQL Server 2012. 

Odata source is 32-bit

To prove this: with SSMS I connected to the SQL database server and in the Integration services catalog, i right-clicked on the package that was deployed in Project deployment mode, then selected 'Execute...'. In the execute dialog windo in the 'Advanced' tab I checked 32-bit runtime. And the package ran.

Final conclusion

1. The Project deployment will work, but not with the Odata source as project connection. The Odata connection must be defined in each package as a package connection.
2. Run the deployed packages in 32-bit mode on the server

SSIS 2012 package with Odata source–failed to deploy project

On a test server with VS 2012 and SQL 2012 installed, I have created an SSIS project in project deployment mode. In the packages of the project I use the Odata source for SSIS. The packages run fine in VS 2012. But when I deploy the package to the Integration Services catalog on the same server as where I am running VS 2012 I get a deployment error:

‘Failed to deploy project. For more information, query the operation_messages view for the operation identifier '25084'.  (Microsoft SQL Server, Error: 27203)

To find out the cause of the problem, you can connect with SSMS to the SSISDB database where the SSIS projects are stored. Then select the record with operation_id from the operation_messages as suggested in the above-mentioned errormessage.

use SSISDB
go

select * from catalog.operation_messages where operation_id = 25084

The message column in the result contains the cause of the error:

‘Failed to deploy the project. Fix the problems and try again later.:Unable to create the type with the name 'ODATA'.’

In my case the deployment failed of SSIS packages that use the Odata source on the same server as where I tested successfully with VS2012.

Now I have to find out what is wrong, see SSIS 2012 package with Odata source-failure (part 2)

Process SSAS cube in good order with SSIS package

Based on an article by Benny Austin about SSIS Package to Process SSAS Cube

I have decided to process the cubes I have to maintain in the same order as suggested in his article.

1. Process Dimensions
1. Process default
2. Process Update
2. Process Facts
1. Process default
1. Process Partitions – default
2. Process Measure groups – default
3. Process Cubes – default
2. Process Data
1. Process Partitions – data
2. Process Measure groups – data
3. Process Cubes – data
3. Process Index
1. Process Dimensions - index
2. Process Cube measures - index
3. Process Partitions – index

A screenshot of what the SSIS package looks like in VS 2012:

SSIS 2012 increment variable in expression task

With SSIS 2012 the Expression Task is a very useful addition.

It can replace some of the tedious little script tasks when all you want to do is to change the value of a variable.

However! Although the programming language of C# in scripts can look very similar to the expression language used in SSIS it can be misleading.

Today I needed a counter in a foreach loop container that needed to be incremented by one if an error was found in a preceding task. I created a variable of type int that was initiallly set to zero. Inside the container I added an Expression Task to increment my counter by 1.

There I made my mistake: being used to incrementing with C# I wrote the following (incorrect) statement:

@[User::Errorcounter] += 1

This statement will evaluate to a value of 1 when you click the ‘Eveluate Expression’ button. since my variable was initially set to zero, this looked OK to me.

However, the variable will always stay at value 1, it will never increment!

The correct increment statement is (in a VB-like or SQL-like form)

@[User::Errorcounter] = @[User::Errorcounter] + 1

This also evalutates to 1, but it will increment to 2, 3 and so on when the package is run.

By the way: this can easily be verified when you set the initial value of the variable Errorcounter to another value, e.g. 5. With the incorrect statement the expression will still evaluate to 1. With the correct statement the expression will evaluate to 6.

Make a dynamic selection from SharePoint list data with the SSIS 2012 OData Source

Problem

Use SSIS to extract, transform and load data from a SharePoint list into a SQL Server table. Extraction from the SharePoint lists is done in a Data Flow Task by means of the OData Source for SSIS 2012. The Data must be dynamically extracted, i.e. only List data from the last <n> days must be read.

The OData Source Editor connects to the SharePoint REST service.

The URL of the REST service: http://myserver/_vti_bin/ListData.svc

A name after the URL of the service returns SharePoint Foundation list data in XML format as a standard Atom feed: http://myserver/_vti_bin/ListData.svc/Incidents

.In the OData Source Editor:

To make a selection of data created since 2014-08-04, the Filter is set to : $filter=Created+gt+datetime'2014-08-04'

There is no way to add a parameter in the Query options text box. How can the filter on the Created date be made variable so that only the last 5 days are read for a daily upload.

Solution

There are no expressions for the Filter property in the OData Source properties. However, in the Data Flow Task that contains the OData Source, we have Expressions that can be used to dynamically set the Query property of the OData Source.

First of all create a Project parameter called ‘LoadDays’ of type Int32 with the value 10.

In the SSIS package add a variable called ‘LoadCreated’ of type DateTime, with an expression to subtract 10 days from the current date: DATEADD( "d", - @[$Project::LoadDays] , GETDATE()  )

In the Control Flow pane, select the Data Flow Task that contains the OData Source.

In the Properties Window, click on the ellipsis button of the Expressions property.

In the Property Expressions Editor window, in the Property column select the OData Source Query property, click on the ellipsis button to open the Expression Builder.

The expression we create will need to mimic an OData filter expression like $filter=Created+gt+datetime'2014-08-04'

In the expression builder, the expression is:

"$filter=Created+gt+datetime'" + (DT_WSTR, 4)YEAR(@[User::LoadCreated]) + "-" + ( MONTH( @[User::LoadCreated]) < 10 ? "0" : "") + (DT_WSTR,2)MONTH( @[User::LoadCreated]) + "-" + (DAY( @[User::LoadCreated])<10 ? "0" : "" ) + (DT_WSTR,2)DAY( @[User::LoadCreated]) +"'"

The OData filter does not accept date literals like ‘201-8-4’, we need to add a leading zero for the month and day part, hence the conditional operator ( MONTH( @[User::LoadCreated]) < 10 ? "0" : "")

Click OK to close the Expression builder.

Click OK to close the Property Expressions Editor.

With the Data flow task selected, click the Data Flow tab. Select the OData Source control and edit. The filter property now holds the calculated values of the expression:

$filter=Created+gt+datetime'2014-08-04'

When the SSIS package runs it will only load list data created after the calculated date.

A deadlock was detected while trying to lock variables for R/W access

Problem

While testing an Integration Service package with a File Watcher task as described in Using the Script Task in SSIS to Process Data Files When They Arrive, the following error was thrown:

The script threw an exception: A deadlock was detected while trying to lock variables <variablelist> for read access and variables <variablelist> for read/write access. A lock cannot be acquired after 16 attempts. The locks timed out.

This was developed as an SSIS 2012 project and deployed on a SQL server in the Integration Services Catalog. The package ran fine when I tested it in Visual Studio and when I executed the task from the Catalog. The error came when I create a SQL Server Agent job to run the task.

First I read some articles about variable locking in SSIS; but why did my package run without errors when I ran it under my account?

Solution

The cause of the problem: it is the account under which the package is run in the Job step that does not have read/write access to the folders where the File Watcher Task and the ensuing File System Tasks are doing their work. In this case the job step was run as the SQL Server Agent Service Account.

Either give this service account read/write access to the folders used by the SSIS package (may not be a good idea) or use a special windows account with sufficient rights and create a SQL Server Agent proxy for this account, see Use a windows account to grant SSIS packages folder and file access on a file share

Environment variable in SSIS not being recognized

Article about Environment variable in SSIS not being recognized by James Serra

If the SSIS package is being scheduled to run in a SQL server job then the SQL server agent service needs to be restarted to recognize the environment variable change.

Change from Package connections to Project connections while upgrading packages to SSIS 2012

Migration of SSIS 2008 project to SSIS 2012. All packages have package connections, that need to be replaced by project connections to simplify maintenance. Problem is to go into the design of each and every package and replace every single use in source or destination or in SQL tasks.

Do not change the packages yet with package connections.

Make a backup of each package file!!!

Create all the new project Connections.

Create a new package where you will use all the Project connections, just for reference. A dataflow with source for each connection will suffice. We’ll need this.

Save the package and close it. right-click on it and select <> view code

In the XML code you will find the <connections>

<connections>

  <connection

    refId="Package\Data Flow Task\OLE DB Source.Connections[OleDbConnection]"

    connectionManagerID="{8EFE3DB7-FEF4-4921-8B90-B8A3D730A546}:external"

    connectionManagerRefId="Project.ConnectionManagers[AdventureWorksDW2012]"

    description="The OLE DB runtime connection used to access the database."

    name="OleDbConnection" />

</connections>

Inside a <connection> we find the project connectionManagerID and the connectionManagerRefId

For each Package with package connections, use the editor to find each source or destination that uses the package connection

<connections>

  <connection

    refId="Package\Data Flow Task\OLE DB Source.Connections[OleDbConnection]"

    connectionManagerID="Package.ConnectionManagers[AdventureWorks]"

    connectionManagerRefId="Package.ConnectionManagers[AdventureWorks]"

    description="The OLE DB runtime connection used to access the database."

    name="OleDbConnection" />

</connections>

Replace the connectionManagerID and the connectionManagerRefId by the project connectionManagerID and the connectionManagerRefId.

SSIS 2012 new REPLACENULL() function

From MSDN documentation – SQL server 2012

REPLACENULL (SSIS Expression)

Returns the value of second expression parameter if the value of first expression parameter is NULL; otherwise, returns the value of first expression.

This function was added to SSIS 2012 and is a useful addition and replaces lengthier expressions needed in SSIS 2008 or 2005.

E.g. after a merge join from 2 data sources A and B, in a conditional split 2 Boolean column values from both tables need to be compared, but sometimes the values on either side can be NULL. In case the value is NULL it is considered to be false.

• If values where NOT nullable: IsValueA == IsValueB
• When either has a NULL value the comparison will throw an error
• with SSIS 2008: (DT_BOOL) (ISNULL(IsValueA) ? false : IsValueA) == (DT_BOOL) (ISNULL(IsValueB) ? false : IsValueB)
• with SSIS 2012: REPLACENULL(IsValueA, false) == REPLACENULL(IsValueB, false)

Remark : in the above example I have accepted that if IsValueA is null while IsValueB = 0 the values are not technically equal, but in business terms they are.

Adding query options to the SSIS OData Source

 

In my previous article about SSIS OData source I created an SSIS 2014 package with an OData source connected to the Odata.org sample webservice.

OData query options

The OData Source has query options that can be used to alter the collection that is being used. I have made some tests on these options.

Option	Description
$expand	Expands related entities inline
$filter	Filters the results, based on a Boolean condition
$inlinecount	Tells the server to include the total count of matching entities in the response
$orderby	Sorts the results
$select	Selects which properties to include in the response
$skip	Skips the first n results
$top	Returns only the first n the results

See also Odata uri conventions paragraph 4

Set query options in OData Source editor

In the Data Flow select and edit the OData Source. The ‘Query options:’ textbox can be used to set options

$orderby

e.g. to order by the Product name: $orderby=Name. To Verify the result, click on the ‘Preview…’ button.

$filter

e.g. select all products with Rating 3: $filter= Rating eq 3

e.g. select products released in the year 2006: $filter= year(ReleaseDate) eq 2006

e.g. select discontinued products: $filter= DiscontinuedDate ne null

$top

e.g. select top 5 products ordered by Name descending: $top=5&$orderby=Name desc

$select

e.g. select only 2 columns on the products table: $select=Price, Name

$inlinecount, $format

Not usefull for SSIS purposes.

$expand

I will investigate this further once I have better samples than the odata.org samples..

SSIS OData Source available from Microsoft

A few days ago Microsoft has released an OData Source for SSIS 2012 / 2014. Downloads can be found in the links below:

Microsoft OData Source for Microsoft SQL Server 2012

Microsoft SQL Server 2014 Feature Pack

Documentation is available in the Books Online OData Connection Manager

Currently I am using SQL 2008 and 2012. There is no OData Source for SQL 2008. I’ve downloaded the OData Source for SQL 2012. Installed the msi file on my SQL test server and on my SQL Development PC with VS 2012.

Add an Odata Source to SSIS package in VS 2012

In a solution with the Project deployment model. Open the the Solution explorer, right click on Connection Manager, select New Connection Manager.

Select ODATA, Click on Add…

The OData Connection Manager Appears

For Service document location I have used the sample sources from odata.org.

http://services.odata.org/V3/OData/OData.svc/

Use Windows authentication to make the connection.

With the Odata Version 4 sample, the connection manager generated an error. Version 3 worked.

Click OK

The connection managers Tab shows the OData Source

Use the OData Source in a Data Flow task

IN the SSIS package with the OData Source, Create a new Data Flow task.

In the Data Flow Task add an OData Source. In the SSIS Toolbox the OData Source can be found under the Common tools, not under the Other Sources.

I cannot help but notice the return of the coloured icons (in contrast to the VS 2012 B&W scheme)

Drag the OData Source onto the Data Flow Task surface.

Edit the OData Source.

OData connection manager: OData Source

Use collection or resource path: Collection

Collection: Products (in the dropdown you will see all available collections (Products, Categories, Suppliers, etc…)

Click on preview to see the Products data (up to 20 first)

In the OData Source Editor look at the columns

Text columns are in Unicode text stream format, Dates are in database timestamp format.

For testing purposes I created a SQL table to accept the products:

CREATE TABLE [dbo].[ImportProduct](

    [ProductId] [int] NOT NULL,

    [ProductName] [nvarchar](255) NULL,

    [ProductDescription] [nvarchar](255) NULL,

    [ReleaseDate] [datetime] NULL,

    [DiscontinuedDate] [datetime] NULL,

    [Rating] [int] NULL,

    [Price] [decimal](12, 4) NULL,

 CONSTRAINT [PK_ImportProduct] PRIMARY KEY CLUSTERED 

(

    [ProductId] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

In the SSIS Dataflow task I can now convert the data from the OData source to the format of the destination table in SQL server.

Next, an ADO.NET Destination is added.

The result after running the package: 11 rows are inserted into the SQL table.

Conclusion

The SSIS OData Source is a nice addition to SQL SSIS 2012 an d2014. It works with OData version 3.0. Now all we need is an OData Destination  component.

In my next post I have tried adding query options to SSIS OData source

Create a WCF service to return List of type <T> and to be used by SSRS or SSIS

Problem

Data on system A can only be read by a proprietary interface. The data must be used by applications on system B equipped with SQL Server 2008 R2. The data will be read by SSIS packages to store in system B for further processing, and by SSRS for reporting.

Solution

To have a uniform access method, the data from system A will be passed along by a WCF service.

The solution will be made with Visual Studio 2012. Care must be taken that SSRS and SSIS can read the service data correctly. Therefore WCF service will use basic Http binding.

Other apps developed with VS 2012 can take full advantage of the List<Type T> returned by the WCF service. Odata has not been chosen since no viable solution to read Odata in SSRS has been found. Type T classes will be defined as MessageContracts in the service.

Walkthrough

• Create a new project WCF Service Application with Visual Studio
  • Under languages – select Visual C# - WCF – WCF Service Application
  • Call the project SysAService (the solution has the same name)
  •  
• Add a reference to the proprietary library with which to communiciate with System A.
  • this can be your own dll library too.
• Rename the Interface IService1 to ISysAComm and the service to SysAcomm. Rename the svc and the cs file too.
  
• · In the case of the proprietary library of System A , we had to change the Target framework in Properties of the application to .NET Framework 3.5
• · In Web config, verify that basicHttpBinding in the service endpoints definition
  

  <system.serviceModel>

  <services>

  <service name="SysAService.SysAComm" behaviorConfiguration="SysAService.SysACommBehavior">

  <!-- Service Endpoints -->

  <endpoint address="" binding="basicHttpBinding" contract="SysAService.ISysAComm">

  
  
• · In the Interface replace the default code by a method to retrieve a list of data from system A
  
  
  
  • the method is called GetDeviceList
    
  • the method returns a List<MyDevice>
    
  • the class MyDevice is also defined in the ISysAComm.cs file
    
    
    
    

    namespace SysAService

    {

    [ServiceContract]

    public interface ISysAComm

    {

    [OperationContract]

    List<MyDevice> GetDeviceList(string LocationCode);

    }

    [MessageContract(IsWrapped = true)]

    public class MyDevice

    {

    [DataMember]

    public string Abbreviation { get; set; }

    [DataMember]

    public string Designation { get; set; }

    [DataMember]

    public int Id { get; set; }

    public MyDevice()

    {

    }

    public MyDevice(string abbreviation, string designation, int id)

    {

    Abbreviation = abbreviation;

    Designation = designation;

    Id = id;

    }

    }

    }

  
  
• Now that we have the Interface to read and the class defined, edit the cpode in the service class SysAComm.svc
  
• Add a using statement to use with the soapexception
  
  
  
  • using System.Web.Services.Protocols;
  
  
• Add ServiceBehaviour before the class SysAComm
  
  
  
  

  [ServiceBehavior(AddressFilterMode = AddressFilterMode.Any)]

  public class SysAComm : ISysAComm

  
  

   

  
  
• Create the method GetDeviceList which will read the proprietary data and create a list of objects of type MyDevice.
  
• The complete list of SysAcomm.svc code
  
  
  
  

  using System;

  using System.Collections.Generic;

  using System.Linq;

  using System.Runtime.Serialization;

  using System.ServiceModel;

  using System.ServiceModel.Web;

  using System.Text;

  using System.Web.Services.Protocols;

  namespace SysAService

  {

      [ServiceBehavior(AddressFilterMode = AddressFilterMode.Any)]

      public class SysAComm : ISysAComm

      {

          public List<MyDevice> GetDeviceList(string LocationCode)

          {

              try

              {

                  SysAReader sys_a_reader = new SysAReader();   // the proprietary librra

                  List<SysADevice> sys_a_devices = sys_a_reader.GetDeviceList(LocationCode);

                  List<MyDevice> my_devices = null;

                  foreach (var sys_device in sys_a_devices)

                  {

                      if (my_devices == null)

                          my_devices=new List<MyDevice>();

                      my_devices.Add(new MyDevice(sys_device.Abbreviation, sys_device.Designation, sys_device.Id));

                  }

                  return my_devices;

              }

              catch (Exception ex)

              {

                  SoapException se = new SoapException(ex.Message, SoapException.ClientFaultCode, ex);

                  throw ex;

              }

          }

      }

  }

Debug the service

• Debug the service with the WCF Test Client : select the service SysAcom.svc in the solution explorer and hit F5.
  
• The WCF Test client starts:
  
  
• Doubleclick the GetDeviceList() method under ISysAComm, the Request and Response panes open.
  
  
• In the Request pane, the Parameter LocationCode has value (null), we can give a valid value to return a List of type MyDevice.
  
  
• When we click on the Invoke button, the method is called with the argument A123, click OK when the Security Warning dialog box opens.
  
• The Response pane is filled with data, in this case a list of 14 elements
  
  
• What we will need to call this service from a SSRS report or SSIS package can be found in the XML tab.
  
  
• Close the test client window.

Publish the WCF Service application to an IIS server

• In the visual studio project, rightclick on the SysAService project and select Publish… from the dropdown.
  
• The Publish Web wizard appears, select <new profile…> from the combobox.
  
  
• In the New profile dialog box enter a profile name, in the example we use the name of the IIS server
  
  
• Connection
  
  
  
  • Publish method: Web Deploy (make sure the web deployment agent is installed on the iis server
    
  • Server: TestWebServer (the test server is an intranet server in the Windows domain, it has no domain extensions)
    
  • Site name, the site will be published under the ‘root’ folder
    
  • User name and password: use an account with administrative rights on the iis server
    
  • Destination URL: http://TestWebserver/SysAService
    
  •  
    
  • Click on the ‘Publish’ button
    
  • The publish actions are executed and an I.E. windows is opened with the Destination URL.
  
  
• If you get HTTP error 500.19: most likely the application pool used is not for.NET 3.5
  
  
  
  • Start IIS Manager and connect to the TestWebServer
    
  • Open the branch under sites / root / SysAService
    
    
  • In the right hand side pane, click on Basic Settings… to open the Edit application dialog.
    
    
  • Click on the ‘Select…’ button to select an application pool with the correct .NET Framework. In our case we use .NET Framework 3.5, so we select a pool with .net Framework 2.0.
  
  
• When the app pool is adjusted, refresh the I.E. where you had the error. You should see the directory of the webservice
  
  
• When we click on the link SysAComm.svc the SysACom service page should display
  
  

  Conclusion

  
  

  The WCF service is now ready to be consumed by a Reporting services report or by an Integration Services package (both version 2008 R2). Read my blog about How to consume a WCF service in a source script in SSIS