SSIS package + .net

Question

Hi,

I am executing my SSIS package in VB.net. In my local machine when i execute the code it works fine and when i deployed into my server, it fails sometime....Mostly its executing but sometimes it says Package execution failed.

Here is my piece of code......... Can anyone tell me what went wrong here.....

           Dim pkg As Microsoft.SqlServer.Dts.Runtime.Package
           pkg = Nothing
           Dim pth As String = ConfigurationSettings.AppSettings("SSISPackagePath")
           Dim app As Microsoft.SqlServer.Dts.Runtime.Application
           app = New Microsoft.SqlServer.Dts.Runtime.Application()
           pkg = app.LoadPackage(pth, Nothing)
                    pkg.Variables("projectCode").Value = Projectcode
                    Dim strConfigPath As String = pkg.Configurations.Item(0).ConfigurationString()
                    Dim FileExists As IO.File
                    If FileExists.Exists(strConfigPath) Then

                        pkg.Execute()
                       pkg.Dispose()
                    pkg = Nothing
                    app = Nothing

Answer 1

My first guess would be that it is an authentication error. Perhaps you were using your NT account to authenticate against SQL and now it is running under your application's service account.

Answer 2

When loading it to Integration Services, I've had good luck when I set the Package Protection Level to "Rely on Server Storage and Roles for access control" and then run it from a job.