I try to avoid doing this. But sometimes, the data processing on DB2 side is too complicated, and it’s necessary to create a procedure on DB2 to do it. Then we need to call the procedure within a SSIS package.
First, create a variable to call the stored proc on DB2 as seen below:
Then create an Execute SQL Task to actually call that SSIS variable(as seen below..settings)
If we need a config file.
If we need to embed credential in the connection string in the data source.
If we need to use Command Line (It’s an example where a SSIS package creates an Excel file.).
"D:\MSSQL2005\MSSQL$HSVR11 (x86)\90\DTS\Binn\DTExec.exe" /DTS "\MSDB\etl_Send_Excel" /SERVER lsvrname /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING V
Here are the two databases for the sample data warehouse Adventure Works.
Here are the two cubes for the sample Adventure Works.
SQL Server 2005 Developer Edition – Installation Procedure
- Install Disc 1: install default instance
- Database Services: SHERRY-PC
- Analysis Services: SHERRY-PC
- Reporting Services: SHERRY-PC\ReportServer
- Notification Services
- Integration Services: SHERRY-PC
- Remove Workstation Component before install Disc 2
- Go to Control Panel
- Go to Program Features
- Select SQL Server 2005
- Click Uninstall
- Select Workstation Component
- Install Disc 2: Tools. Install locally
- Install Business Intelligence Development Studio (BIDS)
- Install Management Studio
- Install SQL XML4
- Install Sample database
- Install SQL Server 2005 SP 2
- Download from internet
- Accept all defaults
- Install the AdventureWorks sample databases
- AdventureWorks: OLTP
- AdventureWorksDW: OLAP
- Adventure Works DW: SSAS
- Install after the Setup
- From Add or Remove Programs, select Microsoft SQL Server 2005 and click Change. Follow the steps in the Microsoft SQL Server 2005 Maintenance wizard.
- From Component Selection, select Workstation Components and then click Next.
- From Welcome to the SQL Server Installation Wizard, click Next.
- From System Configuration Check, click Next.
- From Change or Remove Instance, click Change Installed Components.
- From Feature Selection, expand the Documentation, Samples, and Sample Databases node.
- Select Sample Code and Applications.
- Expand Sample Databases and then select the sample databases to be installed. Click Next.
- To install and attach the sample databases, from Sample Databases Setup, select Install and attach sample databases, and then click Next.
- The database files are created and stored in the folder <drive>:\Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\Data. The database is attached and ready for use.
- Select the instance of SQL Server on which to install the sample databases and samples.
- Complete the steps in the wizard.
- To complete the installation of the samples, after setup, perform one of the following steps:
- From the Start menu, click All Programs, click Microsoft SQL Server 2005, click Documentation and Tutorials, click Samples, and then click Microsoft SQL Server 2005 Samples.
- Now the above has installed Adventure Works Analysis Services Project in the folder "C:\Program Files\Microsoft SQL Server\90\Tools\Samples\AdventureWorks Analysis Services Project". There you will find 2 sub-folders: "Enterprise" and "Standard". If you have SQL Server Enterprise or Developer edition, then use project from Enterprise sub-folder as it has features available just in SQL Server Enterprise Edition.
- Tip for Windows Vista users: if you are doing installation from/on Windows Vista OS, copy sample project out of "c:\Program Files" folder to folder C:\Download\SQL SSAS Project\Enterprise. In Windows Vista this folder has extra security and you might not be able to save changes in project files as files will be read-only.
- In BIDS, open the above project, and examine the design, cubes, dimensions, etc. Then Build the application. This will create the Adventure Works DW.asdatabase file in folder C:\Download\SQL SSAS Project\Enterprise\bin
- Start à All Programs à SQL Server 2005 à Analysis Services à Deloyment Wizard. Use this wizard to deploy the Analysis Services database
- Open Management Studio, log on Analysis Services, we should see Adventure Works DW is a valid AS database.
- Now we can open a new query, start to write MDX.
Report Manager (on Vista fro SQL Server 2005 Express)
Report Server (on Vista for SQL Server 2005 Express)
After finish installing the original release version of SQL Server 2005, we must apply SQL Server 2005 Service Pack 2 (SP2) or a later service pack.
Before installing SQL Server 2005 Reporting Services, I enabled Microsoft ASP.NET and Microsoft Internet Information Services (IIS). On Windows Vista, to enable ASP.NET and IIS, follow these steps:
In Control Panel, click Programs, and then double-click Programs and Features. Click Turn Windows features on or off. Then I basically turn on many features under Internet Information Services, Web Management Tools, and IIS 6 Management Compatibility, and under World Wide Web Services.
Then on Vista, do the following to start the World Wide Web Publishing service.
To configure the service to start automatically, follow these steps:
- In Control Panel, click System and Maintenance.
- Click Administrative Tools.
- Double-click Services.
- In the User Account Control dialog box, click Continue.
- Right-click World Wide Web Publishing Service, and then click Properties.
- In the World Wide Web Publishing Service Properties dialog box, click Automatic in the Startup Type list.
- Click Apply, click Start, and then click OK.
Then start to install SQL Server 2005 with Advanced Services SP2.
After installation, we need to configure the reporting server. To do this, follow these steps:
- Start the Reporting Services Configuration tool. To start the tool, click Start, point to All Programs, point to SQL Server 2005, point to Configuration Tools, and then click Reporting Services Configuration.
- In the User Account Control dialog box, click Continue.
- In the Report Server Installation Instance Selection dialog box, type the name of the computer in the Machine Name box.
- Click the instance of SQL Server 2005 Reporting Services in the Instance Name list, and then click Connect.
- Configure the server for operation.
When done, configure the report server for local administration. To access the report server and Report Manager locally, follow these steps on Vista:
- Start Windows Internet Explorer.
- On the Tools menu, click Internet Options.
- Click Security.
- Click Trusted Sites.
- Click Sites.
- Under Add this Web site to the zone, type http://ServerName. If you are not using HTTPS for the default site, click to clear the Require server certification (https:) for all sites in this zone check box.
- Click Add.
- Repeat to add the http://localhost URL, and then click Close.
Now we can start Internet Explorer together with the Run as administrator option. To do this, click Start, click All Programs, right-click Internet Explorer, and then click Run as administrator.
By default, the Report Manager URL is Http://ServerName/reports.
If you use SQL Server Express with Advanced Services SP2, the Report Manager URL is http://ServerName/reports$sqlexpress. If you use a named instance of Reporting Services, the Report Manager URL is http://ServerName/reports$InstanceName