Hi Team,

When we started the project, we used SQL Server 2008 R2 as DB and VS 2012(C#) for developing the UI. Recently we upgraded the .Net fwk to 4.5.2(VS 2013) and SQL server to 2014. This resulted in one odd behavior.

From our .Net code (a WCF service), we were using LoadFromSqlServer method to load the package deployed in SQL server 2008 R2, which was just working fine. However the same didn't work when we migrated to SQL Server 2014. Please note, we did upgrade all the SSIS packages using the upgrade package wizard from the SQL server 2014.

After going through this sample code, https://msdn.microsoft.com/en-us/library/ms136090.aspx, we understood, Microsoft recommends to use LoadPackage method to load the package from SQL server. We tried this and things worked just as expected.

Few Questions:

1. Is LoadFromSqlServer method decommissioned? Should we not use this to load the package deployed in SQL server >=2012 ?

2. Updating the code to use the LoadPackage method is the only or the best way to resolve this issue if the packages are deployed in SQL server that is >=2012 ?

It is quite important for our team to know this as we have to rebuild few other components based on the response to the above questions.

Appreciate your suggestions.

T

With the 2012 release of SSIS, we have two different deployment models. Package deployment, which is the "classic" model is alive and fully supported. The same code for running a package on 2005 will work for 2012 package deployment model projects. This is theMicrosoft.SqlServer.Dts.Runtime Namespace

Your code is attempting to load a 2012 solution built using the "project deployment model" with the "package deployment model" API. This is the Microsoft.SqlServer.Management.IntegrationServices.

Thanks for the quick response. 

I need a little more explanation and sorry for not explaining the below previously - 

We developed SSIS packages using BIDS 2008 and deployed them to SQL server 2008 R2. Now as we migrated to SQL server 2014, we directly imported those packages which were built in BIDS 2008 and upgraded them using the 'Upgrade Package' wizard from SQL server. NOTE : We are not recompiling the existing SSIS Packages using BIDS 2012

As said previously, we weren't able to invoke/execute the package deployed in SQL server 2014 using LoadFromSqlServer method, from namespace Microsoft.SqlServer.Dts.Runtime (which worked when the packages were deployed in SQL server 2008). However, using LoadPackage,from namespace Microsoft.SqlServer.Dts.Runtime, we were able to load/invoke/execute the SSIS Package deployed in SQL server 2014.

The questions from the original post remain.

Thanks!

Hi Ravikiran.S,

After testing the issue in my environment, I can use LoadFromSqlServer method to execute SSIS package from the MSDB database in SQL Server 2012.

The following sample code is for your reference:
Imports Microsoft.SqlServer.Dts.Runtime

Module Module1

    Sub Main()

        Dim pkgLocation As String

        Dim pkg As New Package

        Dim app As New Application

        Dim pkgResults As DTSExecResult

        pkgLocation = "\Maintenance Plans\MaintenancePlan0805"

        pkg = app.LoadFromSqlServer(pkgLocation, "server_name", Nothing, Nothing, Nothing)

        pkgResults = pkg.Execute()

        Console.WriteLine(pkgResults.ToString())

        Console.ReadKey()

    End Sub

End Module

As per my understanding, I think the issue is that you are trying to execute a package from the file system using LoadFromSqlServer method. Please note that the LoadFromSqlServer method is load a package from SQL Server MSDB database by specifying the server name, user name, and password. We should load a package using an appropriate method based on the package storage location.

Reference:
Application.LoadFromSqlServer Method

Thanks,
Katherine Xiong