You haven't said what fails in SSIS nor what works in SSMS. So we can't see why one works and the other fails. Of course SSIS does place restrictions on your T-SQL, depending on what transforms you are using, and I don't know enough about SSIS to be any help on that side anyway.