Software Architecture – To extract the data from a table of SQL Server and convert it to text file via SSIS in SQL Server Business Intelligence Development Studio

Objective:
Objective of this post is to extract the data from a table of SQL Server and convert it to text file having name as datetime appended via SSIS in SQL Server Business Intelligence Development Studio 2008 .

Environment:
SSIS + SQL Server Business Intelligence Development Studio 2008 + C# 4.0
Steps:

1. Create a new Package named “ExtractData.dtsx” in BIDS 2008.
2. Create a new variable with name “FileLocation” and set its value to folder name, lets say “F:\FileInbox”
3. Append variable “FileLocation” value with datetime stamp by adding a “Script Task” as:
Dts.Variables[“User::FileLocation”].Value = Dts.Variables[“User::FileLocation”].Value.ToString() + “\\data_” + DateTime.Now.ToString(“MMddyyyy_hhmmss”) ;
This command will define the name of data file to be generated.
4. Drag a data flow task on SSIS Designer.
5. On the Data Flow tab, drag and drop the “OLE DB Source” from the Data Flow Sources in the Data Flow Tool Box. Create the new OLE DB source connection manager by clicking on “New” button on the OLE DB Source Editor. Specify the command that will fetch the data from the table as:

SELECT TOP 1000 [AreaId]
,[AreaCode]
,[Description]
,[Active]
,[CreatedDate]
,[ModifiedDate]
,[BranchId]
FROM [GeneralAreaMaster]

6. On the Data Flow tab, drag and drop the “Flat File Destination” from the Data Flow Destinations in the Data Flow Tool Box. Click on New to create the New Flat File Destination Connection Manager. Configure properties accordingly.
7. So at the end of these changes, Control Flow will look like as:

And Data Flow will look like as:

8. Run the Package from Solution explorer to see the output.
9. Package can be run by following c# code.

string pkgLocation = @"F:\\e-pros\\testbest\\SSISFirst\\SSISFirst\\SSISFirst\\ExtractData.dtsx"; 
 
        Package pkg; 
        Microsoft.SqlServer.Dts.Runtime.Application app; 
        DTSExecResult pkgResults; 
        Variables vars; 
 
        app = new Microsoft.SqlServer.Dts.Runtime.Application(); 
        pkg = app.LoadPackage(pkgLocation, null); 
 
      //  vars = pkg.Variables; 
      //  vars["A_Variable"].Value = "Some value";                
 
        pkgResults = pkg.Execute(null, null, null, null, null);
 
        if (pkgResults == DTSExecResult.Success)
        {
            Console.WriteLine("Package ran successfully");
 
        }
        else
            Console.WriteLine("Package failed"); 

Related Post: https://softarchitect.wordpress.com/2010/09/18/software-architecture-sync-data-between-offlinedisconnected-databases-ssis-bids/

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: