Managing all your ETL requiement and file system tasks using SQL Server Integration Services
In many case within the forums and newsgroups I still see many questions from the users asking about SSIS (SQL Serve Integration Services) and here few bits I'm covering on basics on this important service in SQL Server arena.
SSIS is a key part of SQL Server that will help the user to manage data integration services efficiently, ETL packages such as update data warehouses, interact with external resources (outside SQL) and manage import/export with better administrative tasks. To give a brief overview of SSIS architecture it consists of four key parts: the Integration Services service, the Integration Services object model, the Integration Services runtime and the run-time executables, and the Data Flow task that encapsulates the data flow engine and the data flow components.
The basic task of a Developer or DBA is better to keep straight on fundamentel approach towards Integration Services programming whereby you can extend packages by writing components that become available within SSIS Designer to provide custom functionality in a package. Not only that you can easily create, configure, and run packages programmatically from your own applications. A very little in SSIS that you can't achieve with the built in components, if you include the script task and script component then almost everything is achievable.The flexibility of SSIS is if built-in components do not meet your requirements, you can extend the power of Integration Services by coding your own extensions. It is a big doubt for the new users (in SSIS) that how-to in this approach, for instance for ad hoc use in a single package, you can create a custom task by writing code in the Script task, or a custom data flow component by writing code in the Script component, which you can configure as a source, transformation, or destination. These powerful wrappers write the infrastructure code for you and let you focus exclusively on developing your custom functionality; however, they are not easily reusable elsewhere. However for the use in multiple packages, you can create custom Integration Services extensions such as connection managers, tasks, enumerators, log providers, and data flow components. The managed Integration Services object model contains base classes that provide a starting point and make developing custom extensions easier than ever.
Data flow management is another key area in SSIS that helps users to manage ETL processes, here within IS has two distinct engines that define this architecture and that can be automated and extended when programming Integration Services. The run-time engine implements the control flow and package management infrastructure that lets developers control the flow of execution and set options for logging, event handlers, and variables. The data flow engine is a specialized, high performance engine that is exclusively dedicated to extracting, transforming, and loading data. When programming Integration Services, you will be programming against these two engines. Just a reference from Books Online on programming all the aspects of SSIS that, you can program Integration Services packages, custom tasks, and components in Microsoft Visual Studio .NET or in another code or text editor. Visual Studio .NET offers the developer many tools and features to simplify and accelerate the iterative cycles of coding, debugging, and testing. Visual Studio .NET also makes deployment easier. However, you do not need Visual Studio .NET to compile and build Integration Services code projects. The Microsoft .NET Framework SDK includes the Visual Basic .NET and C# compilers and related tools. You may be aware that .NET framework is installed by default with SQL Server 2005 but not the SDK, so to extend this functionality you can download the .NET SDK from this Microsoft Web site. Just a tip that after you downloaded and installed the .NET SDK you can add it to your local SQL Server Books Online and SSMS that will enable the direct links from BOL to SDK documentation, this is the BOL link ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/sqlgtst9/html/ef798cc8-87cf-4d60-a7bf-9e061bdd0052.htm that explains how-to do this task.
In this regard a common question in forums is how can I delete files in a folder and manage the files only that meets a specific criteria such as, the first day of the month in the creation date?
Until the age of SQL Server 2000 version using DTS with the help of Activex or VB Script this is achieveable to some extent, but there were many restrictions to accomplish the task. It is more easier now that using Windows Management Instrumentation (WMI) tasks you can perform such tasks without any issues. Using WMI you can programme the flow of wait for notification that files have been added to a folder and then initiate the processing of the file and then run a package that deletes files when the available memory on a server drops lower than a specified percentage. FOr such references I would recommend to go through this SYSTEM_IO link to know more about it.