Sunday, February 19, 2012

Consume DataReaderDest from asp.net page?

I have seen the other posts about how to use Microsoft.SqlServer.Dts.DtsClient to run a package and get back the DataReader results. But this fails when run from a client mahcine that does not have SSIS installed. I want to have this page on a web server run the package on a remote Sql Server machine and get back the results but have so far failed. Any one got this working?


protected void Page_Load(object sender, EventArgs e)
{
string path = @."C:\Documents and Settings\Brandon\My Documents\Visual Studio 2005\Projects\Integration Services Project5\Integration Services Project5\FuzzyLookup.dtsx";

DtsConnection connection = new DtsConnection();
connection.ConnectionString = string.Format(@."-f ""{0}""", path);
connection.Open();

DtsCommand command = new DtsCommand(connection);
command.CommandText = "DataReaderDest";

IDataReader reader = command.ExecuteReader(CommandBehavior.Default);

DataSet set = new DataSet();
set.Load(reader, LoadOption.OverwriteChanges, reader.GetSchemaTable().TableName);

_grid.DataSource = set;
_grid.DataBind();

connection.Close();

}

You have to install the SSIS components on the machine where you are executing the SSIS package (which does require a license of SQL Server).

No comments:

Post a Comment