Business Intelligence Development Studio
Using BIDS to develop Integration Services projects
BIDS is a Microsoft Visual Studio Tool that may be used to develop Integration Services projects, Reporting Server projects, Analysis Server projects and other database projects. BIDS is available in the SQL Server program group after installation. Click on Start -> SQL Server -> Business Intelligence Development Studio to open BIDS.
Let us use BIDS to create a new database project and open the book_orders_extended stored procedure for viewing and debugging. As you may have observed, using just the SQL Cmd tool to create and work with Stored Procedures does not facilitate ease of debugging and testing. BIS offers a far better tool to work with programmable database objects.
New Database Project
Upon opening BIDS, go to the 'File' menu and click New -> Project. The new project window pops up; expand 'Other Project Types' and click 'Database'. Specify a suitable name and location for the project and click 'OK'.
Choose Data Source
Select 'Microsoft SQL Server' in the choose datasource dialogue and click continue. Now, select your server from the list after pressing the down arrow next to 'Server Name' in the 'Add Database Reference' window. You should now be able to pick the 'booksdb' database from the list under 'Select Or Enter Database Name' (click the radio button next to this entry if it is not checked by default). Click 'OK'.
Add Database Reference
Click View -> Server Explorer. You should now be able to see the booksdb database tree. Expand stored procedures and right click to load, edit, or debug stored procedures. If you choose 'Step into Stored Procedures' after right clicking on book_orders_extended, you will be prompted for an input parameter. Enter 'a' for all authors whose last name starts with 'A'. You may now step into Stored Procedure execution line by line. This should give you an idea about the usefulness of BIDS while working with SQL Server databases.
