Archive for the 'SQL server' Category
Creating an Analysis Services Project in Visual Studio 2008
Microsoft SQL Server Analysis Services allows the developers to provide a unified access of data for analytical analysis and reporting. Developers can manage and aggregate the queries from multiple subject areas. This can be achieved by creating a cube in Visual Studio 2008. This post defines and explains the terms used in creating an analysis project in VS 2008.
Related Posts:
No commentsTable Expressions in SQL server
Table Expressions are sub-queries where a table is expected. There are two types of table expressions. This post explains how to use the Table expressions and their advantages.
- Derived tables
- Common Table Expressions
Related Posts:
No commentsSQL server Joins or Subqueries?
Select statements that use join operator and join statements can be written in Subqueries and similarly Subqueries can be written in Joins. Writing the Select statements with Join operator can help database engine to find more efficient strategy for retrieving the appropriate data. However each having their own advantage over other. Some problems can be easily solve with Subqueries and some can be solve with Joins. More about SQL Joins can read here.
Related Posts:
No commentsSQL Server Reports Integration with SharePoint 2007
This post explains how to Deploy the SQL server Reports to Office SharePoint Server 2007. SQL Server Reporting Services can operate in two modes, either in Native Mode or Share Point Integration Mode.
Related Posts:
No commentsReport Builder 2.0 in SQL Server 2008
Report builder 2.0 is a report authoring tool that we can use to design and publish reports. We can specify the data source, what data to display on report and which layout you prefer to see the report. When you run the report, the report processor takes all the information you specified and retrieves the data and generates each page as you view it. This post explains step-by-step details of creating the report using Report Builder 2.0.
Using Report Builder you can
Related Posts:
No commentsSSIS Package Configuration in SQL server 2008
SSIS configuration wizard allows you to create configurations for packages. It also allows you to update the properties and objects of the package at run time.
Package Configuration Benefits
- Configuration really helps the developers to smoothly move the packages from Development environment to Production environment.
Eg: Configuring the source path of the file or updating the database
connection string.
- Configurations makes it easier when you deploy the packages over different sql servers.
- Using property expressions we can update the value of variable in configuration. More about this can be read here
1. Open the Business Intelligence Development Studio from Start->Programs
Related Posts:
No commentsRuntime Settings with SSIS Package
Introduction
Clients may require to integrate the SSIS packages in their environment(Production). SSIS package settings needs to be changed at run time and all settings needs to be pulled from the database using set of stored procedures.
1. Create a SSIS package and drag the Execute SQL task to control flow in the package as follows
Related Posts:
1 commentETL Process using SQL server SSIS
This post explains about creating a SSIS package for demonstrating the small ETL process using transforms and connections in SQL server 2005.
1. Create a new SSIS package and drag a DataFlow task onto the control flow. Double click the task to go to the Data Flow tab.
2. In the Data Flow tab, drag an OLEDB Source onto the design pane. Select the data connection to Adventure Works Database.
3. Make sure that the Data Access Mode option is set to “Table or View”. Select the [Production].[TransactionHistoryArchive] table from Table drop-down box.
4. Select the ProductID,Quantity and ActualCost columns and say OK to exit the editor.
5. Drag a Derived Column transform to dataflow and connect the dataflow task to this task.
6. Double-click on the Derived Column transform to open the editor and type the new column with name TotalCost and configure the column as follows and click ok to exit the editor.
7. Drag an Aggregate transform onto the data flow and connect the arrow from Derived Column transform onto this transform. Double click the editor to configure the properties.
8. Drag a Sort transform onto data flow pane, connect Aggregate transform to this transform by the green arrow. Double-click on transform to configure the properties. Click OK to exit the editor.
9. Now we export this data to a flat file so that interested parties can read the file. Drag a Flat File dataflow to pane and connect the Sort transform by using the green arrow.
10. Double click on Flat File transform to get the following editor.
11. Now execute the package and you should be see the rows flow through the package as shown below
Conclusion
In this post, we have seen how transformations in SSIS allows us to change the data from a source and pass the results as output to a destination or another transform.
Related Posts:
1 commentUsing File System Task and FTP Task in SQL server SSIS
Tasks are fundamental units in SSIS control flow. Every task in SSIS needs to be configure with required fields. This post going to explain how to use FTP and File System task. You can use the File System to automate data load process.
Consider a scenario where you receive a file from source system via FTP and your package needs to be react by moving it to another working directory.
1. Create a new SSIS package using BIDS and drag the FTP task to the design pane.
2. Double click the task to set the properties
Type a name for the task and set the FTPConnection properties as shown above.
Go to File Transfer page and select the value for remoteVariable. Click Ok to exit the editor. After file being downloaded then you can copy to the local folder for achieving. To copy the file into an archive directory, we will use the File System task. Drag the task to designer and configure the properties.
In the control tab, connect the FTP file task to the File System task with a precedence constraint. The file will be FTPed first and then copied to the archived folder.
Related Posts:
No commentsCreating a SSIS package in VS 2008
This post explains in detail about creating a SSIS package in VS 2008, project folder structure and designer. You can read this post to get understanding on SSIS.
BIDS(Business Intelligence Development Studio) can be found after installing the full version of SQL server 2005 or 2008. I am using the 2008 version for this post. The SSIS development environment is detached from SQL server and you can develop the package offline then can be deployed to the server.
BIDS can be found in the SQL server 2008 group as shown below
1. To start a new SSIS project, first you need to open BIDS and select File —-> New —–> Project
2. In the solution explorer you will notice an empty package called package.dtsx was created.
3. Drag the Execute Process Task to designer from tool box. Double click the task to configure it. Name the task Notepad and browse the file location for executing the task.
4. Drag another Executable Task to designer and double-click on it to open the editor. Name the task calc and browse the exe file for executing the task.
These tasks are now connected, and the calc task will not execute until the first task succeeds.
5. Save the project and run it you were able to see the notepad. After closing the notepad you will get the calc. After execution of this package the tasks should show as green in color, which means successfully executed.
If you look into the directory that contains your solution , you were able to see
- .dtsx – A SSIS package
- .ds – A shared data source file
- .dsv – A data source view
- dtproj – A SSIS project file
The toolbox will look like the same
![Recommend [kalyanms1]](http://s3.amazonaws.com/arkayne-media/img/badge/02me.png){kind=icon}
