Is this you? As a journalist, you can create a free Muck Rack account to customize your profile, list your contact preferences, and upload a portfolio of your best work.
Claim your profile
Articles by Stan Kulp
Supervised versus Unsupervised Training of an Artificial Neural Network – SQLServerCentral
Supervised training of an artificial neural network entails training the network to associate defined patterns with specified labels. Optical Character Recognition is an example of supervised training, where each input pattern is mapped to an alphanumeric character. In unsupervised training, a neural network learns to extract recurring patterns from noisy data.
How to deploy and execute an SSIS package from the SSISDB catalog – SQLServerCentral
In this walk-through we will create a simple SSIS package that writes to a database table, deploy the SSIS project containing the package to the SQL Server Integration Service's catalog and execute the package (1) from the SSIS Catalog's Execute Package GUI and (2) from a T-SQL script generated by the Execute Package GUI. 1. Create a destination table Create a table for our test package to populate by executing the following T-SQL code...
Generate QR Code® barcodes in an SSRS report with the QRCoder library – SQLServerCentral
While the QRCode4CS open source library used in the article, Generate two-dimensional QR Code® bar codes in an SSRS report, enables the display of QR Codes in SSRS reports, it turns out that it only supports the Micro QR Code standard, which limits the input string to 34 characters. In contrast, the open source QRCoder library can support an input string of up to 2952 characters.
SSRS Audit: All reports executed/not-executed during previous 30 days – SQLServerCentral
Microsoft does not document or support querying the ReportServer database, but it contans a lot of useful information about SSRS reports. A previous article, Dynamic SSRS report documentation via a ReportServer.dbo.Catalog query, demonstrated how to query the ReportServer.dbo.Catalog to generate live documentation of SSRS reports. In this article we will query the ReportServer.dbo.ExecutionLog table to determine which reports have been executed in the past month and which have not.
Parse Data from a Field Containing Multiple Values using CROSS APPLY – SQLServerCentral
There are two kinds of ASCII files commonly used for data transfer: Flat - a plain text file where each field value is the same width and padded with spaces. Delimited - a plain text file where each field value is separated by specific delimiter characters. By definition, a flat file can only have one value per field per record. A delimited file, however, can have one or more fields that are further delimited by other delimiter characters.
Linear barcodes in SSRS using the Barcode Image Generation Library – SQLServerCentral
The Barcode Image Generation Library was designed to provide a C# class that enables generation of barcode images from a string of data. It currently supports the following 27 linear barcode encoding types: Bookland/ISBN Code-128C MSI Codabar Code-93 Pharmacode Code 11 EAN-13 PostNet Code 39 EAN-8 Standard 2 of 5 Code 39 Extended FIM Telepen Code 39 Mod 43 Interleaved 2 of 5 UPC 2 Digit Ext. Code-128 ITF-14 UPC 5 Digit Ext.
Generate QR Code® barcodes in an SSRS report with the QRCoder library – SQLServerCentral
While the QRCode4CS open source library used in the article, Generate two-dimensional QR Code® bar codes in an SSRS report, enables the display of QR Codes in SSRS reports, it turns out that it only supports the Micro QR Code standard, which limits the input string to 34 characters. In contrast, the open source QRCoder library can support an input string of up to 2952 characters.
How to execute an SSIS package from the command line or a batch file – SQLServerCentral
Business Intelligence Development Studio is well-suited to creating and testing SSIS packages, but batch files using the DTExec command are a more convenient means of executing production SISS packages. The DTExec.exe command requires one parameter, the path to a valid SSIS package. The path to a configuration file can be added to override parameters hard-coded into the package, along with other switches to select execution options such as error output mode and log file path.
Machine Learning 101: The Mathematics of an Artificial Neural Network – SQLServerCentral
Attached as a resource to this article is an MS DOS application I wrote a few years ago in Microsoft QuickBASIC and assembly language that displays a functioning artificial neural network (ANN) and illustrates the underlying mathematics. While the operating system and code are obsolete, the math is not. Executing the DOS Optical Character Recognition (OCR) Demo Application Download and install the free DOSBox DOS-emulator from http://dosbox.com.
Batch SSIS pkg execution from Business Intelligence Development Studio – SQLServerCentral
SSIS packages can be executed from Business Intelligence Development Studio (BIDS) one at a time, but this can be cumbersome when there are a lot of packages to run and data to import or process. SSIS package execution can be automated with the DTEXEC.exe command or the SSISDB catalog, but both these methods require the installation of Integration Services and the requisite license.
Dynamic SSRS report documentation via a ReportServer.dbo.Catalog query – SQLServerCentral
Creating adequate documentation for any IT project is a challenge, particularly with SSRS where an organization can have literally hundreds or thousands of reports in production. Writing the documentation alone can be a formidable task, not to mention keeping it up-to-date when changes are made. Fortunately, the SQL Server ReportServer.dbo.Catalog table contains data that can be used to create a documentation system that is automatically updated as report changes are made.
Execute a data-driven SSRS subscription from SSIS to archive a report – SQLServerCentral
There are situations in which it would be useful to save an SSRS report to a network folder when an underlying table is updated by an SSIS package. This can be accomplished by creating a data-driven subscription for the report and triggering the subscription from the SSIS package via SQL Server Agent and the ReportServer.dbo.AddEvent stored procedure.
Create/write to an Excel 2007/2010 spreadsheet from an SSIS package – SQLServerCentral
In this article we will develop an SSIS package that uses the EPPlus.dll assembly to create an Excel file and write to it while looping through the result set of a SQL Server query. 1. Download and extract EPPlus.zip Download the EPPlus zip file from http://epplus.codeplex.com/. Extract the contents of the EPPlus zip file to a folder.
Trigger an Email of an SSRS Report from an SSIS Package – SQLServerCentral
There are situations in which it would be useful to send a report to interested parties when an underlying table is updated by an SSIS package. This can be accomplished by creating a subscription to the report and triggering the subscription from the SSIS package via SQL Server Agent and the ReportServer.dbo.AddEvent stored procedure.
Dynamically change background color in SSRS reports using custom code – SQLServerCentral
Changing the background color of rows can be a valuable method of visually grouping data in a Reporting Services (SSRS) report, as demonstrated in the screen shot below. Dynamically changing the background color requires custom code that can compare the current value of a field with its value in the previous record and cell expressions that toggle the cell background color if the two values are not the same.
SQL-only ETL using a bulk insert into a temporary table (SQL Spackle) – SQLServerCentral
"SQL Spackle" is a collection of short articles written based on multiple requests for similar code. These short articles are NOT meant to be complete solutions. Rather, they are meant to "fill in the cracks". --Phil McCracken Introduction The BULK INSERT statement imports a data file into a database table or view in a user-specified format in SQL Server 2005 and above.
Encrypt a file from SSIS using the Advanced Encryption Standard (AES) – SQLServerCentral
The Advanced Encryption Standard (AES) cryptography code used in this project was adapted from the C# code that Ira Lukhezo has kindly provided on his blog at: http://lukhezo.com/2011/11/06/encrypting-files-in-net-using-the-advanced-encryption-standard-aes/ Overview Creating an SSIS package that encrypts a file with a CLR assembly and generates an MD5 checksum file entails the following steps: (Steps 1 through 6 can be omitted if you prefer by using the attached AES256.dll file rather than...
How to create an SSIS conditional data flow task – SQLServerCentral
A Data Flow Task created with the SSIS Import/Export Wizard reads an entire data file into a single destination table. You cannot use the import/export wizard alone if you wish to direct records in a data file to different tables based upon the values of fields in the record, or to filter out some records entirely. You can either modify a wizard-generated data flow task, or create one from scratch.
SFTP, encrypt or compress data files in SSIS using custom components – SQLServerCentral
The following article is a walkthrough of the CodePlex open source SSIS extensions for SFTP, PGP encryption and Zip archiving. Resources Download the appropriate version of the SSIS extensions, and the SFTP mini-server.. 1. SSIS Extensions - SFTP Task, PGP Task, Zip Task Web Site: http://ssisextensions.codeplex.com/releases/view/101949 2008 R2 Download Link: http://ssisextensions.codeplex.com/downloads/get/623584 2012 Download Link: http://ssisextensions.codeplex.com/downloads/get/616818 2.
How to implement SHA-2 in SQL Server 2005 or 2008 with a CLR assembly – SQLServerCentral
A hash function maps a data string of variable length to a data string of a fixed length. The fixed-length data string is called a hash value. A hash function is one-way, meaning that you cannot use a hash function to recreate the original data string from its hash value. A secure hashing algorithm will produce a unique value for any given data string. A collision occurs when a hash function produces the same hash value for two different data strings.
How to Compile, Deploy and Consume a SQL Server CLR Assembly – SQLServerCentral
Databases that include Social Security or credit card numbers are vulnerable to identity theft and represent a liablity for any organization possessing either one. For that reason it makes sense to store Social Security and credit card numbers as encrypted values. Use of a CLR (Common Language Runtime) assembly for encryption can be useful in situations where you are trying to match the encryption algorithm being used by an extenal organization with which you exchange data.
Batch ETL of Multiple Data Files Using an SSIS Foreach Loop Container – SQLServerCentral
An SSIS ETL package created with the Import/Export Wizard will process one data file at a time. There may be situations, however, when multiple data files will be arriving continuously for extraction to the same table. A base SSIS package generated using the Import/Export Wizard can be modified to process these files in batch mode. In this tutorial we will take an SSIS package generated by the Import/Export wizard and modify it to process multiple files and move those files to a backup directory.
Basics of XML and SQL Server, Part 5: Generate/Transmit XML with SSIS – SQLServerCentral
The attached EmailFtpXmlInvoice.dtsx demo SSIS package queries the Clients table for client ids, then loops through the client ids to: Generate invoice XML code for each client using a set of FOR XML queries. Save the invoice XML code to an XML file. Email the XML invoice file to the client if there is an email address for the client in the lookup table. FTP the XML invoice file to the client's server if there is FTP server information for the client in the lookup table.
Basics of XML and SQL Server, Part 4: Create an XML invoice with SSIS – SQLServerCentral
One of the more common tasks for which XML is used for is to automate invoicing. This is what a simple text invoice looks like. This is what an analogous XML invoice looks like. Creating an XML document like an invoice is more art than science. There is no single correct way to do it. Having a paper document to use as a model helps a lot.
Basics of XML and SQL Server, Part 3: Transform and Shred XML in SSIS – SQLServerCentral
The following SSIS package can be modified to transform and shred any XML document, provided you create the appropriate XSL stylesheet(s) for the transformation(s), and construct the required XQuery SQL code to shred the desired records from the transformed file for insertion into SQL Server. The XmlTransformation.dtsx SSIS package in the attached demo file archive contains two versions of the Script Task, one written in C# and the other written in VB.NET.
Basics of XML and SQL Server, Part 2: Shredding XML – SQLServerCentral
The first step in shredding an XML document and inserting its data into a SQL Server table is getting the document into the most easily-processed form. The most easily-processed form of XML document is one in which all of its data is encapsulated inside elements and none of its data is in the form of attributes. With that in mind, we are going to create an XML document in which all of the data is in attributes and convert it to one in which all of the data is in elements.
Basics of XML and SQL Server, Part 1: Streaming XML – SQLServerCentral
XML simply defines a container for data. An XML document doesn't do anything with its data. There is no way to throw something at the document and observe the results. All you can do is browse its boring elements or embed some boring data inside it or memorize some boring XML syntax rules in the hope of doing something with it. The XSL stylesheet is the missing link needed to interact with an XML document.
How to join a local table to a remote table by way of a linked server – SQLServerCentral
A linked server configuration enables SQL Server to execute distributed queries against tables on remote servers. Unfortunately, linked servers can also cause significant performance degradation because of the volume of network traffic generated between the servers and the fact that processing cycles are being consumed on both servers.
A script task alternative to a massive SSIS multicast transformation – SQLServerCentral
A Multicast Transformation can be used for many things, and one of those things is to write the same data file generated from a query in a Data Flow Task into multiple network locations. The only problem with this approach is that each destination connection and file must be configured manually, which can get tedious and messy as the number of destination files grows. Just look at the package in the image below.
Dynamically create and populate staging tables from CSV files – SQLServerCentral
The following SSIS package dynamically creates staging tables from comma-separated value (CSV) files in a subdirectory using the field names contained in the first line of each file, then populates the newly created tables with the data contained in the remaining lines of each file. Each staging table is given the same name as the corresponding CSV file.
An Introduction to Integration Services log providers – SQLServerCentral
To demonstrate SQL Server Integration Services' logging features, create a new SSIS package by clicking "Create: Project" from the opening screen of Microsoft Visual Studio, selecting the "Integration Services Project" template, and entering the name "LoggingDemo." Click the "OK" button to create the project. A default SSIS package named "Package.dtsx" has been created and opened by default.
A C# toolkit for writing SSIS Script Tasks – SQLServerCentral
It is not necessary to know the C# universe in order to write valuable C# script tasks. Keep in mind that it is being used as a scripting language, not a full-fledged application development language. The ability to perform the following basic operations will allow you to do quite a lot.
A VB.NET toolkit for writing SSIS Script Tasks – SQLServerCentral
The standard SSIS task components provide a robust array of services, but the loop task components (For Loop and Foreach Loop Containers) are unacceptably slow, iterating in the area of dozens of loops per second. VB.NET loops are much faster, capable of iterating at tens of thousands of loops per second. When an SSIS job requires repetitive processing through thousands of records, a custom script task can be very useful.
Extract multiple files from multiple directories with a data flow task – SQLServerCentral
[AUTHOR] Comments to my previous related article indicated that it should have had error checking. I have added an addendum and another file to address this in this article. A previous article demonstrated how to extract multiple files from a single subdirectory with a data flow task. This article demonstrates how to extract multiple files that reside in more than one subdirectory.
Extract multiple files with a MultiFlatFileConnection data flow task – SQLServerCentral
By default, a data flow task generated by the SSIS Import and Export Wizard extracts data from a single file. By changing the file connection manager of a data flow task from a FlatFile connection to a MultiFlatFile connection, a data flow task can extract all the designated files in the folder containing the original file using a wildcard in place of part of the original file name.
How to Name Sheets in Exported Excel Workbooks with SSRS 2008 R2 – SQLServerCentral
In versions of SQL Server Reporting Services (SSRS) previous to SSRS 2008 R2, multi-sheet Excel workbooks exported from a report looked like this, with sheets named "Sheet1," "Sheet2," "Sheet3," etc. Starting with SSRS 2008 R2, the sheets can have custom names designated in the PageName attribute of report items that contain a PageBreak attribute. Only SSRS 2008 R2 or later supports named sheets for exported Excel files through the PageName attribute.
Simplify Large Queries with Temporary Tables, Table Variables and CTEs – SQLServerCentral
Large, complex queries involving multiple joins, unions and nested subqueries can get incomprehensible fast, and can be extremely difficult to validate, revise or troubleshoot. Views are a means of simplifying such queries, but it is impractical to create new views for every query you write. Temporary tables, table variables and common (derived) table expressions (CTEs) can be thought of as ad hoc views that can be created on demand.
Copy-and-Paste SSIS Execution Monitoring with Failure-Alert Email – SQLServerCentral
A lot of things can and do go wrong when using a suite of SSIS packages to populate a SQL Server data warehouse with files from various sources on a daily basis. When something does go wrong, it is important to detect and rectify it in a timely manner. A missing data file or a file containing incomplete, corrupt, or improperly formatted data can cause a package to fail and data to be omitted, while a file that doesn't get transferred in time can cause the previous day's data to be duplicated.
No SFTP Task Component in SSIS 2005/2008? No Problem! – SQLServerCentral
The Problem There is no SFTP (SSH/TLS-encrypted FTP) Task component in SSIS 2005/2008, but sometimes unencrypted file transfers are not acceptable.
Hello SSIS World, From XML! – SQLServerCentral
Extensible Markup Language (XML) is a robust method of transferring data between systems, but it can be confusing and difficult to implement for the beginner. The SSIS "XML Source" data flow component makes it trivial to read XML data into an SSIS package, and is an excellent way for an XML newbie to hit the ground running.
Show More
loading
Actions
Is this you?
As a journalist, you can create a free Muck Rack account to customize your profile, list your contact preferences, and upload a portfolio of your best work.Get in touch with Stan
Contact Stan, search articles and posts on X, monitor coverage, and track replies from one place.
Learn more about Muck Rack