Using SQL Task Results in a Script Task in SSDT

In SSDT if you want to access the results of a SQL query in a script task you can do this by outputting the results as a full result set into a variable of type Object and then load this into a DataTable within your script task. It’s also possible to read records directly from a SQL query in script tasks within a data task using the method here but this doesn’t work in standard script tasks as you’re unable to add connections to them.

Get ResultSet

Save to Variable

Load into DataTable
	
public void Main()
{
	DataTable dataTable = new DataTable();
	OleDbDataAdapter adapter = new OleDbDataAdapter();
	adapter.Fill(dataTable, Dts.Variables["User::processing_emails"].Value);

	foreach (DataRow row in dataTable.Rows)
	{
		// Process rows
	}

	Dts.TaskResult = (int)ScriptResults.Success;
}
	

Leave a Reply

Your email address will not be published. Required fields are marked *