With the absolution of SQL Server 2005 and its accompanying Business Intelligence (BI) toolset, the role of the DBA is boring changing. It is now accepted that the DBA should accept some compassionate of the all-embracing BI toolset, such as SQL Server Integration Services (SSIS), SQL Server Analysis Services (SSAS) and SQL Server Reporting Services (SSRS). This commodity will focus on SSIS, which is the new Extract, Transform and Load (ETL) toolset from Microsoft.
As with any new abstruse apparatus it requires time and accomplishment to get accustomed with the product, and SSIS is no exception. There are some assets on the Internet, but best of these are alone snippets of what you may need. BOL does accept some advice on SSIS, but the abridgement of advantage alloyed with ambiguous account and burst cipher examples do accomplish it difficult for the uninitiated. Best of the advice that I begin attainable are gleaned from the development association and accessories begin on the WWW (refer to the References area below). Therefore, in the spirit of reciprocity, I accept accounting this commodity which I achievement would animate you to investigate added the adaptability allotment of SSIS.
The afterward adeptness will be affected for the purpose of this discussion. It is accepted that you accept these accessories installed and attainable from your computer:
SQL Server 2005 (including SSIS), Microsoft Visual Studio 2005, .NET Framework v2.0, Visual SourceSafe 6.0 (or after versions)
In accession to the above, you should accept at atomic a basal knowledge/understanding of the accessories as able-bodied as some acquaintance with a scripting accent able-bodied be application VB .NET for this project.
There is a bifold purpose to this project; firstly, to attending at how to body a SSIS antecedent basic that will acquiesce you to abstract files from a adaptation ascendancy arrangement (such as Microsoft Visual SourceSafe); and secondly, to attending at how to use this custom congenital basic in SSIS, accurately to apprehend the capacity of files and to assassinate the SQL statements adjoin a database. For the purpose of this discussion, it will be affected that the capacity of the files are accurate T-SQL scripts.
As allotment of the architecture blocks for the custom SSIS component, I accept created a .NET chic library Gww.Vss.CommonLibrary.dll. The assemblys capital action is to affix to Visual SourceSafe athenaeum and to abstract the files based on latest adaptation or based on the accurate ‘label’ assigned to the files.
Begin by aperture an instance of Microsoft Visual Studio 2005; actualize a new Visual Basal Windows activity and baddest the Chic Library activity template. Access the following: Gww.Vss.CommonLibrary for Name, C:ProgrammingDevelopment for Location, and SSIS for Band-aid Name. Bang OK to continue.
In Band-aid Explorer bang on the figure labeled Show All Files. Right-click the References bulge and baddest Add Reference from the ambience menu. Baddest the COM tab and annal bottomward to locate the basic alleged Microsoft SourceSafe 6.0 Blazon Library (version 5.1 or a after adaptation of SourceSafe). Baddest the basic and Bang OK to continue.
Next, baddest and alter the capacity of chic Class1 with the capacity of Listing 1. It is not all-important to rename the class, so we will leave it as Class1 for now.
Still in Band-aid Explorer, right-click the activity name Gww.Vss.CommonLibrary and baddest Properties. In the Application tab, bright out the agreeable of the Root Namespace argument box.
In adjustment to annals the accumulation in the Global Accumulation Cache (GAC), it is all-important that the accumulation has a Able Name. There is a account alleged sn.exe that can be acclimated for this purpose. However, for this project, cross to the Signing tab, baddest Sign the accumulation option, and baddest <New> from the drop-down list. Blazon a name for the able name key book (I accept acclimated gww). Finally, uncheck the advantage to Protect my key book with a password. Bang OK and save the activity file.
The accumulation is accessible for its aboriginal build. Right-click on the activity Gww.Vss.CommonLibrary and baddest Build. Verify that the body completed after any errors. Booty the time to accept a attending at the capacity of the binder C:ProgrammingDevelopmentSSISGww.Vss.CommonLibrary and its sub-folders.
The final footfall is to annals the basic in the GAC. To do this, assassinate the accumulation book in Listing 2. To actualize a accumulation file, artlessly save the Notepad book in your bounded binder with the .bat filename extension. Double bang the book to assassinate the accumulation commands. Verify that there were no errors. Analysis that the accumulation has been registered in the GAC by active the accumulation book in Listing 3: a gacListing.txt book is created in the agenda area the accumulation book is executed. Confirm that both Gww.Vss.CommonLibrary and Interop.SourceSafeTypeLib are listed in gacListing.txt. If needed, actualize a abstracted accumulation book agnate to Listing 2 to annals the Interop.SourceSafeTypeLib assembly.
Start a new instance of Microsoft Visual Studio 2005. As before, actualize a new Visual Basal Windows activity and baddest the Chic Library activity template. Access Gww.Dts.Pipeline for Name, C:ProgrammingDevelopmentSSIS for Location and uncheck the Actualize agenda for band-aid option. Bang OK to continue.
In Band-aid Explorer bang the Show All Files icon. Aggrandize the References bulge and adapted bang the bulge to baddest Add Reference from its ambience menu. Baddest the .NET tab and annal bottomward to locate the afterward assemblies baddest anniversary by application Ctrl Left-Mouse-Click: Gww.Vss.CommonLibrary, Microsoft.SqlServer.DTSPipelineWrap, Microsoft.SqlServer.DTSRuntimeWrap, Microsoft.SqlServer.PipelineHost. Bang OK to add the called assemblies to the accepted project.
Next, baddest and alter the capacity of chic Class1 with the capacity of Listing 4. It is not all-important to rename the class, so we will leave it as Class1 for now.
We are about accessible to body the component. In Band-aid Explorer adapted bang the activity bulge Gww.Dts.Pipeline and baddest Properties. Firstly, in the Application tab, bright out the agreeable of the Root Namespace argument box; secondly, baddest the Compile tab and annal bottomward to acknowledge the Body Events on. Archetype and adhesive the scripts in Listing 5 to the Post-build accident command band box. This will advice with automatically registering the accumulation in the GAC aloft a acknowledged build. Analysis that On acknowledged body advantage is called for Run the post-build accident alternative list; and lastly, baddest the Signing tab, baddest the Sign the accumulation option, and baddest <Browse> from the drop-down list. Cross to the binder area you created the book gww.snk beforehand on or alternatively you can actualize a new SNK file. Press OK to continue.
The accumulation is now accessible for its aboriginal build. Right-click on the activity Gww.Dts.Pipeline and baddest Build. Verify that the body completed after any errors. Also verify that the accumulation was auspiciously added to the GAC by active the accumulation book in Listing 3.
Now the fun part. To use the custom component, alpha a new instance of Microsoft Visual Studio 2005. Actualize a new SSIS activity by selecting Business Intelligence Projects and again Integration Services Project. Give the activity an adapted name and bang OK.
The Ascendancy Flow tab should already be called (top left-hand corner). Next, baddest Toolbox from the Appearance menu. Beneath Ascendancy Flow Items annoyance and bead the account Data Flow Task assimilate the Ascendancy Flow pane. Perform this aforementioned footfall for the afterward three items: Foreach Loop Container, Calligraphy Task, and Assassinate SQL Task. Your artist should attending commodity like Figure 1.
It is absolutely accepted to use variables in SSIS. For this project, we will actualize a capricious of blazon commodity with a Amalgamation ambit and two variables of blazon cord with a Foreach Loop Container scope. To do this, right-click on an abandoned allotment of the Ascendancy Flow area and baddest Variables from the ambience menu. Next, bang on the Add Capricious on to actualize a new variable. Access rsFilepath for name and baddest commodity for Data Type. Leave the Variables window accessible and bang on the Foreach Loop Container to baddest it (this footfall is important); bang on the Add Capricious on to actualize a new variable. Access fpath for name and baddest cord for Data Type. Actualize a third capricious and access ftext for name and baddest cord for Data Type. Verify that the aftermost two variables are assigned the Foreach Loop Container scope. Close the Variables window.
Double-click the Data Flow Task, which will booty you to the Data Flow area (second tab on the list). Next, baddest Toolbox from the Appearance menu. Right-click anywhere on the Toolbox window and baddest Choose Items from the ambience menu. In a few seconds, the Choose Toolbox Items window appears. Baddest the SSIS Data Flow Items tab, annal bottomward to locate the accumulation VssSourceAdapter and analysis the box to baddest the item. Press OK to continue.
The VssSourceAdapter basic should now be arresting in the Toolbox beneath Data Flow Sources. Note that the basic blazon was declared as a SourceAdapter in Listing 4, which is the acumen that it appears beneath the Data Flow Sources list: The added two forms are Data Flow Transformations and Data Flow Destinations.
Next, annoyance and bead the VssSourceAdapter basic assimilate the Data Flow pane. Also annoyance and bead the Recordset Destination account (listed beneath Data Flow Destinations). Join the achievement from VssSourceAdapter to the ascribe of Recordset Destination, as apparent in Figure 2. The Recordset Destination is acclimated as a bore to abundance the achievement from the custom component.
Double-click the VssSourceAdapter basic to accompany up its backdrop window. In the Basic Backdrop tab, annal bottomward to Custom Backdrop and ample in the appropriate ambit as per your environment. Bang OK to continue.
Next, double-click the Recordset Destination basic to accompany up its backdrop window. In the Basic Backdrop tab, access rsFilepath for VariableName beneath Custom Properties. Cross to the Ascribe Columns tab and baddest the analysis box aing to the name filepath. Bang OK to continue.
This completes the agreement all-important for the Data Flow Task. The aing agreement will absorb the Foreach Loop Container. Bang on the Ascendancy Flow tab to baddest it. Double-click the Foreach Loop Container to accessible its backdrop window. Bang on the account labeled Collection from the left-hand pane. For Enumerator, baddest Foreach ADO Enumerator from the drop-down list. Beneath Enumerator Agreement baddest User::rsFilepath for ADO commodity antecedent capricious and baddest Rows in the aboriginal table for Enumeration Mode. Next, bang on the account labeled Capricious Mappings from the left-hand pane. Configure the cavalcade Capricious as User::fpath and 0 for the Index column. Bang OK to continue.
To configure the Calligraphy Task, double-click it to accessible the Calligraphy Task Editor window. Baddest the account labeled Calligraphy from the left-hand pane. Access fpath for ReadOnlyVariables and ftext for ReadWriteVariables. Next, bang on Design Calligraphy to instantiate Microsoft Visual Studio for Applications. Beneath ScriptMain, alter the absolute calligraphy with the capacity of Listing 6. Bang OK to continue.
Lastly, double-click on the Assassinate SQL Task to accessible its backdrop window. On the left-hand pane, bang on the account labeled Script. Beneath Result Set, baddest None from the drop-down list. Beneath SQL Statement, baddest OLE DB for Connection Type, baddest <New Connection> for Connection configure a accurate SQL Server and database to assassinate the scripts against. Baddest Capricious for SQLSourceType and User::ftext for SourceVariable. That is it! Press F5 to alpha beheading of the SSIS package.
Here are all the assorted listings:
There are artlessly too abounding things to be abundantly covered in an commodity such as this, including how to alter a SSIS component, how to body a basic that supports an absurdity output, how to use the Data Viewer, etc. Therefore, for those adulatory to aggrandize their adeptness a little further, I will artlessly accredit you to the accessories listed beneath References.
I accept two accurate use for the custom component; one is the adeptness to synchronise all my stored procedures and functions on a accurate database on assorted servers I do this with my aliment database, which contains all my DBA specific stored procedures and functions; the additional use is for applying database advancement scripts in a specific order. There are a brace of approaches to this, but I will leave it as an exercise for you to do at your own leisure, and at the aforementioned time exploring the affluent functionality offered by SSIS.
One of the best accessories on the web ambidextrous with architecture SSIS custom basic a agenda archetype of one accomplished affiliate from a book. An accomplished album for SSIS extensibility: http://media.techtarget.com/digitalguide/images/Misc/sqlserver_ch14.pdf
A actual acceptable commodity anecdotic a agnate activity from SQLServer Magazine. You may charge to be a paid subscriber to appearance its contents: http://www.sqlmag.com/Article/ArticleID/45801/sql_server_45801.html
An aloft boilerplate commodity from the msdn2 website Building, Deploying and Debugging Custom Objects: http://msdn2.microsoft.com/en-US/library/ms403356.aspx#building
A clear commodity from the msdn2 website Developing a Custom Antecedent Component: http://msdn2.microsoft.com/en-us/library/ms136088.aspx
This is the SSIS Learning Centre from SearchSQLServer.com website: SSIS Center
You Should Experience Custom Tincture Labels At Least Once In Your Lifetime And Here’s Why | Custom Tincture Labels – custom tincture labels
| Welcome to be able to our blog, with this period I’ll demonstrate in relation to custom tincture labels