Dynamically creating SQL Server Report

I've been working with Crystal Reports for a couple of months and I'm now interested in creating a SQL Server Report but need assistance. With CR I'm able to point a report to a dataset within the application and then modify the dataset by passing parameters from the web page that hosts the crystalreportviewer. The CR then reflects the updated dataset. I'm looking to create the same functionality with SQL Server Reports but I'm unfamiliar. Below is the code I use for one of my reports called 'Holds'. Any assistance is appreciated!

Holds.aspx

<form id="form1" runat="server">
<div class="rptParameters">
<table cellpadding="0" cellspacing="0" border="0">
<tr align="left" valign="middle">
<td>
Season:
</td>
<td>
<asp:DropDownList ID="ddlSeason" runat="server" AutoPostBack="true" OnSelectedIndexChanged="ddlSeason_SelectedIndexChanged">
<asp:ListItem Value="" Text=""></asp:ListItem>
<asp:ListItem Value="MC0607" Text="MC0607"></asp:ListItem>
<asp:ListItem Value="MC0708" Text="MC0708"></asp:ListItem>
</asp:DropDownList>
</td>
</tr>
</table>
</div>
<asp:Panel ID="rptPanel" runat="server" CssClass="rptViewer" Visible="false">
<CR:CrystalReportViewer ID="CrystalReportViewer1" runat="server" AutoDataBind="True"
HasCrystalLogo="False" EnableDrillDown="False" />
</asp:Panel>
</form>

Holds.aspx.cs

using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections;
using System.Text;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using CrystalDecisions.Shared;
using CrystalDecisions.CrystalReports.Engine;
using CrystalDecisions.Web;
public partialclass Holds : System.Web.UI.Page
{
private ReportDocument myReport;
// Handles initial page loadprotected void Page_Load(object sender, EventArgs e)
 {
if (!IsPostBack)
 {
 Session["Season"] =null;
 }
if (Session["Season"] !=null)
 {
 ConfigureCrystalReports();
 }
 }
// Handles configuring crystal reportsprivate void ConfigureCrystalReports()
 {
 myReport =new ReportDocument();
string reportPath = Server.MapPath("reports/Holds.rpt");
 myReport.Load(reportPath);
 DataSet dataSet = DataSetConfiguration.Holds;
 myReport.SetDataSource(dataSet);
 CrystalReportViewer1.ReportSource = myReport;
 }
// Handles season dropdownlist selectedIndexChangedprotected void ddlSeason_SelectedIndexChanged(object sender, EventArgs e)
 {
if (ddlSeason.SelectedIndex == 0)
 {
 Session["Season"] =null;
 }
else
 {
 Session["Season"] = ddlSeason.SelectedValue;
 ConfigureCrystalReports();
 }
 }
}

DataSetConfiguration.cs

using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
/// <summary>
/// Summary description for DataSetConfiguration
/// </summary>public class DataSetConfiguration
{
public DataSetConfiguration()
 {
//
 // TODO: Add constructor logic here
 // }// Handles creating holds datasetpublic static DataSet Holds
 {
get {// get parametersstring SEASON = System.Web.HttpContext.Current.Session["Season"].ToString();
string TK_EVENT_STATUS ="TK_EVENT_" + SEASON +"_STATUS";
string TK_EVENT ="TK_EVENT_" + SEASON;
 DataSet dataSet =new DataSet();
string query ="SELECT TOP 100 PERCENT A.[STATUS], '" + SEASON +"' AS [SEASON], C.NAME AS [DESC]," +
" A.[Z_ID] AS [EVENT], A.[NSTATUS], B.[NAME], B.[DATE], B.[TIME]" +
" FROM " + TK_EVENT_STATUS +" A LEFT OUTER JOIN" +
" " + TK_EVENT +" B ON A.Z_ID = B.Z_ID LEFT OUTER JOIN" +
" TK_SEAT_STATUS C ON A.STATUS = C.STATUS COLLATE SQL_Latin1_General_CP1_CS_AS" +
" WHERE (A.Z_ID NOT LIKE '%P') AND (A.Z_ID NOT LIKE 'W%')" +
" AND (A.STATUS <> 'O') ORDER BY B.[DATE], B.[TIME], B.[NAME]";
 dataSet.ReadXmlSchema(@."D:\Web Sites\Holds\App_Code\HoldsDataSet.xsd"); SqlConnection conn =new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnectionString"].ConnectionString);
 SqlDataAdapter cmd =new SqlDataAdapter(query, conn);
 cmd.Fill(dataSet,"Holds");
return dataSet;
 }
 }
}

Thanks!

I created a solution for my question. Now I have a SQL Server Report(.rdlc) displaying the data.

Holds.aspx

<form id="form1" runat="server">
<div class="rptParameters">
<table cellpadding="0" cellspacing="0" border="0">
<tr align="left" valign="middle">
<td>
Season:
</td>
<td>
<asp:DropDownList ID="ddlSeason" runat="server" AutoPostBack="true" OnSelectedIndexChanged="ddlSeason_SelectedIndexChanged">
<asp:ListItem Value="" Text=""></asp:ListItem>
<asp:ListItem Value="MC0607" Text="MC0607"></asp:ListItem>
<asp:ListItem Value="MC0708" Text="MC0708"></asp:ListItem>
</asp:DropDownList>
</td>
</tr>
</table>
</div>
<div>
<rsweb:ReportViewer ID="ReportViewer1" runat="server" Font-Names="Verdana" Font-Size="8pt" Height="400px" Width="400px">
</rsweb:ReportViewer>
</div>
</form>

Holds.aspx.cs

using System;using System.Data;using System.Data.SqlClient;using System.Configuration;using System.Collections;using System.Text;using System.Web;using System.Web.Security;using System.Web.UI;using System.Web.UI.WebControls;using System.Web.UI.WebControls.WebParts;using System.Web.UI.HtmlControls;using Microsoft.Reporting.WebForms;public partialclass Test : System.Web.UI.Page{// Handles initial page loadprotected void Page_Load(object sender, EventArgs e) {if (!IsPostBack) { Session["Season"] =null; } }protected void ConfigureReports() { ReportViewer1.LocalReport.ReportPath = Server.MapPath("reports/Holds.rdlc"); DataSet dataSet = DataSetConfiguration.Holds; ReportDataSource ds =new ReportDataSource("HoldsDataSet_Holds", dataSet.Tables[0]); ReportViewer1.LocalReport.DataSources.Clear(); ReportViewer1.LocalReport.DataSources.Add(ds); ReportViewer1.LocalReport.Refresh(); }// Handles season dropdownlist selectedIndexChangedprotected void ddlSeason_SelectedIndexChanged(object sender, EventArgs e) {if (ddlSeason.SelectedIndex == 0) { Session["Season"] =null; }else { Session["Season"] = ddlSeason.SelectedValue; ConfigureReports(); } } }

DataSetConfiguration.cs is the same.

FYI I found the information I needed at http://www.codeproject.com/aspnet/ReportViewer.asp

Dynamically create an SSIS Bulk Insert Package

I am looking high and low for some assistance with developing a VB .NET solution that I programmatically create a package and add tasks. I am adding a BULK INSERT task to load large FLAT TEXT files into SQL Server 2005 tables. When I execute the application I execute a package validation and it always returns FAILURE. I have been reading and searching like crazy and I have bought 2 microsoft books, TO NO AVAIL! Can anyone PLEASE help me with this. Thank you!

Cheers~

Do you have any more specific messages other than "FAILURE"?|||

No, unfortunately not. This is a new venture for me and I am not even sure that I am going about the creation process correctly. Quite honestly, I am getting 'pieces' of code from different places off of Microsofts Books Online and sort of putting them together. I have been able to successfully load a package template and execute it successfully, but creating the package from scratch has proven to be quite the challenge. I have found a plethora of samples in C#, but I have not been able to locate a full sample in VB .NET at this point.

The reason I only get a 'FAILURE' return is because I am simply saying DTSExecResult = pkg.execute() and then displaying the return value of the result.

Any suggestions, guidance or direction will be GREATLY appreciated at this point! Thanks a million!

Cheers~

|||Be sure to enable logging: http://technet.microsoft.com/en-us/library/ms136023.aspx

Also know that I'm not going to be much help with the coding. I'm learning on my own as well, but I'm just trying to gather specific information for others who will read this thread.

Also know that at some point you may be asked to share your code.|||

I will gladly share my code! Thank you so much for the replies thus far. I am hoping that I can get some great responses and learn as much as possible in the coming days about this topic. Talk to you soon!

Cheers~

|||Try saving the package (Application.SaveToXML) after creating it. Then open and run the package in BIDS. That makes it a lot easier to see what the problem is.|||

John, I am about to do this. However, what do you mean by running it in BIDS? Thanks.

Cheers~

|||Business Intelligence Development Studio -- or Visual Studio -- which is where you develop SSIS packages via the GUI.|||

OK, what I am seeing now is that the Destination connection is not available, and there are no columns mapped from the input source - (flat file) - to the ole destination table. I am assuming that this is because I am failing to do something correctly in the code to setup the package's BULK INSERT properties.

Are there any tutorials available to understand how to set all the properties for source and destinations, respectively? Any direction you might can provide is awesome!!!!

Again, I am finding bits and pieces here and there, but nothing that says "Hey, do it this way"...

Cheers~

|||

OK, I have made some progress. I now have a Flat FIle source and an OLEDB destination. My delimma at this point is mapping the SOURCE columns from the flat file to the DESTINATION columns of the OLEDB table. The Flat File does not have a header row and it is a pipe "|" delimited file. Any idea on how to map the source / destination columns?

Cheers~

|||

Do you have Output columns in the Flat FIle Source output? Save the package in code (example http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2179817&SiteID=1) and open it to check what it looks like.

If so then you should be able to connect the output to the destination's input, and select your columns. Mapping is a simple method call, but what you map to what is your logic really. In this example I just use index position becuase I "know" that my source and destination formats match.