I had the same issue when just pasting in syntax as a SQL job where it runs the syntax as the SQL Agent Service Account, which is not helpful at all. Creating Credentials and Proxy Accounts is WAY too complicated just to run one large insert statement, so I tried creating it in SSIS.
I've added one T-SQL Statement Task and pasted in my large insert statement. This runs fine and inserts rows if I run this in Query Analyzer. If I run the sql job, again, it does nothing. I setup the Connection Manager to use a SQL login that I have setup on other SSIS tasks that insert data, so I don't understand why this doesn't work. I am able to save the password with SSIS as I've updated the DataSource in AGENT and added the password so it's not a password issue. The SQL Login is a sysadmin and it does have permissions in SQL to insert. The syntax itself just does not seem to run. I also tried creating a stored procedure to run the insert but that doesn't run it properly either as a scheduled task.
I'm literally giving up and manually opening my syntax from a text file and manually running it each day. You'd think using SSIS would be more automated than that.