Execute ssis package with dtexec - sql server logging not writing events to database

0
votes

I am using sql server data tools to create ssis packages to pull data from an AS400 to a sql 2012 express database. I am running windows 7, 64bit. I created the packages and setup logging to a text file and a sql server database within Visual Studio Shell. When running within the visual studio shell the package executes and both the log text file and the sysssislog table are populated. However, when executing the packaging using Windows Scheduler and the dtexec utility, the sql server log table does not populate, but the text file does.

Does anyone know if any of the service accounts in the database need to be given special permissions so that when executing using the dtexec utility the log table sysssislog will be updated? Is it possible that dtexec is using an account that does not have permissions either to write to the log table or execute a stored procedure?

The only argument I am using in Windows Scheduler is /f "\server\filepath\package.dtsx". I set the logging in visual studio. Is it possible that logging also needs to be set up as an argument?

Please let me know if additional information is needed.

1
windowsssisschedulerdtexec
In your dtexec call, add /rep EWI (error, warning, information). I bet you'll see something like "CREATE TABLE permission denied in database X" or something along those lines – billinkc
I did not see anything about that. The log table already exists because it was created when I ran the package in visual studio. It just won't log anything to it when run with dtexec. – Andrew Corson
Fire up SQL Profiler and watch where you think it should be logging. You should see connection and calls to sp_ssis_addlogentry – billinkc
I am running sql express and I just read another post about a similar error. I checked and I guess I don't actually have integration services installed - I have data tools, but that would explain why I am having issues running the package outside of data tools, right? I don't even think that express comes with sql profiler. – Andrew Corson
Yes, not having the integration services service installed (which really boils down to licensing) would prevent the package from executing. I find it curious though that your text error file is populated. – billinkc

1 Answers

0
votes

It turns out the issue was the I do not have SSIS installed on my machine, just the designer (SSDT) which is why logging worked correctly when executing the package in Visual Studio but not when using DTExec utility.