I need to import data from a Oracle DB into my SQL 2008 server.
I've created an SSIS package which does this by means of an ODBC connection. Upon creating this package the excecution runs fine and the data is imported.
Now I need this import to be sheduled at 00:00 every day. I've done this with the SQL Server Agent by means of creating a task.
The tasks first drops the 2 tables, by means of a query.
Then the next tasks excecutes the SSIS package which first creates the tables and then copies the data.
However, the SSIS package keeps excecuting. No error is thrown, nothing happends. The log doesn't even show the 2nd tasks (SSIS package running) starting. However it is started because the 2 tables are allready created. Only the data does not get transfered.
I've tried multiple things so far:
-Saving the SSIS package on the SQL server or on the HD (C:\mypackage.dtsx).
-Tried different types of encryption (server handled, none, own password).
-Tried filling the connection string WITH and Without package password in the command window of the SSIS import step.
When running the package with the command /validate the job finished with no errors. However because /validate is used no data is copied.
When I run the package from "Excecute Package Utility" the package runs fine and the data is imported. No errors are shown in the log.
When I run the package from a batch file with this line:
dtexec "/FILE "C:\mypackage.dtsx" /DECRYPT mypassword /CHECKPOINTING OFF /REPORTING EW "
It also runs fine.
When I copy this command in the SQL server Agent JOB the job also keeps running (never ending).
The sqlserveragent has all the privedgles requirent to acces the local database. And the SSIS package has the password of the oracle database inside it. (It works when I run the batch so..)
How is it possible I can NOT run this package with the SQL server agent?
My workaround is running the batch file with windows tasks sheduler. However because now my database password is accessible due to the fact that the batch file contains the SSIS password (which then contains the database password) I have kind of an security leek.
Does anyone know how to solve this?