SSIS package execution fail after deploy with dtexec command

  • I developed my package using BIDS and successfully tested it on local computer. On deployment to new server I have to run my package with command prompt utility.

    Initially i faced problem creating deployment installer from BIDS, there was an error like this that unable to copy the config file to deployment folder as it already exist.Though i can physically see the config file moved to deploy folder but the deploymentmanifest was not generated. Hence I did manual deployment. Below is error details.

    Error 9 System.ApplicationException: Could not copy file "C:\Project\IntegrationServices\SFRIntegrationServices\PCSConfiguration.dtsConfig" to the deployment utility output directory "C:\Project\IntegrationServices\SFRIntegrationServices\bin\Deploy". ---> System.IO.IOException: The file 'C:\Project\IntegrationServices\SFRIntegrationServices\bin\Deploy\PCSConfiguration.dtsConfig' already exists. at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath) at System.IO.File.InternalCopy(String sourceFileName, String destFileName, Boolean overwrite) at Microsoft.DataTransformationServices.Project.DataTransformationsProjectBuilder.CopyFiles(ICollection fileNames, String outputPath) --- End of inner exception stack trace --- at Microsoft.DataTransformationServices.Project.DataTransformationsProjectBuilder.CopyFiles(ICollection fileNames, String outputPath) at Microsoft.DataTransformationServices.Project.DataTransformationsProjectBuilder.CreateDeploymentUtility(IOutputWindow outputWindow)

    The MAJOR issue is after manual deployment the config file path is being used as one specified at design time. Please look into below error.

    H:\>DTExec /FILE "D:\DTS\Packages\pkgSFRLocation.dtsx" /SET \Package.Variables.Value;"D:\DTS\Input\PalletControl\LOC00022"

    Microsoft (R) SQL Server Execute Package Utility

    Version 9.00.3042.00 for 32-bit

    Copyright (C) Microsoft Corp 1984-2005. All rights reserved.

    Started: 9:15:40 AM

    Warning: 2008-03-06 09:15:50.89

    Code: 0x80012012

    Source: pkgSFRLocation

    Description: The configuration file name "C:\Project\IntegrationServices\SFRI

    ntegrationServices\PCSConfiguration.dtsConfig" is not valid. Check the configura

    tion file name.

    End Warning

    Warning: 2008-03-06 09:15:50.89

    Code: 0x80012059

    Source: pkgSFRLocation

    Description: Failed to load at least one of the configuration entries for the

    package. Check configurations entries and previous warnings to see descriptions

    of which configuration failed.

    End Warning

    DTExec: The package execution returned DTSER_SUCCESS (0).

    Started: 9:15:40 AM

    Finished: 9:15:54 AM

    Elapsed: 14.172 seconds

    Last i tried specifying the path externally from dtexec options then it failed because my master config file has values for 3 variables (User::MailRecepient,User::CopyRecepient,User::Profile) which has not been declared in this current package(pkgSFRLocation.dtsx) I am trying to execute. These variables have values specified for other packages which share the config file.

    H:\>DTExec /FILE "D:\DTS\Packages\pkgSFRLocation.dtsx" /conf "D:\DTS\Packages\PC

    SConfiguration.dtsConfig" /SET \Package.Variables[InputParamFileName].Value;"D:\

    DTS\Input\PalletControl\LOC00022"

    Microsoft (R) SQL Server Execute Package Utility

    Version 9.00.3042.00 for 32-bit

    Copyright (C) Microsoft Corp 1984-2005. All rights reserved.

    Started: 9:25:42 AM

    Warning: 2008-03-06 09:25:43.98

    Code: 0x80012012

    Source: pkgSFRLocation

    Description: The configuration file name "C:\Project\IntegrationServices\SFRI

    ntegrationServices\PCSConfiguration.dtsConfig" is not valid. Check the configura

    tion file name.

    End Warning

    Warning: 2008-03-06 09:25:43.98

    Code: 0x80012059

    Source: pkgSFRLocation

    Description: Failed to load at least one of the configuration entries for the

    package. Check configurations entries and previous warnings to see descriptions

    of which configuration failed.

    End Warning

    Warning: 2008-03-06 09:25:44.40

    Code: 0x80012017

    Source: pkgSFRLocation

    Description: The package path referenced an object that cannot be found: "\Pa

    ckage.Variables[User::MailRecepient].Properties[Value]". This occurs when an att

    empt is made to resolve a package path to an object that cannot be found.

    End Warning

    Warning: 2008-03-06 09:25:44.42

    Code: 0x80012017

    Source: pkgSFRLocation

    Description: The package path referenced an object that cannot be found: "\Pa

    ckage.Variables[User::CopyRecepient].Properties[Value]". This occurs when an att

    empt is made to resolve a package path to an object that cannot be found.

    End Warning

    Warning: 2008-03-06 09:25:44.43

    Code: 0x80012017

    Source: pkgSFRLocation

    Description: The package path referenced an object that cannot be found: "\Pa

    ckage.Variables[User::Profile].Properties[Value]". This occurs when an attempt i

    s made to resolve a package path to an object that cannot be found.

    End Warning

    DTExec: The package execution returned DTSER_SUCCESS (0).

    Started: 9:25:42 AM

    Finished: 9:25:44 AM

    Elapsed: 2.265 seconds

    PLEASE HELP me resolve either these problems ...I am unable to undestand how to deploy this successfully.

  • I resolved this issue.

    Thanks

  • how? how did you solve it? please help!

  • I'm having the same problem. How did you resolve it?

  • Since writing this post, have you found a way to eliminate the warning message? Seems a little short sighted of Microsoft to think that the various environments that companies use to escalate design changes through would all allow exactly the same path as your development workstation, however after reading this article... http://www.sqlservercentral.com/articles/SQL+Server+2005+-+SSIS/3219/ ... skip to the "One Configuration Per Connection Manager" ... it appears as though we are limited with the current version.

    I should mention that there is not an error generated, only a warning, so the package will run just fine. Looks what the original post was referencing was a bit more complex, so could have generated an error.

  • That article answers nothing...

  • can you assist with the step to resolve this issue please

  • I was looking for information on something else so I didn't review your post in detail but the SQL 2005 documentation on the dtsexec options says:

    "you cannot use the /ConfigFile option to replace configured values that you also specified at design time."

    Hope this helps,

    Jim

  • open the '.dtsx' file in a text editor and look for the path to the config file. Change the path to reflect the new path in the deployment box. try running the package and this time it will recognize the config file.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply