SQL Server writable scripts and programming extensions SSIS packages

The SQL Server Integrated Services (SSIS) used in Microsoft's SQL Server 2005 to replace data transfer services (DTS) contains a number of tools for importing data and translating it into meaningful information, not just passive imports. But one thing to keep in mind is that this new SSIS tool doesn't always cover everything you need to do.

As a result, Microsoft offers two basic ways to extend the capabilities of SSIS. One approach is relatively simple for people who don't have a lot of programming experience, or who don't need to write complex programs, and the other is complex, which allows programmers who like to challenge to go into SSIS and extend it to a large extent.

Simple way: Script

Most of us have at least one contact with the script, SSIS by using VB in SSIS packages. NET to provide programmers with the ability to write script actions. The scope of the script is very small and centralized compared to the customer project, which is used when you need to make some changes in the scope of the existing package to allow or to be completed.

Within the SSIS package, there are two elements that you can use to add scripts: The script Task (in the Control Flow window of the integrated service Design application) and the script Component (in the Data Flow window). They have a slightly different application environment.

A script task is a flow control that you use to achieve a general purpose within a package--it is more global, more powerful, but more complex than a script component (Component). It runs outside of the packet's data stream and cannot be constrained by the way the data flow works, although the script task usually runs only if the package is triggered (although you can build it inside the exception). Tasks also support breakpoints and debugging, which is useful when you write more granular scripts that have control logic or a certain kind of decision. An example of a scripting task is querying the Active Directory, looking for information about the data, or talking to another data warehouse-all before the package is run.

The way script components work more closely with data flow. The script component does not run once in the entire package, but its primary processing runs once for each data row that needs to be processed. The scripting component has three basic operating environments: Data sources, data transformations, or data targets. The component is also less interactive-it does not support the type of debugging that the scripting task supports. Most of the scenarios that use scripting components are a line-by-row conversion, a client ODBC target, or an unimportant error-handling or conversion action that cannot be handled by SSIS local functions.

Advanced approach: Programming for Client Objects

Although the script inside the SSIS package is powerful, sometimes it still fails to accomplish certain tasks. In some cases, you may need to write from scratch (or someone else writes) a client SSIS extension. It's not an easy thing to do; it requires you to fundamentally understand programming. But for a client object, it may be used in some way SSIS, but this is definitely not a simple automation task.

For example, if your data source does not support any existing SSIS transformations (for example, some of the odd private data sources that are no longer supported by the manufacturer), you can write a client connection management object that allows you to use the data as you do locally. Similarly, you can create a customer task, a log provider, or a data flow component with the same programming library implemented through SSIS.

Each of these types can be used as the base class, attribute, and method set in SSIS-Supported languages: Visual Basic, C #, C + +, J #, and JScript. C + +, C # and VB are more likely to produce the best results, because in these cases they are more supported by developers and vendors. The idea is that the language you use should not be a hindrance to you; they can all be plugged into the same external programming interface. You can also create user interfaces for client objects through standard Windows Forms, whether or not they are needed.

An extremely powerful example of this is that you can create customizable front-end survey devices from the SSIS client object. We say that if you want to create a set of program classes to perform tasks for each set of objects in the collection, for example, a set of tables in a database. If you want to achieve such an action on a wide scale and don't need to reinvent the wheel every time, that's the best way to do it. When you create a client Connection manager for some new types of data, such as those mentioned in the previous example, and you want to create a customer front-end action above, it is particularly useful.

Conclusion

The way you extend SSIS, both scripting and programming, is determined by your needs and your abilities. Because you can use two ways-even at the same time!—— you can do without a lot of effort. You can also make corrections and matches as needed.