March 20, 2006 at 7:49 am
being fairly new to SQL2005 and Buisness Intelligence, i thought if make a simple project using the data wizard to copy data from an old SQL2000 Server to the SQL2005 server. I managed this quite easily, and also managed to set this up as a job in the SQL2005 server.
I have tried to run that job this morning and it failed, i loaded the porject back up in SQL Server Business Intelligence Development Studio and tried to debug it. it cane up with the folowing error
Error: 0xC002F304 at NonTransactableSql, Execute SQL Task: An error occurred with the following error message: "Could not find a part of the path 'C:\Documents and Settings\Administrator.CBI\Local Settings\Temp\2\tmpB.tmp'.".
Task failed: NonTransactableSql
SSIS package "Package2.dtsx" finished: Failure.
Can anybody guide me as to the error of my ways.
Thank you in advance
March 23, 2006 at 8:00 am
This was removed by the editor as SPAM
January 11, 2007 at 3:30 am
I too am experiencing this same issue. I currently have just moved up to SQL 2005 and reporting services. I have one job that copies data from my source server using a query and then dumps it into my reporting db. The other job, which is the one causing this error is using the table copy functionality rather than specifying a query. I have created this SSIS package multiple ways and it will run the first time through, but not from the stored version.
February 26, 2007 at 1:24 am
I have the exact same problem as you Michael, it's running the first time, but not when scheduled. Have anyone of you found an explanation? I assume it's some permission failure, but I can't figure out where...
February 27, 2007 at 9:39 am
Do you know what context the package(s) are running under? In the first post in this thread, seeing a directory path that has 'Administrator.CBI' normally would indicate that it's using or looking for a path that belongs to the Administrator account (where Administrator is the domain admin not the machine admin, unless CBI is the name of the machine - i've assumed it's the domain name).
I would also check the Event Log(s) to see what errors are thrown there. If anything these should show the user context within which the package is attempting to execute. With reference to that path again, that path would be inaccessible to any user who isn't an Admin on the machine as it's user specific and likely to be secured.
Steve.
February 27, 2007 at 7:23 pm
I had this problem before. On my case, I didn't put any credential in Sql server 2005 database when running the jobs. After i created one for the job. it was running well. Hope it can help you.
February 27, 2007 at 11:39 pm
The job is executed as local administrator. It succeeds the first step just running a simple UPDATE sql query, but fails when trying to run an SSIS package.
Budiman: Should I create a credential? To what identity should that be bound? And where do I use it?
March 2, 2007 at 5:22 am
I still can't get it to work. I tried all tips I've found. Made a proxy, tried different users, tried different "encrypt with password" and "server storage" when creating the package. (That is when I run the import wizard to fetch the tables I want to import.) Anyone have other ideas of what I might have forgotten!!
March 2, 2007 at 7:11 am
Jonas,
Can you post the error message(s) from the Event Log and/or SQL logs? Also a brief description of what the package looks like would make it easier for others to comment on possible solutions.
Cheers,
Steve.
March 2, 2007 at 8:25 am
Thanks, but now I got it to work. I'm not sure what made the major difference, but I suppose it's the right combination of a lot of things. This is how my SSIS scheduled job works right now:
* Proxy running the job, Credential bound to user LOCAL/Admin.
* SSIS package with "Encrypt sensitive data with password" instead of "Encrypt sensitive data with user key".
* Login with sql user do not work for me, don't know why. But it now works with "Windows Authentication".
This is what was obvious parts of my problem.
Thanks all!
March 2, 2007 at 10:22 am
(* Proxy running the job, Credential bound to user LOCAL/Admin.
* SSIS package with "Encrypt sensitive data with password" instead of "Encrypt sensitive data with user key".
* Login with sql user do not work for me, don't know why. But it now works with "Windows Authentication".)
All of the above is context and automation related it is not complicated but Microsoft may not document it because they will have to explain to users.
Kind regards,
Gift Peddie
March 2, 2007 at 10:30 am
One thing that jumps at me out of that is the change from encrypting with the user key. If the info is/was encrypted with a user key but then a different user was executing the package then I would expect something to fail. Am probably biased but i think this supports this.
Steve.
March 2, 2007 at 11:11 am
Yep the automation subsystem depends on the Agent running with decent permissions but to explain how all work together will take sometime so Microsoft choose not to document it or document it haphazardly.
Kind regards,
Gift Peddie
March 4, 2007 at 11:39 pm
Is this documented somewhere else? Since Microsoft has a really poor documentation, someone else parhaps have something better! I'm still not sure of how to do things the right way, and what I can see in forums around I'm not the only one.
March 6, 2007 at 10:15 am
I would not say Microsoft docs in SQL Server is not good because I use Oracle and SQL Server together albeit as a developer. To why the automation and ETL subsystem correct configuration is not documented because I think the SQL Server team probably don't have that information, I think it is Microsoft consulting that uses that for customers or you meet people like me online who can give you what you need to get the system going. You need xp_cmdshell enabled and clone an admin level permissions to run the Agent and I don't think you need to encrypt anything because banks use it to move DB2 deposits to SQL Server based bank at home with DTS and the stuff works fine using the correct context account. I helped the Project server team at a previous employer collect and deliver 3000 Excel files a week to be delivered to specific databases in a 300 database server. There were minor permissions issues but before I could walk down to their section of the building it was up and running it is that easy. Hope this helps.
http://support.microsoft.com/kb/890775
Kind regards,
Gift Peddie
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply