November 10, 2010 at 11:57 am
Hi everyone, I am having a problem while I am creating a scheduled job in SQLServer 2005 Server Agent with SSIS package.
The SSIS package is developed in SQLServer 2008 10.0.1600.22 Version, and run in VS OK. However when I upload file to server and try to schedule a job and load this SSIS package, I met this error message:
===================================
The package failed to load due to error 0xC0010014 "One or more error occurred. There should be more specific errors preceding this one that explains the details of the errors. This message is used as a return value from functions that encounter errors.". This occurs when CPackage::LoadFromXML fails.
------------------------------
Program Location:
at Microsoft.SqlServer.Dts.Runtime.Application.LoadPackage(String fileName, IDTSEvents events, Boolean loadNeutral)
at Microsoft.SqlServer.Dts.Runtime.Application.LoadPackage(String fileName, IDTSEvents events)
at Microsoft.DataTransformationServices.Controls.PackageProtectionUtils.<>c__DisplayClass4.<LoadPackageFromFile>b__3(String password, IDTSEvents events)
at Microsoft.DataTransformationServices.Controls.PackageProtectionUtils.LoadPackageWithPassword(PackageLoader loader, IWin32Window dialogParent, String& packagePassword)
at Microsoft.DataTransformationServices.Controls.PackageProtectionUtils.LoadPackageFromFile(String file, IWin32Window dialogParent, String& packagePassword)
at Microsoft.DataTransformationServices.DTSExecUI.Controls.GeneralViewCtrl.GetPackage()
===================================
The package failed to load due to error 0xC0010014 "One or more error occurred. There should be more specific errors preceding this one that explains the details of the errors. This message is used as a return value from functions that encounter errors.". This occurs when CPackage::LoadFromXML fails.
------------------------------
Program Location:
at Microsoft.SqlServer.Dts.Runtime.Wrapper.ApplicationClass.LoadPackage(String FileName, Boolean loadNeutral, IDTSEvents90 pEvents)
at Microsoft.SqlServer.Dts.Runtime.Application.LoadPackage(String fileName, IDTSEvents events, Boolean loadNeutral)
Does anyone have any suggestion? Thanks very much!!
November 11, 2010 at 1:15 am
Does the package have a password or is there any passwords saved in any connection strings? That is the first thing that comes to mind..
CEWII
November 11, 2010 at 1:19 am
I agree with Elliot.. did you save any passwords or authentication information to data sources?
If you configure dtsx packages with passwords, you need to save them with the passwords encrypted.
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
November 11, 2010 at 1:28 am
They are encrypted by default, the default config saves them encrypted so that the user who entered them can decrypt them but nobody else.. I personally NEVER save passwords in packages and opt for config files or database settings that I get them from.
CEWII
November 11, 2010 at 3:24 pm
Henrico Bekker (11/11/2010)
I agree with Elliot.. did you save any passwords or authentication information to data sources?If you configure dtsx packages with passwords, you need to save them with the passwords encrypted.
Hi Elliot & Henrico,
Yes, you are exactly right that I have password in my db connection strings. However, when I delete them from XML file, the same issue.
I am not quite familiar with this part, please advice:
1. During development, use the local DB as connection string to build the SSIS package, and then use package configuration to manage connection string, DB name, DB user, DB password, in a separate config file, that's right?
Should I use a variable to setup connection string ? what's the typical way to do that?
2. The Send Email task, what if the SMTP server has authentication requirement, in the package configuration there is no place to manage those, do you have any suggestions?
Thanks very much!
November 11, 2010 at 9:24 pm
I am not quite familiar with this part, please advice:
1. During development, use the local DB as connection string to build the SSIS package, and then use package configuration to manage connection string, DB name, DB user, DB password, in a separate config file, that's right?
Should I use a variable to setup connection string ? what's the typical way to do that?
2. The Send Email task, what if the SMTP server has authentication requirement, in the package configuration there is no place to manage those, do you have any suggestions?
I'll answer #2 first, From BOL: The SMTP connection manager supports only anonymous authentication and Windows authentication. It does not support basic authentication.
#1, I tend to try and use integrated security in every case I can, I try really hard not to deal with password, I strongly recommend this if possible. I have handled connection strings in a couple different ways, depending on the level of security desired. If we had strong file system security on config files then we sometimes put passwords there. These files can contain any config information and usually contained a full connection string without breaking out the individual elements. In another case we stored encrypted connection strings in the database and built a custom assembly that decrypted them, this method was a little rough in that we elected to use different keys for prod and all non-prod environments. This method worked but I found it to be more trouble than it was worth. Using trusted security and controlled config files eliminated a lot of problems.
I hope this answered your question..
CEWII
November 12, 2010 at 10:02 am
Elliott Whitlow (11/11/2010)
I'll answer #2 first, From BOL: The SMTP connection manager supports only anonymous authentication and Windows authentication. It does not support basic authentication.
#1, I tend to try and use integrated security in every case I can, I try really hard not to deal with password, I strongly recommend this if possible. I have handled connection strings in a couple different ways, depending on the level of security desired. If we had strong file system security on config files then we sometimes put passwords there. These files can contain any config information and usually contained a full connection string without breaking out the individual elements. In another case we stored encrypted connection strings in the database and built a custom assembly that decrypted them, this method was a little rough in that we elected to use different keys for prod and all non-prod environments. This method worked but I found it to be more trouble than it was worth. Using trusted security and controlled config files eliminated a lot of problems.
I hope this answered your question..
CEWII
Thanks very much CEWII for your response, it really helps me!
Regarding Q2, I think I need to customize a little bit, if SMTP server doesn't have authentication requirement, then I will use SEND EMAIL Task, otherwise, use Database sp_sendemail function.
Regarding Q1, which is the biggest concern of mine. As you said, typically I also make an assembly and encrypted password for vb code. In this case, I would rather use a full connection strings in the configuration file. My question is, even though I still got the same error message while I deploy the SSIS package.
What I did:
1. During development steps, I need to create a connection string to test, and afterwards, I setup package DTSProtectionLevel = Don'tSaveSensentive. Then I create a package configuration file with that connection string, and edit the full connection string to that config file.
2. copy .dtsx & .config files to Production server, and load as new SSIS job, then I got the error.
Please advice me what's the problems? Or...did I do wrong on the connection string setup? thanks.
November 12, 2010 at 10:13 am
Besides, when I build SSIS package on local, I create the configuration file also on Local, therefore, I found this:
<DTS:Property DTS:Name="ConfigurationString">C:\....\....\....\....\DBConnection.dtsConfig</DTS:Property>
Should I also put the same DBConnection.dtsConfg to the same directory?
Thanks.
November 12, 2010 at 11:46 am
The config file can be anywhere that the process can reach it, so I'm not sure that is your problem. However, whatever the path to the config file is on dev needs to be the same on prod, so I generally use a path like: D:\ConfigFiles\ProcessOrProductName
I want to expound on the encrypted connection string. In this case I don't set the connection string directly from the config file. I use the config file to set a string variable which is then used by the script task to decrypt the value, then the connection string is set from the decrypted value, this can be done directly or you can use a second string variable which is used by an expression to set the actual connection string. Under either method, it is important to have a connection string available when the package opens at design-time or you will have validation failures.
CEWII
November 12, 2010 at 12:39 pm
I would also recommend using an environment variable if you are going to be switching your packages between servers often. Then you could store the package configuration file wherever you want and then just change the environment variable to that file path - which could change depending on which machine you are on.
November 12, 2010 at 1:52 pm
Thanks CEWII & Tmitchelar, I will definitely have the Environmental variable, which is much easier for management.
My problem is:
1. Set the connection string during development stage in VS Business Intelligent.
2. Set package protectionlevel = Don'tSaveSensitive
3. Setup package configuration and check database connection string as variable in config file.
4. Copy *.dtsx & config file to Production
5. Update *.dtsx's config file directory under XML format.
6. Update config file's connection string content
7. Use SSMS to create a new job in SQL Agent server and load as a SSIS package from file system.
after this step, I still get the original error on 1st floor.
Any suggestions ?
Thanks.
November 12, 2010 at 1:59 pm
BTW, I was able to setup a job on my local SSMS, but when I copy the files to production, I got the error on the 1st floor, the wrong password.....any thoughts?
November 12, 2010 at 2:40 pm
princa (11/12/2010)
Thanks CEWII & Tmitchelar, I will definitely have the Environmental variable, which is much easier for management.My problem is:
1. Set the connection string during development stage in VS Business Intelligent.
2. Set package protectionlevel = Don'tSaveSensitive
3. Setup package configuration and check database connection string as variable in config file.
4. Copy *.dtsx & config file to Production
5. Update *.dtsx's config file directory under XML format.
6. Update config file's connection string content
7. Use SSMS to create a new job in SQL Agent server and load as a SSIS package from file system.
after this step, I still get the original error on 1st floor.
Any suggestions ?
Thanks.
Two things come to mind. Depending on OS on server, you might need to restart the machine after configuring your environment variable. Second, you need to make sure that your SQL Server Agent account has the correct permissions...since the SQL Server Account is typically different than the user account that is used in BIDS.
November 12, 2010 at 2:56 pm
tmitchelar (11/12/2010)
Two things come to mind. Depending on OS on server, you might need to restart the machine after configuring your environment variable. Second, you need to make sure that your SQL Server Agent account has the correct permissions...since the SQL Server Account is typically different than the user account that is used in BIDS.
When you say the correct permissions for SQL Server Agent account, the permissions are directory permissions or SSMS permissions?
Now, I create the manifest deploy files and try to install on SQL Server, then I got this:
===================================
Could not save the package "E:\SSIS\Amp_Applicant_Info_Update.dtsx" to SQL Server "tintin\sql2005". (Package Installation Wizard)
===================================
The package failed to load due to error 0xC0011008 "Error loading from XML. No further detailed error information can be specified for this problem because no Events object was passed where detailed error information can be stored.". This occurs when CPackage::LoadFromXML fails.
------------------------------
Program Location:
at Microsoft.SqlServer.Dts.Runtime.Application.LoadPackage(String fileName, IDTSEvents events, Boolean loadNeutral)
at Microsoft.SqlServer.Dts.Runtime.Application.LoadPackage(String fileName, IDTSEvents events)
at Microsoft.SqlServer.Dts.Deployment.DtsInstaller.SavePackageToSqlServer(WizardInputs wizardInputs, String packagePassword, Boolean bUseSeverEncryption, String serverName, String userName, String password, String packageFilePath, List`1 configFileNames)
at Microsoft.SqlServer.Dts.Deployment.DtsInstaller.InstallPackagesToSqlServer(WizardInputs wizardInputs)
===================================
The package failed to load due to error 0xC0011008 "Error loading from XML. No further detailed error information can be specified for this problem because no Events object was passed where detailed error information can be stored.". This occurs when CPackage::LoadFromXML fails.
------------------------------
Program Location:
at Microsoft.SqlServer.Dts.Runtime.Wrapper.ApplicationClass.LoadPackage(String FileName, Boolean loadNeutral, IDTSEvents90 pEvents)
at Microsoft.SqlServer.Dts.Runtime.Application.LoadPackage(String fileName, IDTSEvents events, Boolean loadNeutral)
November 12, 2010 at 3:01 pm
I checked the SQL Agent Server account, it uses local system as log on account. So need to modify local system's permission on directory and SQL Server?
Thanks
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply