| Author |
Topic |
|
kflee2000
Starting Member
14 Posts |
 Posted - 2007-06-11 : 05:20:50
|
Hi all, I can run the import/exp through the utility and link to oracle to extract data via OLE DB ORACLE interactively,it run prefectly ok. However, when I try to schedule as a package and jobs, it always give me invalid password/username. Why the username/password not save or somehow it wouldn't accept when run as package or jobs? Anyone can help? Thanks. |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-06-11 : 08:52:11
|
| You can't save the user name and passwodr unless you password protect the package (I guess - never tried it).The usual way to do it is to save the password in a config file.seehttp://www.nigelrivett.net/DTS/SSISConfigurationFile.htmlfor a step by step guide how to do it.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-06-11 : 17:42:09
|
| Seems sql agent service account doesn't have permission to access oracle db, try set proxy account for that. |
|
|
|
kflee2000
Starting Member
14 Posts |
Posted - 2007-06-11 : 20:51:29
|
| Hi, I'm using the administrator account to create a job, do I still need a proxy account? Thanks. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-06-12 : 11:47:50
|
| It's not job owner's permission but sql agent service account's. |
|
|
|
kflee2000
Starting Member
14 Posts |
Posted - 2007-06-13 : 05:21:01
|
| Hi all, is not the proxy account which caused the problem, I found out that in SSIS package if you schedule it wouldn't save the password so need to create a package config file. Another problem which I using the DTS 2000 to convert the DTS to SSIS package (sql2000->sql2005), for exporting package in 2000 it read the result file but for 2005 it can't, therefore given some problem, anyone know this problem. thanks. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-06-13 : 09:58:53
|
| And did you read my first reply here?==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
kflee2000
Starting Member
14 Posts |
Posted - 2007-06-13 : 21:47:14
|
| nr, yes I did, therefore I have slove part of my problems.I still try to figure out how to convert the export pkg from sql2000 to sql2005, the problem lay on the result file which how to convert or represent in sql2005 ?Thanks |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-06-13 : 22:16:31
|
| You mean sql2k5 can't read from data file? |
|
|
|
kflee2000
Starting Member
14 Posts |
Posted - 2007-06-14 : 20:57:46
|
| ok, let me explain. In the package (DTS) my source is from sql server, my desination is to output to a execl file, it work fine in sql2000. When try to convert to sql2005, the execl file potion not working, the desination instead of execl file it become native ole db\ms jet 40 ole db provider and it's not the original that I want.How to define the ouput file to an execl,which the problem that I can't define in the SSIS in sql2005, or anyone know how to solve the problems.Thanks. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-06-14 : 22:42:32
|
| There is excel data source in ssis on my machine, you can't find it on your's? |
|
|
|
kflee2000
Starting Member
14 Posts |
Posted - 2007-06-17 : 22:16:02
|
| problem 1, as I mentioned when convert dts2000 to sql2005 it become ole db\ms jet 40 ole db provider instead of execl data source.problem 2, as I have try directly using wizward to create export in sql2005,I can see the execl data source, but this time I encountered the The AcquireConnection method call to the connection manager "DestinationConnectionExcel" failed with error code 0xC0202009.Thanks for any help |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-06-17 : 22:46:26
|
| 1. Can't change data source back to excel in the package?2. Is excel file on sql server's local disk? Does sql service account have permission to access it? |
|
|
|
kflee2000
Starting Member
14 Posts |
Posted - 2007-06-18 : 20:10:34
|
| Hi,1. yes, can't change it in grey.2. yes, in the server I'm using adminstrator account with full control. |
|
|
|
|
Invalid password/user name