Sunday, May 13, 2012

Creating File System Task with SQL Server Integration Services (SSIS)


You can create SSIS package to perform file related operations like copy file or directory, create file or directory, delete file or directory, rename file etc. You just need to drag File System Task item from tool bar to control flow tab or simply double click on File System Task item from tool bar. Double click on File System Task item it will open editor dialog window. You can set source and destination connection properties based on selected file operation.  You can see below the list of various file operations available to perform with file system task.  



Below is the File System Task editor dialog which displays properties to be set to perform particular task. The available tasks for File System Task are displayed in above image.



As per selected operation, you need to specify source and destination connection or only specify source connection. You need to specify either existing connection or create new connection. Below is the new file connection dialog.



You can select usage type as create file, create folder, existing file or existing folder. Specify file name with folder location. After setting all the parameters, you can execute package and your selected file operation successfully completed.

Below is the video which explains how to create and execute file system task with different file operations.




See Also –


Monday, April 30, 2012

Creating simple SSIS package using SQL Server Import Export Wizard


SSIS (SQL Server Integration Services) is excellent component comes along with SQL Server. SSIS is used to perform data migration task. This is also called as ETL (Extraction, Transformation and Loading) tool. In Data warehousing applications, SSIS is used to perform various operations like importing/exporting data from different sources, perform transformation on data, doing calculation etc.

To start with SSIS, Business Intelligence Development Studio must be installed on your machine and make sure that SQL Server Integration Service is running (Start->Run-> services.msc). After that you need to create SSIS package first. Package is collection of control flow elements, data flow elements, events handlers, variables and configurations. Package is unit of work which can be debugged, executed and saved.

In this post, I will explain how to create simple SSIS package using import export wizard. SSIS has many other features which I will explain in my later posts.

Below are the steps and screenshots to create SSIS package using Import Export wizard.

1. Open Import Export Wizard from Start -> SQL Server -> Import Export Data or Open SQL Server Management Studio and write click on database from which you want to export/import data select task and Export Data.




2. SQL Server Import and Export wizard opens


3. Select source of data. (In below image I have selected SQL Server Data Source and AdventureWorks2008R2 database from where I want to export)



4. Select destination (In below image I have selected Flat File as destination there are many other options available you can select as per your requirement. You need to provide flat file name and if you want to see column name on first row check below displayed checkbox.).



5. Select Table (you can select one or more table to export data or write your own query)
  


6. Select Table from where you want to export data with row delimiter and column delimeter.


Click on Edit Mappings button to select which column data you want to export at your destination file.

Click on preview to see your data



7. Click on Save SSIS Package and File System.


8. Specify the location and name where you want to save SSIS package in local drive.

9. Verify the choices made in wizard and click on finish.



10. Package will start executing and after completion of execution check where package executed successfully or fails.



As per above image, package executed successfully and 504 rows transferred to flat file. See below image which shows column name in first row and below comma separated data.



The similar way you can import data from any source to any destination using Import and Export wizard.

Let’s have a look on created SSIS package. SSIS package has .dtsx extension. In above exercise we have created package named ProductPackage.dtsx. Double clicked on this file and it will open in Visual Studio see below screenshot.

This image shows one data flow task created on control flow tab.


On double click that task you can reach to Data Flow tab and it shows how data transfers from source to destinations.




You can also watch below video which demonstrate above mentioned all the steps and explains how create simple SSIS package using import export wizard.






Hope you liked this post. Please leave your feedback in comments section.


Tuesday, April 17, 2012

How to use Pre-build and Post-build Events in Visual Studio?



Many of you might aware about Build Events feature of Visual Studio. In this post i will explain little bit about Pre-build and Post-build events and their usage.

Sometimes you might need to execute some additional tasks just before and after building your Visual Studio solution. So for this Visual Studio provides nice way to do that using Build Events. You can specify commands inside pre-build and post-build command line. You can find build events tab inside project properties.


As mentioned in above image, you can specify almost all DOS commands inside pre-build and post-build event as per your requirement. Post-build event gives you more control over Pre-build command line execution. You can decide when to execute post-build event from given three options displayed in above image.

Let’s try to understand with simple example.



On successful build I want to copy my executable to some specific folder on my local drive. So in above example I have used xcopy command to copy files from one directory to another. In above example I have used ‘$(ProjectDir)’ macro which will gives me the current project directory. There are more macros available to use for various purposes see below image. These macros can be used to specify location of project or solution, project name, extension, output directory, configuration name etc.



You can use this feature in many scenarios like processing or executing batch file, copy assemblies to specific location, generate resources file using tool like resgen.exe etc. This is really nice and useful feature provided by visual studio.

Hope you liked this small tip. Please leave your feedback in comments section.


See Also – 

Tuesday, April 10, 2012

How to modify/create build configurations in Visual Studio?



In project life cycle you might need to create or access various builds like Development, Testing, User Acceptance Testing, Production etc. Visual Studio provides Configuration Manager Toolbar to change, create or access builds. By default solution has two builds Debug and Release.

Typically debug build is used to debug and detect errors (compile-time and runtime) from solution or project. Once project or solution development is complete and ready to deploy than project or solution should be built in Release mode. Release builds enables code optimization and enables fast execution of program.

You can also change or create new configuration using configuration dropdown available on standard toolbar.


For add or change configuration you can select configuration manager from any of the drop down. If sometimes configuration or platform drop downs are not visible on standard tool bar of visual studio in such case you can add those externally by clicking on customize menu item of tools menu.


The Configuration Manager Dialog will appear.


Similarly you can open Configuration Manager Dialog from right click of your solution and click on Configuration Manager (below image).

You can change or edit configuration and platform for each project under solution.


The same configuration used to develop application to choose correct config file or connection string or other configuration specific task. Let’s have a look on below code.

#if DEV
        Console.WriteLine("DEV Configuration...");
#elif QA
        Console.WriteLine("QA Configuration...");
#endif

As per above code, based on selected configuration either DEV or QA the appropriate code will execute. One more thing, by default Debug and Release configuration added with project and during compilation it will create Debug and Release folder under Bin folder located at solution folder. If we added new configurations in our case DEV and QA then respective folder will get created under solution’s Bin folder and respective resource and assembly will be added to that.



Monday, April 9, 2012

How to build Solution or Project from command line (Batch File)?


Sometimes you might need to build multiple solutions or complex project in particular sequence. In such case you can build your project or solutions using command line or batch file. In this post I will explain simple way to build project or solution using command line tool or creating batch file.

Devenv allows you to build your solution or project using command line. It also allows related multiple operations such as clean solution, rebuild solution or deploy solution etc. To execute commands, you need to open Visual Studio command prompt available inside visual studio tools under all programs of start menu. You can find multiple options available with devenv using below command.

Devenv /?

Let’s start with simple example which builds solution using command prompt.

Devenv  TestConsoleApplication.sln /Build Debug



The above command builds TestConsoleApplication solution in Debug configuration mode. To build this solution with Release mode you just need to write Release instead of Debug in above code.

Similarly you can clean and rebuild solution using below code.

Devenv  TestConsoleApplication.sln /Clean

Devenv  TestConsoleApplication.sln /Rebuild Debug

We can also build specific project with project build configuration along with solution.

Devenv  TestConsoleApplication.sln /Build Debug /Project TestConsoleApplication/TestConsoleApplication.csproj /ProjectConfig Debug


Below command opens solution from command prompt and runs the application. If any exception occurred while running application it will log the same in MyErrorLog.txt file.

Devenv /Run TestConsoleApplication.sln /out D:/Temp/MyErrorLog.txt

The above command runs the application and log exception if any into separate file mentioned with /out switch.

We can also reset Visual Studio’s setting using below line of code. This is nice and great feature which can be useful when something is missing from IDE or IDE is not working property. This will restore back all the default settings of Visual Studio’s IDE.

Devenv /ResetSettings

MSBuild.exe

For build related task, it is recommended by Microsoft to use Msbuild.exe. Msbuild.exe builds project or solution with specified options. You can find multiple options using below help command.

msbuild.exe /?

To build solution using msbuild you can use below line of code.

MSBuild TestConsoleApplication.sln /p:Configuration=Release /p:Platform=”Any CPU”


You can also perform multiple task like Rebuild, Clean etc using MsBuild.

You can also create batch file to build your application. Below is the sample code of batch file to build your application using devenv.

BuildSolution.Bat
@Echo OFF
Echo "Building solution/project file using batch file"
SET PATH=C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\
SET SolutionPath=d:\Temp\TestConsoleApplication\TestConsoleApplication.sln
Echo Start Time - %Time%
Call Devenv %SolutionPath% /Build Debug
Echo End Time - %Time%
Set /p Wait=Build Process Completed...


You can also create batch file to build solution using MSBuild.exe file similar above mentioned batch file with just little change.

BuildSolution.Bat
@Echo OFF
Echo "Building solution/project file using batch file"
SET PATH=C:\Windows\Microsoft.NET\Framework\v4.0.30319\
SET SolutionPath=d:\Temp\TestConsoleApplication\TestConsoleApplication.sln
Echo Start Time - %Time%
MSbuild %SolutionPath% /p:Configuration=Release /p:Platform="Any CPU"
Echo End Time - %Time%
Set /p Wait=Build Process Completed...


See Also - 

Sunday, March 25, 2012

WPF Page Navigation



WPF provides support for browser style navigation inside standalone application using Page class. User can create multiple pages, navigate between those pages along with data.There are multiple ways available to Navigate through one page to another page.

Page can be implemented as root element in Xaml file and can contain single element similar to window.

<Page x:Class="WpfApplication1.Page1"
      xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
      xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
      Title="Page1">
      Welcome to WPF Page Navigation.
</Page>

Here you noticed that instead of Window element Page element is the root element. Similar to normal xaml file you can specify page name StartupUrl inside App.xaml to open specific page.

<Application x:Class="WpfApplication1.App"
             xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
             xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
             StartupUri="Page1.xaml">
</Application>

Navigate to another page using hyperlink

The simplest way to navigate from one page to another page is using hyperlink. Hyperlink requires two things one is the URL or page name to navigate and another is content on which user can click and navigate to page. Let’s have a look on below code snippet.

Page1
<Page x:Class="WpfApplication1.Page1"
      xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
      xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
      Title="Page1">
    <StackPanel>
        <TextBlock>
            Go to <Hyperlink NavigateUri="Page2.xaml"> Page 2 </Hyperlink>
        </TextBlock>
    </StackPanel>
</Page>


Page 2
<Page x:Class="WpfApplication1.Page2"
      xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
      xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
      Title="Page2"> 
    <StackPanel>
        <TextBlock Margin="10">Welcome to Page2.</TextBlock>
        <TextBlock Margin="10">
            Go back to <Hyperlink NavigateUri="Page1.xaml"> Page 1 </Hyperlink>
        </TextBlock>
    </StackPanel>
</Page>


Navigate to page using Navigation Service

Alternatively you can use navigation service implemented with Page class to navigate to another page. The navigate method of navigation service enables the current page to be changed to another page. This method accepts page name or URI.

<Page x:Class="WpfApplication1.Page1"
      xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
      xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
       Title="Page1">
    <StackPanel>
        <TextBlock Margin="10"> Welcome to Page 1.</TextBlock>
        <TextBlock Margin="10">
            Go to <Hyperlink Click="Hyperlink_Click"> Page 2 </Hyperlink>
        </TextBlock>
    </StackPanel>
</Page>

private void Hyperlink_Click(object sender, RoutedEventArgs e)
{
    Page2 p2 = new Page2();
    this.NavigationService.Navigate(p2);
}

In above example, navigate method has been used to navigate to page2. Navigation service is implemented in Page class by default. So In above example instead of NavigateUri property of hyperlink click event is used to navigate to page2.

You can also navigate to an external or internal page by specifying URI in navigate method. The below line of code will open Google page.

this.NavigationService.Navigate(new Uri("http://www.google.com"));

Navigate to page using Journal

Journal keeps history of navigated page and user can also navigate Back, Forward using journal. User can navigate journal using xaml code as well code behind. Let’s have a look on below code.

<StackPanel>
    <TextBlock Margin="10">
        Navigate to <Hyperlink Command="BrowseBack">previous page. </Hyperlink>
    </TextBlock>
    <TextBlock Margin="10">
        Navigate to <Hyperlink Command="BrowseForward">next page. </Hyperlink>
    </TextBlock>
</StackPanel>


or

this.NavigationService.GoForward();
this.NavigationService.GoBack();

Navigating between pages inside Frame

Sometimes, application might not need whole page hosted as navigation window but it requires some small part inside window, in such cases we can use frame control to host page navigation framework. Frame class provides inbuilt support NavigationFramework. Let’s have a look on below code.

<StackPanel>
    <TextBlock Margin="10">This is Main Window.</TextBlock>
    <Frame Margin="10"
        Source="Page1.xaml"
            JournalOwnership="OwnsJournal"></Frame>
</StackPanel>


Above example demonstrate frame control which contains Page. Frame control is capable to hold page and it also support navigation. Frame also maintains its own journal if JournalOwnership property of Frame control set to “OwnsJournal”. If you specify “UsesParentJournal” then it will use parent page’s journal.

Passing data between pages

There are multiple ways available to pass data between pages using page navigation. The simplest and easiest way to pass data between pages using page's constructor.

Page2 p2 = new Page2("This is Page2");
this.NavigationService.Navigate(p2);

public Page2(string message)
{
     Console.WriteLine(message);
}

Another way to pass data is you can send extra data with Navigate method of NavigationService and get extra data on target page.

Page1
private void Hyperlink_Click(object sender, RoutedEventArgs e)
{
    Page2 p2 = new Page2();
    this.NavigationService.Navigate(p2,10);
}

Page2
void Page2_Loaded(object sender, RoutedEventArgs e)
{
    NavigationService ns = this.NavigationService;
    if (this.NavigationService != null)
        this.NavigationService.LoadCompleted += new LoadCompletedEventHandler(NavigationService_LoadCompleted);
}
void NavigationService_LoadCompleted(object sender, NavigationEventArgs e)
{
    if (e.ExtraData != null)
        Console.WriteLine(e.ExtraData);
}

In above example, data has been pass to Navigate method on page 1 and the same handled and retrieved on page 2 using NaivgationService’s LoadCompleted method.

Another way to keep data shred across multiple pages is using Application object’s property collection which stores data shared across pages.

Page1
Page2 p2 = new Page2();
Application app = Application.Current;
app.Properties["PageData"] = 10;
this.NavigationService.Navigate(p2);

Page2
Application app = Application.Current;
if (app.Properties["PageData"] != null)
    Console.WriteLine(app.Properties["PageData"].ToString());

Returning data back from page with PageFunction

Sometimes you might require some user input for particular action so to achieve that you might need to create one user input page and return data. But WPF has inbuilt facility to do the same using PageFunction. PageFunction is special class which allows user to return data back to caller page.

Let’s have a look on below code to get some more idea on PageFunction class.

Page1
XAML
<Page x:Class="WpfApplication1.Page1"
      xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
      xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
       Title="Page1">
    <StackPanel>
        <TextBlock Margin="10"> Welcome to Page 1.</TextBlock>
        <TextBlock Margin="10">
            Go to <Hyperlink Click="Hyperlink_Click"> data entry page </Hyperlink>
        </TextBlock>
        <TextBlock Name="lblName" Margin="10"></TextBlock>
    </StackPanel>
</Page>

Code
private void Hyperlink_Click(object sender, RoutedEventArgs e)
{
    DataEntryPage userInputPage = new DataEntryPage();
    userInputPage.Return += new ReturnEventHandler<string>(userInputPage_Return);
    NavigationService.Navigate(userInputPage);
}
void userInputPage_Return(object sender, ReturnEventArgs<string> e)
{
    lblName.Text = string.Format("Your name is {0}", e.Result);
}

Data Entry Page
XAML
<PageFunction
    xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
    xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
    xmlns:sys="clr-namespace:System;assembly=mscorlib"
    x:Class="WpfApplication1.DataEntryPage"
    x:TypeArguments="sys:String"
    Title="DataEntryPage">
    <StackPanel>
        <TextBlock Margin="10">Welcome to Data Entry Page.</TextBlock>
        <WrapPanel>
            <TextBlock Margin="10">Enter your Name:</TextBlock>
            <TextBox Name="txtName" Height="25" Width="150" />
        </WrapPanel>
        <TextBlock Margin="10">
        Return back to <Hyperlink Click="Hyperlink_Click"> Page 1 </Hyperlink>
        </TextBlock>
    </StackPanel>
</PageFunction>

Code
public partial class DataEntryPage : PageFunction<String>
{
    public DataEntryPage()
    {
        InitializeComponent();
    }
    private void Hyperlink_Click(object sender, RoutedEventArgs e)
    {
       OnReturn(new ReturnEventArgs<string>(txtName.Text));
    }
}

Output
Page1

Data Entry Page


Return Back to Page1 with Data



As per above output, When user clicks on goto data entry page link from page1 it will redirect to data entry page. User enters name in textbox and clicks on return back to page1 hyperlink it will redirect back to Page1 with entered name.

So Data entry page implements PageFunction class which is generic type where the type argument represents return type.

public partial class DataEntryPage : PageFunction<String>

<PageFunction
    xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
    xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
    xmlns:sys="clr-namespace:System;assembly=mscorlib"
    x:Class="WpfApplication1.DataEntryPage"
    x:TypeArguments="sys:String"
    Title="DataEntryPage">
</PageFunction>


OnReturn method of PageFunction class automatically returns the data or value to the caller page. In above example it will redirect back to Page1 with data.
private void Hyperlink_Click(object sender, RoutedEventArgs e)
    {
    OnReturn(new ReturnEventArgs<string>(txtName.Text));
    }

On caller page, we need to handle the return value coming from data entry page. For that we need to register and handle return event. Return value can be retrieved from e.Result property.

void userInputPage_Return(object sender, ReturnEventArgs<string> e)
{
    lblName.Text = string.Format("Your name is {0}", e.Result);
}


See Also –