March 22, 2006 at 5:10 am
ok i admit i struggled to creat a SSIS package in the SQL Server Business Intelligence Development Studio, but after a few days of pulling hair it finally came thru. I used the SSIS Import & Eport Wizard to copy data from our old SQL2000 server into the brand spanking shinny new SQL2005 server. Imagine how excited i was to actually get it working, as its aiding in the transfer from our old systems onto out new ones. I got really clever then and scheduled this as a SQL Agent job in the 2005 server, which after testing several times at different intervals worked. I have come in this morning and found that the task has failed. the Job history is less than helpful as you can see below
03/22/2006 01:00:00,Copy BacsSys,Error,0,SQL1,Copy BacsSys,(Job outcome),,The job failed. The Job was invoked by Schedule 3 (NightMove). The last step to run was step 1 (210306 BACSSYS Copy).,00:00:01,0,0,,,,0
03/22/2006 01:00:00,Copy BacsSys,Error,1,SQL1,Copy BacsSys,210306 BACSSYS Copy,,Executed as user: CBI\SQL. The package execution failed. The step failed.,00:00:01,0,0,,,,0
As you can see this gave me a great insite as to what the problem is, so i reopened the ssis in the SQL Server Business Intelligence Development Studio and tried to run it again there, it failed with the following 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\3\tmp60.tmp'.".
Task failed: NonTransactableSql
SSIS package "Package3.dtsx" finished: Failure.
Im at a total loss as who what why and when.... can anyone point me in the simple instructions way to resolve this ?
Thansk in advance
March 23, 2006 at 9:46 am
That's a very strange error. I'm afraid I can't help with that.
I did want to point out this very important article regarding the execution of packages from SQL Agent though: http://wiki.sqlis.com/default.aspx/SQLISWiki/ScheduledPackages.html
-Jamie
Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson
January 11, 2007 at 1:29 am
I have nearly the same problem. I made 1 SSIS-package from the Server Import and Export Wizard. I added 1 SQL-task to the package in BIDS. One thing that seems strange for me is the flat files in the connection manager. The connection for the first task (NonTransactableSQL) is located in "C:\Documents and Settings\kjetil\Local Settings\Temp\tmp2CA.tmp". The package run successfully both on its own and as a step in a job. The problem is when I log out, then the package (as step in the job) will not run successfully. It does neither run successfully after I log in again because the temp files then are removed.
How can this be solved?
/Kjetil
January 11, 2007 at 6:56 am
It seems that the sollution was to copy the files to a permanent folder and then replace the file locations for each SQL file in the connection manager. Main problem was InnerPackage.dtsx that seemed to be created and written to under the process. I am new in using BIDS, and it seemed that problem was that BIDS used the file under the debug. After importing package with new connections, I was able to run the job successfully.
What I have some questions about is how the SSIS Proxy is working. Right now the job run as SQL Agent Service Account (Local administrator). The job failed when I tried to set up a proxy with SSIS Package executing using credential with what I think is sufficient rights.
/Kjetil
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply