Get SSIS to report failure when running a sqlcmd task

hi,

we have sis package single 'execute process task' executes sqlcmd.exe follows

-s <sqlinstance> -d <dbname> -q <storedproc> -o <outputfilename>

the output csv file containing output of 30 queries each headed query name
, footed blank row.

the stored procedure creates contains 30 queries, each selects hard coded string as
query title followed subsequent select of columns different tables. columns aliased
name followed commas in single quotes give comma separators along lines of -

<columnname> <alias>,', ',', 

the net result single csv file of 30 queries, opened in excel, gives 30 different width
tables 30 queries generate.

this works fine but, on odd occasion 1 of queries encounters invalid character - '?' in column
attempting cast int. in instance, csv file gets created , contain output
error error message itself.

the problem ssis package completes , reports dt_ser_success (0) (probably because file created).

i cannot fathom way ssis report dt_ser_failure (1) required on these occasions

does have idea how can done? 

thanks

this sounds hopeful - output final 'eof' string end of csv in last stored procedure and use script task check this.

do have examples or links on how done?

many thanks,

update

i added final select 30 queries output 'end of file' string end of csv , added script task ssis check string in output file. took me research write the vb for this don't have vb (or c#) in toolbox. but, result works , least intrusive fix overall process.

thanks arthurz idea of using script task- hadn't used 1 before , unaware of how useful component is.

SQL Server  >  SQL Server Integration Services