No rows returned in DTS task, but returned when debugging script

I hope I explain this correctly. I have an SSIS Script task which very simply queries a view, fills a datatable with the result and outputs the number of rows that the resultant datatable has.

SqlCommand cmd = new SqlCommand("select .....", conn);
                using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                {
                    da.Fill(dt);
                    output += "Datatable row count " + dt.Rows.Count.ToString() + " \r\n";
                }

In Visual Studio this returns the proper result/rowcount (165 rows). When I save the package and run it as a step in a SQL Server job, it returns a rowcount of 0. (the job runs without errors) Same code, same query, same connection, same credentials only difference is it works when I hit "debug" within the package in Visual Studio but does not when I execute the package as a SQL Server Job step.

Driving me mad! Anyone any ideas? MANY THANKS.

Solution

I'd recommend running via the dtexec command line tool or the dtexecUI to see which behavior it yields. That would help to determine if it's a package issue or job issue. Also, are you running this within a SSIS Script task? If so, SSIS offers a couple of constructs to do this in a more standard (to SSIS) way. For example you could use a OLEDB Source within a DataFlow, then use the RowCount Task for reporting the RowsAffected count that are passed through the pipeline.