September 2007 - Posts

• T-SQL GO and execution loop

  An old dog is never to old to learn new tricks, well that what I was thinking is after I read Andras blog.  The T-SQL GO Statement is used as an end-of-batch statement.  However if you enter it with a integer parameter it executes the batch several times. Example: PRINT 'Hello World!' GO 5 Results in: Beginning execution loop Hello World! Hello World! Hello World! Hello World! Hello World! Batch execution completed 5 times. Uses Inserting dummy data Clearing hierarchical data Posted Sep 26 2007, 12:00 PM by Brett with no comments Filed under: SQL Server

• Course WSS3DEV: Development for Windows SharePoint Services 3.0

  Course WSS3DEV: Five days; Instructor Lead Course Summary SharePoint technologies provide an effective solution to creating business portal. This course will help learners design and develop custom business solutions for Windows SharePoint Services 3.0 (WSS). The course will teach the learner to create, debug and deploy the fundamental building blocks such as Features, Site Definitions, Page Templates, Web Parts, List Schemas, Content Types, Event Handlers and Workflow Templates. Additionally, the learner will learn to extend out-of-the box functionality that is included with Microsoft Office SharePoint Server 2007 (MOSS). Audience This course is intended for experienced web application developers who intend creating customized Windows SharePoint Services. At course completion After course completion, the learner will be able to Develop and deploy Features Site definitions Page templates List Schemas Content Types Event Handlers Work with the WSS object model Build customized workflows Develop Web Parts Implement AJAX in Web Parts Implement authentication and authorization within components Prerequisites This course is intended for experienced developers with a good understanding of web development A strong working experience with ASP.Net development Server side control development Strong development experience with C#, VB.NET and the .NET 2.0 Framework ASP.Net AJAX Understanding a working understanding of Windows SharePoint Services and Microsoft Office SharePoint Server (MOSS) In addition to the prerequisites, it is highly recommended that the learner completes the following courses prior to attending this course 2543 2544 AJAX 5060 5061 Course Outline Module 1: Getting Started Site Provisioning Site Customization WSS and a development platform Creating your first feature Summary Module 2: SharePoint architecture IIS and ASP.NET 2.0 Primer ASP.NET 2.0 Framework WSS Integration with ASP.NET Creating custom application pages Summary Module 3: Page and Design Site Page Fundamentals Designing site pages using controls Master pages Branding WSS Sties by using CSS files Summary Module 4: Web Parts Introduction Fundamentals Control basics SharePoint development versus ASP.NET Creating a feature for importing web parts Debugging Web Parts Building custom editors Web Part Connections Summary Module 5: AJAX Web Parts Introduction Building Rich Internet Applications with ASP.NET AJAX Building AJAX Web Part Parts for WSS Summary Module 6: List and Content Types Lists and Content Types Event Receivers Summary Module 7: Document Libraries Working with document libraries Forms libraries and Microsoft Office InfoPath Office Open XML file formats Summary Module 8: SharePoint Workflows Windows Workflow Foundation SharePoint Workflows Developing Custom Workflow Templates Integrating Workflow Input Forms Summary Module 9: Solutions and Deployment Introduction Site Definitions Application Deployment through features WSS Globalization and Localization Deployment using Solution Packages Language Packs Summary Module 10: Application Security Introduction Trust Levels and Code Access Security Authentication Authorization Identities User Impersonation Summary Scheduling For additional information and course scheduling please email me at info [at] theshack [dot] co [dot] za Posted Sep 16 2007, 12:31 AM by Brett with no comments Filed under: Courses

• Course AJAX: Web Application development using ASP.NET AJAX

  Course AJAX: Three days; Instructor Led Course summary This 3 day instructor-led course provides learners with the knowledge and skills to implement ASP.NET Ajax into their web applications. The course focuses on the architecture, mechanisms and usage of ASP.NET Ajax and how it can be practically used within an organisation. Audience The course is intended for web application developers who wish to create richer and more responsive web application within ASP.NET. At course completion After completing the course, learners will be able to: Define what ASP.NET AJAX can deliver and identify where it could be implemented. Describe the ASP.NET AJAX Internals Implement ASP.NET AJAX Server controls Debug an ASP.NET AJAX site Implement ASP.NET AJAX controls in master pages Develop custom ASP.NET server controls that implement ASP.NET AJAX. Use ASP.NET AJAX Toolkit controls Prerequisites Learners attending the Application development using ASP.NET AJAX course need to meet the following prerequisites Working knowledge of XHTML Cascading Style Sheets Working experience with JavaScript XML A strong working knowledge of ASP.NET Working knowledge of C# or Visual Basic Course Outline Module 1: ASP.NET Review The first module is to refresh learners on the ASP.NET features that will be used through the course. These core fundamentals are important for full understanding of the ASP.NET technologies. Server Controls View State ASP.NET Postbacks Page Lifecycle User Controls Custom Controls Master Pages HTTP Request Pipeline Custom HttpModules and HttpHandlers Summary of XHTML, CSS, JavaScript, DOM and DHTML Module 2: AJAX Internals This module identifies what Ajax is and where it is used and how to obtain and install it. The module then explains underlying techniques on which AJAX is built. ASP.NET AJAX Installing ASP.NET Ajax Page Postbacks versus Partial Page Updates The HmlHttpRequest Object Registering Client Script Blocks ICallbackEventHandler Interface and Client Callbacks Module 3: ASP.NET AJAX Server Controls This module introduces the learner on how to use server controls that necessary for AJAX usage and how to perform common AJAX calls. ScriptManager UpdatePanel Intrinsic Events UpdateMode Triggers Collection Timer UpdateProgress ScriptManagerProxy Module 4: ASP.NET AJAX Debugging and Tracing The module teaches the learner how to debug ASP.NET applications both on the server and on the browser. Configuring the Web App for Debugging Server-side Debugging and Tracing Client-side Debugging Configuring Internet Explorer for Debugging Attaching Visual Studio Internet Explorer Module 5: Working with AJAX and Nested Server Components The module focus on how to use AJAX on master pages, through user and custom controls Master Pages and AJAX User Controls and AJAX Custom Controls and AJAX Module 6: Client-Side Network Callbacks with ASP.NET AJAX In this module the learner will learn how to expose methods from a web service that will be called from a client. Asynchronous Communication Layer Overview Exposing Web Services to Client Script in ASP.NET AJAX Calling Web Services from Client Script in ASP.NET AJAX Module 7: ASP.NET AJAX Control Toolkit Controls AJAX provides a set of controls in the AJAX toolkit. This module will briefly introduce the different controls to the learner. Accordion Control AlwaysVisible Extender Calendar Control CascadingDropDown Extender Collapsible Panel Extender Draggable Panel Extender DropShadow Extender DyanicPopulate Extender FilteredTextbox Extender HoverMenu Extender ModalPopup Extender PasswordStrength Extender PopUp Extender RoundedCorners Extender TextBox Watermark Extender ToggleButton Extender Module 8: Custom AJAX Development This module will teach the learner client control behaviours and localisation and then focus on server side controls. Custom Server Control Client Behaviours Localised JavaScript Resource Assembly Custom ASP.NET AJAX Non-Visual Client Components Custom ASP.NET AJAX Server Control Custom ASP.NET AJAX Client Controls Scheduling For more information please e-mail me at info [at] theshack [dot] co [dot] za  Posted Sep 16 2007, 12:24 AM by Brett with no comments Filed under: Courses

• Open XML SDK announced

  The 2007 Microsoft® Office system introduces a new file format that is based on XML called Open XML Formats. Microsoft Office Word 2007, Microsoft Office Excel® 2007, and Microsoft Office PowerPoint® 2007 all use these formats as the default file format. Open XML formats are useful for developers because they are an open standard and are based on well-known technologies: ZIP and XML. Microsoft provides a library for accessing these files as part of the WinFX technologies in the System.IO.Packaging namespace. This SDK is built on top of the System.IO.Packaging API and provides strongly typed part classes to manipulate Open XML documents. Download the FREE SDK from here Why is this important? As a developer using Microsoft technologies, it is highly likely that at some point in your career you will be asked to create a Word, Excel or PowerPoint document from code.  This is even harder should you have to run the code from a server as Office is a desktop application. Now the past was a huge hack to get this right, first office had to be installed on the server. Then the products object model was accessed to create the document and eventually save it.  The hacks came in in trying to deal with unexpected dialog boxes and errors.   Secondly the object model was more aligned with macro writing than coding.  One of the biggest challenges was to attempt to shut down the product after an error and some nice process killing mechanisms employed.  This too resulted in elevated permissions on the service that ran on the server. With Office 2007,  Microsoft has adopted the Open XML form for their Office applications,  you have seen that the documents are now .docx, .xlsx and pptx.  Yep, they are XML documents that follow a strict schema and packaging.  With the .NET 3.0 framework a new namespace System.IO.Packaging was introduced to manipulate the package (.docx, .xlsx and .pptx) and open this ZIP file and manipulate the contents.  Yes, you read it correctly; these files are stock standard zip files. Try it, create a .docx file in word and after you have saved and closed the file from word, simply rename it to a .zip file.  Open and explore the may XML files here. The above SDK now gives you further functionality to manipulate these files and follow the Open XML rule set. Your benefits You can create .docx, .xlsx and pptx files without having to install Office 2007!  Nice if this is a SharePoint server. The SDK implements the Open XML standards which means you do not have to go into raw XML processing. My advice, if this is new to you it will be well worth your time to start learning this technology.  I have been playing with for about two weeks now and find it very valuable.  As and when I find something of interest, I will blog it Posted Sep 08 2007, 04:11 AM by Brett with 1 comment(s) Filed under: SharePoint, Office 2007, Open XML

• Control Flow Tasks

  All the control flow tasks will be identified and outlined in this post.  I suggest using this post as a source of reference and on your first read get a basic idea of what the control flow tasks can do for you in your SQL Server Integration Services package. Data Tasks The data tasks allow you to perform Extract, Transform and Load (ETL) operations on your data and thus full manipulate the data.  In particular the Data Flow Task, as this task allows you to full edit the ETL process on the Data Flow Tab within the package. Data Flow The Data Flow task encapsulates the data flow engine that moves data between sources and destinations, providing the facility to transform, clean, and modify data as it is moved. Addition of a Data Flow task to a package control flow makes it possible for the package to extract, transform, and load data. Bulk Insert The Bulk Insert task provides the quickest way to copy large amounts of data into a SQL Server table or view. For example, suppose your company stores its million-row product list on a mainframe system, but the company's e-commerce system uses SQL Server 2005 to populate Web pages. You must update the SQL Server product table nightly with the master product list from the mainframe. To update the table, you save the product list in a tab-delimited format and use the Bulk Insert task to copy the data directly into the SQL Server table. Execute SQL The Execute SQL task runs SQL statements or stored procedures from a package. The task can contain either a single SQL statement or multiple SQL statements that run sequentially. You can use the Execute SQL task for the following purposes: Database Transfer Tasks The database transfer tasks allow you to move databases around as well as supporting system data such as agent jobs, error messages, master stored procedures and logins.  Transfer Database The Transfer Database task transfers a SQL Server database between two instances of SQL Server. In contrast to the other tasks that only transfer SQL Server objects by copying them, the Transfer Database task can either copy or move a database. The task can copy a database between instances of SQL Server 2000, instances of SQL Server 2005, or one of each. This task can also be used to copy a database within the same server. Transfer Error Messages The Transfer Error Messages task transfers one or more SQL Server user-defined error messages between instances of SQL Server. User-defined messages are messages with an identifier that is equal to or greater than 50000. Messages with an identifier less than 50000 are system error messages, which cannot be transferred by using the Transfer Error Messages task. Transfer Jobs The Transfer Jobs task transfers one or more SQL Server Agent jobs between instances of SQL Server. Transfer Logins The Transfer Logins task transfers one or more logins between instances of SQL Server. Transfer Master Stored Procedures The Transfer Master Stored Procedures task transfers one or more user-defined stored procedures between master databases on instances of SQL Server. To transfer a stored procedure from the master database, the owner of the procedure must be dbo. Transfer SQL Server Objects The Transfer SQL Server Objects task transfers one or more types of objects in a SQL Server database between instances of SQL Server. For example, the task can copy tables and stored procedures. Depending on the version of SQL Server that is used as a source, different types of objects are available to copy. For example, only a SQL Server 2005 database includes schemas and user-defined aggregates. Analysis Services Tasks One of the primary functions of SSIS is to support the loading of data to your data models.  In addition to the ETL components of SSIS, there are also components allowing you to manipulate your cubes and dimensions, process objects within analysis service and finally run prediction queries. Analysis Services Execute DDL The Analysis Services Execute DDL task runs data definition language (DDL) statements that can create, drop, or alter mining models and multidimensional objects such as cubes and dimensions. For example, a DDL statement can create a partition in the Adventure Works cube, or delete a dimension in Adventure Works DW, the sample Analysis Services database included in SQL Server 2005. Analysis Services Processing The Analysis Services Processing task processes Analysis Services objects such as cubes, dimensions, and mining models. Data Mining Query The Data Mining Query task runs prediction queries based on data mining models built in Analysis Services. The prediction query creates a prediction for new data by using mining models. For example, a prediction query can predict how many sailboats are likely to sell during the summer months or generate a list of prospective customers who are likely to buy a sailboat. File and Network Tasks The file tasks allow you to perform disk operations of directories and files. The network task allow for ftp, message queues, smtp and web service calls.  Finally the XML task allows you to work with XML data. File System The File System task performs operations on files and directories in the file system. For example, by using the File System task, a package can create, move, or delete directories and files. You can also use the File System task to set attributes on files and directories. For example, the File System task can make files hidden or read-only. FTP The FTP task downloads and uploads data files and manages directories on servers. For example, a package can download data files from a remote server or an Internet location as part of an Integration Services package workflow. Message Queue The Message Queue task allows you to use Microsoft Message Queuing (MSMQ) to send and receive messages between SQL Server Integration Services packages, or to send messages to an application queue that is processed by a custom application. These messages can take the form of simple text, files, or variables and their values Send Mail The Send Mail task sends an e-mail message. By using the Send Mail task, a package can send messages if tasks in the package workflow succeed or fail, or send messages in response to an event that the package raises at run time. For example, the task can notify a database administrator about the success or failure of the Backup Database task Web Service The Web Service task executes a Web service method. You can use the Web Service task for the following purposes: Writing to a variable the values that a Web service method returns. For example, you could obtain the highest temperature of the day from a Web service method, and then use that value to update a variable that is used in an expression that sets a column value. Writing to a file the values that a Web service method returns. For example, a list of potential customers can be written to a file and the file then used as a data source in a package that cleans the data before it is written to a database. XML The XML task is used to work with XML data. Using this task, a package can retrieve XML documents, apply operations to the documents using Extensible Stylesheet Language Transformations (XSLT) style sheets and XPath expressions, merge multiple documents, or validate, compare, and save the updated documents to files and variables. Execute Tasks The execute tasks allow you to call other packages or components such as ActiveX scripts, VB.NET scripts and even executables. ActiveX Script The ActiveX Script task provides a way to continue to use custom code that was developed using ActiveX script, until such scripts can be upgraded to use the more advanced features provided by the Script task. ActiveX script has typically been used for the following purposes: Including business logic in packages. For example, ActiveX script can use conditional logic to manage package workflow. Writing functions that use conditional logic to set variable values. For example, an ActiveX script can access values in a database table using ActiveX Data Objects (ADO) and populate variables with table values. Performing complex computations. For example, an ActiveX script can compute new dates by applying built-in functions such DateAdd and DateDiff to data values. Accessing data using other technologies that are not supported by built-in connection types. For example, an ActiveX script can use Active Directory Service Interfaces (ADSI) to access and extract the user names from Active Directory. Execute Package The Execute Package task extends the enterprise capabilities of Integration Services by letting packages run other packages as part of a workflow. Execute Process The Execute Process task runs an application or batch file as part of a SQL Server 2005 Integration Services (SSIS) package workflow. Although you can use the Execute Process task to open any standard application, such as Microsoft Excel or Microsoft Word, you typically use it to run business applications or batch files that work against a data source. For example, you can use the Execute Process task to expand a text file. Then the package can use the text file as a data source for the data flow in the package. As another example, you can use the Execute Process task to run a custom Visual Basic application that generates a daily sales report. Then you can attach the report to a Send Mail task and forward the report to a distribution list. Execute DTS 2000 Package The Execute DTS 2000 Package task runs packages that were developed by using the SQL Server 2000 tools. By using this task, you can include SQL Server 2000 DTS packages in SQL Server 2005 data transformation solutions. A package can include both Execute Package tasks and Execute DTS 2000 Package tasks, because each type of task uses a different version of the run-time engine. Script The Script task provides code to perform functions that are not available in the built-in tasks and transformations that SQL Server 2005 Integration Services provides. The Script task can also combine functions in one script instead of using multiple tasks and transformations. The code is custom Microsoft Visual Basic .NET code that is compiled and executed at package run time. Container and looping Tasks The container and looping tasks allow you to repetitively run a set of tasks for a set number of times or for each element in a collection, such as all files in a folder. For Loop The For Loop container defines a repeating control flow in a package. The loop implementation is similar to the For looping structure in programming languages. In each repeat of the loop, the For Loop container evaluates an expression and repeats its workflow until the expression evaluates to False. The For Loop container uses the following elements to define the loop: An optional initialization expression that assigns values to the loop counters. An evaluation expression that contains the expression used to test whether the loop should stop or continue. An optional iteration expression that increments or decrements the loop counter Foreach Loop The Foreach Loop container defines a repeating control flow in a package. The loop implementation is similar to Foreach looping structure in programming languages. In a package, looping is enabled by using a Foreach enumerator. The Foreach Loop container repeats the control flow for each member of a specified enumerator. SQL Server 2005 Integration Services (SSIS) provides the following enumerator types: ADO enumerator to enumerate rows in tables. For example, you can get the rows in an ADO recordset. ADO.NET Schema Rowset enumerator to enumerate the schema information about a data source. For example, you can enumerate and get a list of the tables in the AdventureWorks SQL Server database. File enumerator to enumerate files in a folder. The enumerator can traverse subfolders. For example, you can read all the files that have the *.log file name extension in the Windows folder. From Variable enumerator to enumerate the enumerable object that a specified variable contains. For example, the variable contains the result of a query that is enumerated at run time. Item enumerator to enumerate items that are collections. For example, you can enumerate the rows and the columns in an Excel spreadsheet. Nodelist enumerator to enumerate the result set of an XML Path Language (XPath) expression. For example, this expression enumerates and gets a list of all the authors in the classical period: /authors/author[@period='classical']. SMO enumerator to enumerate SQL Server Management Objects (SMO) objects. For example, you can enumerate and get a list of the views in a SQL Server database. Sequence The Sequence container defines a control flow that is a subset of the package control flow. Sequence containers group the package into multiple separate control flows, each containing one or more tasks and containers that run within the overall package control flow. There are many benefits of using a Sequence container: Disabling groups of tasks to focus package debugging on one subset of the package control flow. Managing properties on multiple tasks in one location by setting properties on a Sequence container instead of on the individual tasks. Providing scope for variables that a group of related tasks and containers use. Instrumentation Tasks Two components allowing you to interact with the Windows Management Instrumentation. WMI Data Reader The WMI Data Reader task runs queries using the Windows Management Instrumentation (WMI) Query Language that returns information from WMI about a computer system. You can use the WMI Data Reader task for the following purposes: Query the Windows event logs on a local or remote computer and write the information to a file or variable. Obtain information about the presence, state, or properties of hardware components, and then use this information to determine whether other tasks in the control flow should run. Get a list of applications and determine what version of each application is installed. WMI Event Watcher The WMI Event Watcher task watches for a Windows Management Instrumentation (WMI) event using a Management Instrumentation Query Language (WQL) event query to specify events of interest. You can use the WMI Event Watcher task for the following purposes: Wait for notification that files are added to a folder and then initiate the processing of the file. Run a package that deletes files when the available memory on a server drops lower than a specified percentage. Watch for installation of an application, and then run a package that uses the application Maintenance Plan Tasks Although not part of a ETL process, these components allow you to perform day-to-day administrative tasks on your SQL database. Back Up Database The Back Up Database task performs different types of SQL Server database backups. Check Database Integrity The Check Database Integrity task checks the allocation and structural integrity of all the objects in the specified database. The task can check a single database or multiple databases, and you can choose whether to also check the database indexes. Execute SQL Server Agent Job The Execute SQL Server Agent Job task runs SQL Server Agent jobs. SQL Server Agent jobs automate tasks that you perform repeatedly. You can create jobs that execute Transact-SQL statements and ActiveX scripts, perform Analysis Services and Replication maintenance tasks, or run packages. SQL Server Agent is a Microsoft Windows service that runs jobs, monitors Microsoft SQL Server, and fires alerts. Execute T-SQL Statement This task is similar to the Execute SQL task. However, the Execute T-SQL Statement task supports only the Transact-SQL version of the SQL language and you cannot use this task to run statements on servers that use other dialects of the SQL language. If you need to run parameterized queries, save the query results to variables, or use property expressions, you should use the Execute SQL task instead of the Execute T-SQL Statement task. History Cleanup The History Cleanup task deletes entries in the following history tables in the SQL Server msdb database. backupfile backupfilegroup backupmediafamily backupmediaset backupset restorefile restorefilegroup restorehistory By using the History Cleanup task, a package can delete historical data related to backup and restore activities, SQL Server Agent jobs, and database maintenance plans. Notify Operator The Notify Operator task sends notification messages to SQL Server Agent operators. A SQL Server Agent operator is an alias for a person or group that can receive electronic notifications. Rebuild Index The Rebuild Index task rebuilds indexes in SQL Server database tables and views. Reorganize Index The Reorganize Index task reorganizes indexes in SQL Server database tables and views. Shrink Database The Shrink Database task reduces the size of SQL Server database data and log files. Update Statistics The Update Statistics task updates information about the distribution of key values for one or more statistics groups (collections) in the specified table or indexed view. Posted Sep 01 2007, 10:02 AM by Brett with no comments Filed under: SSIS