November 19, 2010 at 8:42 am
Hi,
i have a package that uses flat file connection to get the data from txt file, then dump into a table in Sql server. The protection level of the package is set to EncryptSensitiveWithUserKey. The package runs fine when ran from the studio.
I am using Integration Services to import the package on the server.
Then using the CmdExec on the sql server agent, i am calling the package from the server. When i start the job, it fails with the error below. Any ideas why? Please advise!!
Executed as user: domain\administrator. ...4035.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005.
All rights reserved.
Started: 8:08:35 AM Progress: 2010-11-18 08:08:37.17
Source: Copy Data into Temp Table
Validating: 0% complete End Progress Progress: 2010-11-18 08:08:37.17
Source: Copy Data into Temp Table
Validating: 50% complete End Progress Progress: 2010-11-18 08:08:37.36
Source: Copy Data into Temp Table
Validating: 100% complete End Progress Warning: 2010-11-18 08:08:37.36 Code: 0x80047076
Source: Copy Data into Temp Table DTS.Pipeline
Description: The output column "PoBox" (56) on output
"Flat File Source Output" (2) and component "Flat File Source" (1)
is not subsequently used in the Data Flow task. Removing this unused output column
can increase Data Flow task performance. End Warning Warning: 2010-11-18 08:08:37.36 Code: 0x80047076
Source: Copy Data into Temp Table DTS.Pipeline Desc... Process Exit Code 1. The step failed.
I do get warnings when i execute the package from the business intelligence studio. But the package runs fine. When i run the same package, i get an error on the sql server agent. Just not sure if this is a problem related to something else..like user permission?
November 19, 2010 at 8:46 am
That is a warning only, not an error and should not cause the step to fail. Are you sure that you provided all of the message text?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
November 19, 2010 at 8:47 am
They won't normally fail on warnings like that. I'd suspect a permissions issue, with a difference between the account using BIDS and the account the job belongs to in SQL Server.
Keep in mind that BIDS has its own engine for SSIS, separate from the service. That allows building SSIS packages on computers that don't have the service running. It also means you can run into weird issues like this sometimes.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 19, 2010 at 8:54 am
Yes, when i execute this package from the agent, then after 4 seconds, it stops with with the error message i provided above. Thats the error message i get when i go into the View History.
I was assuming the same thing whether this was a permission issue. will check with my settings here to see if i can assign this to a different user.
Also, there are a number of packages running on this server. They have been assigned to the same user that this package is. They run fine, its just with this one i am having problem with.
Here is my cmdExec if this helps any.
dtexec /dts "\MSDB\Package" /ser ServerName
November 20, 2010 at 6:18 am
It certainly is a permission issue. You encrypt the package with EncryptSensitiveWithUserKey, but then you execute the package under a different user (the domain account of SQL Server Agent to be precise). If I'm not mistaken, the user key is a combination of your user account and your computer name. Just set the protection level to EncryptSensitiveWithPassword.
See also this topic: http://support.microsoft.com/kb/918760
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 20, 2010 at 9:04 am
Hi,
I changed the protection level of the package in BIDS as EncryptSensitivewithPassword. Upon saving the package, it asked me to enter a new password. When I imported this package in the integration services, I selected Rely on server storage and roles for access control for the protection level. Since my package in BIDS had a password, it asked me for a password and i saved it in the Integration services.
When ran, the job failed with the same error. Not sure what i am still doing wrong.
November 20, 2010 at 12:19 pm
bladerunner148 (11/20/2010)
Hi,I changed the protection level of the package in BIDS as EncryptSensitivewithPassword. Upon saving the package, it asked me to enter a new password. When I imported this package in the integration services, I selected Rely on server storage and roles for access control for the protection level. Since my package in BIDS had a password, it asked me for a password and i saved it in the Integration services.
When ran, the job failed with the same error. Not sure what i am still doing wrong.
Not sure what went wrong either, but you're mixing protection levels. Rely on server storage means that you rely on SQL Server security for access and execution of the package. Are you sure the SQL Server Agent account has the necessary permissions in the MSDB database?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 21, 2010 at 10:25 am
Hi,
I did find one thing when i ran the package from the integration services. Here, I can see the detailed error message..should have done this earlier.
Error: The product level is insufficient for component "Flat File Source".
The task is failed right here.
I installed work stations using developer edition. Do i need to install anything else?
I hope this helps in finding the bug.
November 21, 2010 at 11:23 am
Maybe it has something to do with the problems mentioned in this thread:
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 22, 2010 at 6:17 am
bladerunner148 (11/21/2010)
Hi,I did find one thing when i ran the package from the integration services. Here, I can see the detailed error message..should have done this earlier.
Error: The product level is insufficient for component "Flat File Source".
The task is failed right here.
I installed work stations using developer edition. Do i need to install anything else?
I hope this helps in finding the bug.
What edition of SQL Server are you trying to run it from?
SSIS packages that import/export data won't run under Workgroup Edition's SQL Agent.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 22, 2010 at 7:26 am
I installed the Sql Server 2005 Developer Edition 64-bit. All i installed was the Work Station Components, Books online and Development tools. Do i need to check the Integration Services as well in order for the Flat file to work?
Some posts suggested to install IS and then uninstall IS once the Sql Server is installed. That way, all the files necessary to run Flat File will be copied.
Please advise!
November 22, 2010 at 8:31 am
Yes, you'll need to have IS installed and running to run an SSIS package outside of BIDS.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 22, 2010 at 9:43 am
Ok, i installed IS with the WorkStations. The package now runs fine from the Integration Services. Before, this was errored out.
Now, when I try to run this package from the Sql Server Agent, I get the same error that I was getting before. Is there a better way to find the error message in detail thats coming from Sql Server Agent? I don't see the full error message.
November 22, 2010 at 11:16 am
Try enable logging in your package.
See this tutorial of MSDN: http://msdn.microsoft.com/en-us/library/ms167061.aspx
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 22, 2010 at 11:23 am
bladerunner148 (11/22/2010)
Ok, i installed IS with the WorkStations. The package now runs fine from the Integration Services. Before, this was errored out.Now, when I try to run this package from the Sql Server Agent, I get the same error that I was getting before. Is there a better way to find the error message in detail thats coming from Sql Server Agent? I don't see the full error message.
What's installed on the server where you have SQL Agent running? Is IS running there? What edition of SQL Server?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply