1 / 28

Using .NET with the MS BI Stack

Using .NET with the MS BI Stack. Kevin S. Goff. Kevin S. Goff: 30 seconds of Shameless Promotion. Developer/architect since 1987 Microsoft SQL Server MVP 2011 Columnist for CoDe Magazine Wrote a book, collaborated on a 2 nd book

rooney-delaney
Download Presentation

Using .NET with the MS BI Stack

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Using .NET with the MS BI Stack Kevin S. Goff

  2. Kevin S. Goff: 30 seconds of Shameless Promotion • Developer/architect since 1987 • Microsoft SQL Server MVP 2011 • Columnist for CoDe Magazine Wrote a book, collaborated on a 2nd book • Currently writing a 3rd book on Business Intelligence with SQL Server • Frequent speaker for Database/SharePoint User Group/.NET community events • kgoff@kevinsgoff.net • www.KevinSGoff.Net • Photo Gallery for my daughter! Using .NET with MS BI Stack

  3. .NET Code in the BI Stack • We’ll look at a set of miscellaneous scenarios where .NET (C#) and the BI stack come together • No general theme, just miscellaneous examples • Examples themselves are basic, won’t win any awards, but helpful to .NET developers using the SQL/BI tools Using .NET with MS BI Stack

  4. The Agenda – .NET Code in the BI Stack • C# scripts inside of SQL Server Integration Services • C# web service to get Currency conversion rates from a web service, inside an SSIS application • Sending an email using Gmail from inside SSIS, when the built-in SSIS email task doesn’t work • Incrementing and accumulative SSIS variables in a basic SSIS package • Generating an SSRS report to a PDF inside an SSIS package • Visual Studio.NET 2010 examples • Putting an SSRS report on an ASP.NET webpage • C# code to post geospatial information from a web service to a geography data type • Using C# code to execute an SSIS Package • Accessing OLAP data with an MDX query from C# • Passing an ADO.NET DataTable to a SQL Server Stored Procedure using the SQL Server 2008 TableType Using .NET with MS BI Stack

  5. Demo 1 – Calling a web service from SSIS • We want to call a web service to get Currency conversion rates for specific currency codes, inside an SSIS application Using .NET with MS BI Stack

  6. Demo 1 – Calling a web service from SSIS Must tell script component about the input columns from the data flow pipeline, as well as new output column (ConversionRate) Using .NET with MS BI Stack

  7. Demo 1 – Calling a web service from SSIS Then inside the script component, must add a web reference, and provide a reference name (“ConvertorWebReference”) Using .NET with MS BI Stack

  8. Demo 1 – Calling a web service from SSIS For the new web reference, View in Object Explorer to get the full name, and then copy/paste the name to use the full reference Using .NET with MS BI Stack

  9. Demo 1 – Calling a web service from SSIS • Finally, inside the script code, we can tap into the method ProcessInputRow, which exposes a typed object called Row. • We can call the web service method (ConversionRate). Note that we have to cast the Currency Codes to the type called Currency (requirement of the web service) public override void Input0_ProcessInputRow(Input0Buffer Row) { string FromCurrencyCode = Row.FromCurrencyCode.ToString(); // from pipeline data string ToCurrencyCode = Row.ToCurrencyCode.ToString(); // from pipeline data CurrencyConvertorMyExRate = new CurrencyConvertor(); double conversionRate = MyExRate.ConversionRate( (Currency)Enum.Parse(typeof(Currency), FromCurrencyCode), (Currency)Enum.Parse(typeof(Currency), ToCurrencyCode)); Row.ConversionRate= (decimal)conversionRate; // bring the rate back into the pipeline } Using .NET with MS BI Stack

  10. Demo 2 – sending email in SSIS with a C# script • SSIS has a Send Mail Task • Great for Exchange Servers (and when not using a custom SMTP) • Not so great when special credentials needed for sending (GMAIL) • Example creates variables for sender/recipient/server, and then uses C# script to create credential object Using .NET with MS BI Stack

  11. Demo 2 – sending email in SSIS with a C# script • Script code – note use of SSIS variable collection string EmailSender = (string)Dts.Variables["EmailSender"].Value; string EmailRecipient = (string)Dts.Variables["EmailRecipient"].Value; string EmailServer = (string)Dts.Variables["EmailServer"].Value; string EmailBodyMessage = "This is line 1" + "\r\n" + "This is line 2"; Attachment myAttachment = new Attachment("c:\\emplist.csv"); MailMessagemyHtmlMessage= new MailMessage(EmailSender, EmailRecipient,"Results", EmailBodyMessage); SmtpClientmySmtpClient = new SmtpClient(EmailServer, 587); mySmtpClient.EnableSsl = true System.Net.NetworkCredential Credentials = new NetworkCredential(EmailSender, "ABCdef123!"); mySmtpClient.UseDefaultCredentials = false; mySmtpClient.Credentials = Credentials; myHtmlMessage.Attachments.Add(myAttachment);mySmtpClient.Send(myHtmlMessage); Dts.TaskResult = (int)ScriptResults.Success; Using .NET with MS BI Stack

  12. Demo 3 – Incrementing/Accumulating SSIS variables • In SSIS, if we want to accumulate variable values (from a loop) into a “totalcount” variable, we need to use an SSIS script • We want to loop through X number of files in a folder, open each file, count the # of rows, and accumulate each file row count to a grand total row count. • SSIS doesn’t provide a built-in component for handling simple variable calculations, and the row count component isn’t additive – so we need to write a basic script to accumulate RowsProcessedTotalRows Using .NET with MS BI Stack

  13. Demo 3 – Incrementing/Accumulating SSIS variables • In the script task editor, need to define the variables that the script will read, and the variables that the script will modify. • The script references the variables through the Dts.Variables collection • The next release of SQL Server (SSIS 2012) will have a component for this – and a script will not be necessary Dts.Variables["NumberFilesProcessed"].Value = (int)Dts.Variables["NumberFilesProcessed"].Value + 1; Dts.Variables["RowsProcessedTotalRows"].Value = (int)Dts.Variables["RowsProcessedTotalRows"].Value + (int)Dts.Variables["RowsProcessedSingleFile"].Value; Using .NET with MS BI Stack

  14. Demo 4 – Generating an SSRS report inside SSIS • Suppose we want to run an SSRS report automatically to a PDF – perhaps only if the SSIS package discovered new data during an ETL Load • Also, the SSRS report might use some parameters based on values from the SSIS variables – so we can pass package-level variables to the SSIS script Using .NET with MS BI Stack

  15. Demo 4 – Generating an SSRS report inside SSIS • References: Microsoft.ReportViewer.Common, Microsoft.ReportViewer.WebForms, System.Web • Using Microsoft.Reporting.WebForms, System.Collections.Generic, and System.IO; ReportViewerrViewer = new ReportViewer(); rViewer.ServerReport.ReportServerUrl= new Uri(@"http://kevin-asuspc/ReportServer_SQL2008R2"); List<ReportParameter> ParmList = new List<ReportParameter>(); string Year = Dts.Variables["YearToRun"].Value.ToString(); ParmList.Add(new ReportParameter("CalendarYear", Year)); rViewer.ServerReport.ReportPath= "/SSRS2008R2ReportGallery/rptPerformanceGaugeAndSparkline"; rViewer.ServerReport.SetParameters(ParmList); string mimeType, encoding, extension; string[] streamids, Warning[] warnings; byte[] bytes = rViewer.ServerReport.Render("PDF", null, out mimeType, out encoding, out extension, out streamids, out warnings); using (FileStream stream = File.Create(@"C:\ReportOutput\TestOutput.PDF")) { stream.Write(bytes, 0, bytes.Length); } Using .NET with MS BI Stack

  16. Demo 5 – Running a report in an ASP.NET webpage • Suppose we need to retrieving latitude/longitude points for addresses – to store in a Geography Data Type in SQL Server, for SSRS Map reporting? Using .NET with MS BI Stack

  17. Demo 5 – Putting an SSRS report on a .NET page • First, drop an instance of the ReportViewer control onto a web page • Must add using statements for Microsoft.Reporting.WebForms and Systems.Collections.Generic • Code below creates a URI from the SSRS Web Service URL, sets properties for the SSRS report viewer, and creates a Parameter List for any report parameters string ReportPath = "/SSRS2008R2Maps/rptMapFavoriteRestaurants"; ReportViewer.ServerReport.ReportServerUrl=new System.Uri("http://kevin-asuspc/ReportServer_SQL2008R2"); ReportViewer.ServerReport.ReportPath = ReportPath; ReportViewer.ShowParameterPrompts = false; ReportViewer.ShowFindControls = false; ReportViewer.ShowPrintButton = false; ReportViewer.ProcessingMode = Microsoft.Reporting.WebForms.ProcessingMode.Remote; List<ReportParameter> reportParmList = new List<ReportParameter>(); reportParmList.Add(new ReportParameter("Zoom", this.txtZoom.Text)); reportParmList.Add(new ReportParameter("MoveRight", this.txtLeftRightPlaceHolder.Text)); reportParmList.Add(new ReportParameter("MoveUp", this.txtUpDownPlaceHolder.Text)); ReportViewer.ServerReport.SetParameters(reportParmList); ReportViewer.Visible = true; Using .NET with MS BI Stack

  18. Demo 6 – Retrieving Geospatial Data for SSRS • We can populate a SQL Server table with geospatial codes – but how to retrieve these? We can use a web service!!! Using .NET with MS BI Stack

  19. Demo 6 – Retrieving Geospatial Data for SSRS • Many public web services that receive address data and return Latitude / Longitude points • http://rpc.geocoder.us/service/rest?address={6700 Powers Ferry Rd NW}, {Atlanta}, {GA} • But how can we call the web service, collect the latitude and longitude, and convert them to a geospatial code that SQL Server can use? Using .NET with MS BI Stack

  20. Demo 6 – Retrieving Geospatial Data for SSRS • Inside a .NET application, we can do the following: • Prompt the user for a Street, City, and State • Create an instance of an XDocument and pass the formatted URL containing the web service as a parameter • Return the value from the Xdocument Load and place it into an anonymous type • Use LINQ to XML to retrieve the first instance of the Latitude (“lat”) and Longitude (“long”), and place the values into scalar variables. • Next step will be to convert the Latitude and Longitude to a geospatial code varurl = string.Format ("http://rpc.geocoder.us/service/rest?address={0},{1},{2}", street, city, state); vargeocoderXmlDoc = XDocument.Load(url); varlatitude = (from node in geocoderXmlDoc.Descendants() where node.Name.LocalName == "lat" select node).First().Value; varlongitude = (from node in geocoderXmlDoc.Descendants() where node.Name.LocalName == "long" select node).First().Value; Using .NET with MS BI Stack

  21. Demo 6 – Retrieving Geospatial Data for SSRS • Finally, we can call a stored procedure that will receive the Latitude and Longitude values from the application, and write out a Geospatial value using the Geography class STPointFromText: • geography::STPointFromText( 'POINT(-84.396227 33.873203)', 4326) CREATE PROCEDURE [dbo].[AddAddress] @Restaurant varchar(100), @Address varchar(100), @City varchar(50), @State varchar(50), @longitude float, @latitude float as begin insert into dbo.MyAddresses (Address, City, State, GeoData) values (@Address, @City, @State, geography::STPointFromText( 'POINT(' + CAST(@longitude AS VARCHAR(20)) + ' ' + CAST(@latitude AS VARCHAR(20)) + ')', 4326) ) Returns geographic instance (4326 represents Open Geospatial Consortium Reference ID) Using .NET with MS BI Stack

  22. Demo 7 – Accessing OLAP data from C# using MDX • For querying cube using MDX, can use an OLE DB driver (System.Data.OleDb) OleDbConnectionoConn = new OleDbConnection(); oConn.ConnectionString = "provider=msolap;Data Source=localhost\\sql2008r2;initial catalog=Adventure Works DW 2008R2;"; OleDbCommandoCmd = new OleDbCommand(); oCmd.Connection = oConn; oCmd.CommandText = "SELECT [Internet Sales Amount] ON COLUMNS, "; oCmd.CommandText+= " [Customer].[Country].Members ON ROWS "; oCmd.CommandText += " FROM [Adventure Works] "; DataSetdsCube = new DataSet(); OleDbDataAdapteroDA= new OleDbDataAdapter(oCmd.CommandText, oConn); oDA.Fill(dsCube, "CubeData"); Using .NET with MS BI Stack

  23. Demo 7 – Accessing OLAP data from C# using MDX • To discover OLAP structure and process the cube, must use AdomdConnection • Must add reference to C:\Program Files (x86)\Microsoft.NET\ADOMD.NET\100\Microsoft.AnalysisServices.AdomdClient.dll and also System.Web.Services • Must add using Microsoft.AnalysisServices.AdomdClient; • Must set Target Framework to .NET Framework 4 (not Framework 4 Client Profile) AdomdConnectionoConn= new AdomdConnection ("Data Source=localhost\\sql2008r2;initial catalog=Adventure Works DW 2008R2"); oConn.Open(); DataTabledtCubeInfo = new DataTable(); dtCubeInfo.Columns.Add("Cube", typeof(System.String));dtCubeInfo.Columns.Add("Dimension", typeof(System.String)); dtCubeInfo.Columns.Add("Hierarchy", typeof(System.String));dtCubeInfo.Columns.Add("Level", typeof(System.String)); foreach(CubeDefoCube in oConn.Cubes)foreach (Dimension oDimension in oCube.Dimensions)foreach (Hierarchy oHierarchy in oDimension.Hierarchies) foreach (Level oLevel in oHierarchy.Levels)dtCubeInfo.Rows.Add(oCube.Name, oDimension.Name, oHierarchy.Name, oLevel.Name); Using .NET with MS BI Stack

  24. Demo 8 – Calling an SSIS Package from .NET • To execute SSIS packages from .NET… • Must add reference to C:\Program Files (x86)\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SQLServer.ManagedDTS.dll • Must add using Microsoft.SqlServer.Dts.Runtime; string MyPackage = @"C:\BIStackWith.NET\SSISWith.NET\AccumulateVariables"; Microsoft.SqlServer.Dts.Runtime.ApplicationoApp = new Microsoft.SqlServer.Dts.Runtime.Application(); Package oPackage = oApp.LoadPackage(MyPackage + ".DTSX", null); // oPackage.ImportConfigurationFile (MyPackage + ".dtsConfig"); for XML configs Variables oVars = oPackage.Variables; oVars["LocalDownloadFolder"].Value = @"c:\FTPFiles2"; DTSExecResultoResult = oPackage.Execute();MessageBox.Show(oResult.ToString()); // to execute a package that's deployed to SQL Server..// oApp.LoadFromSqlServer (MyPackage, "localhost", "UserID", "PW", null); Using .NET with MS BI Stack

  25. Demo 9 – Passing an ADO.NET datatable to a stored procedure • Scenario – want to allow a user to select multiple vendors • Want to pass the selected vendors as a datatable to a stored procedure • Want the stored procedure to query the order table for the selected vendors , and return the orders • In prior years, had to create a CSV list or XML string and pass it as a parameter, and then have a stored procedure or TVF “shred” the string into a table variable Using .NET with MS BI Stack

  26. Demo 8 – Passing an ADO.NET datatable to a stored procedure • Step 1: Must create a TableType in SQL Server 2008 • Set in the Programmability/Types/User-Defined Table Types area… Using .NET with MS BI Stack

  27. Demo 8 – Passing an ADO.NET datatable to a stored procedure • Step 2: Create a stored procedure that receives an instance of the table type as a READONLY parameter Using .NET with MS BI Stack

  28. Demo 8 – Passing an ADO.NET datatable to a stored procedure • Step 3: you can create an ADO.NET data table • Give it the same table name as the Table Type Name • Pass the data table as a parameter to the procedure Using .NET with MS BI Stack

More Related