I assume you have a bunch of .sql files in a folder that you want to execute. What you can do is use a foreach loop container in SSIS and get the name of file in a variable.

Then inside foreach loop, place an execute sql task with SQLSourcetype as FileConnection. Configure the Connectionstring property of Connection manager of this Fileconnection to read value from variable being used in foreach loop container.

If any of the script fails, package will fail.

Hope this helps !!