Import Flat File to SQL Server with SSIS

In this tutorial, you will learn how to import a Flat File data to SQL Server Database using SSIS.

Example:
We have a Text File which contains Credit Card Details with Pipe Delimiter and we will import this data in SQL Server Database Table.

Prerequisite:

• SQL Server 2012 with SSIS installed:
• SQL Server Data Tools
• SQL Server Management Studio
• Text file with Credit Card Details.

Step 1: Open Notepad and copy the below Credit Card Details to the notepad file and save the file as CreditCardDetails.txt

CustomerId|CreditCardNo|TansactionType|Date|Amount C00000001|SBI000000001|DR|1/1/2016|2500.00 C00000002|CAN000000001|DR|1/1/2016|2800.00 C00000001|SBI000000001|CR|2/1/2016|25.00 C00000003|SBI000000002|DR|2/1/2016|1485.00 C00000004|SBI000000003|DR|3/1/2016|2528.45 C00000002|CAN000000001|CR|4/1/2016|14.00 C00000003|SBI000000002|CR|4/1/2016|37.13 C00000004|SBI000000004|DR|5/1/2016|1000.00 C00000005|CAN000000002|DR|5/1/2016|3000.20

Step 2: Run SQL Server Management Studio,  connect with database and run below script to create Credit Card Detail Table.

CREATE TABLE CreditCardDetails(
    CustomerId      VARCHAR(50),
    CreditCardNo    VARCHAR(50),
    TansactionType  CHAR(2),
    TransactionDate DATETIME,
    Amount          NUMERIC(18,2)
)

Step 3: Run SQL Server Data Tools.

• Create a new Project and a shared Data Connection Manager

If already created in previous tutorial(s), open the existing project SSIS-Tutorials.

Step 4: Add new package to the Project. Name the package ImportFlatFile

Step 5: Add Data Flow Task to Control Flow Tab

Double Click Data Flow Task to switch to Data Flow Tab.

Step 6: Add and Configure Flat File Source

• Add Flat File Source to the Data Flow Task from the Toolbox

• Double Click on Flat File Source. This will open a pop up window Flat File Source Editor.
• Click New Button will pop up another window Flat File Connection Manager Editor.
• In General Tab Browse the file CreditCardDetails.txt

• In Columns Tab, select Vertical Bar {|} as Column delimiter and click Refresh. This will preview the data like below

• Click OK twice to close the pop up windows.

Step 7: Add and Configure SQL Server Destination

• Add SQL Server Destination.
• Connect Flat File Source to SQL Server Destination.

• Double Click SQL Server Destination. This will open SQL Destination Editor window.
• Select the Shared Data Connection if not selected we created in Step 2.
• Select Table CreditCardDetails

• On Mapping Tab Map Input Column with Destination Column like below. Input Column and Destination column with same names are automatically mapped, otherwise you have to manually map. Here we have manually map Data and Transaction Date as the names are different

• Click OK to close the Destination Editor window. At this stage we are done with configuration of SQL Server Destination but there is a data conversion error between the source and destination data types.

• All the columns that we are loading from Flat file have string type but in SQL Server Table there are two columns which have DateTime and Numeric Data Type. For this we need to place Data Conversion Transformation between the Source and Destination to make it compatible

Step 8: Add and Configure Data Conversion Transformation

• Delete the connector between Flat File Source and SQL Server Destination
• Add Data Conversion Transformation
• Connect Flat File Source to Data Conversion
• Connect Data Conversion to SQL Server Destination

• Double Click Data Conversion and configure like below.

Step 9: Update mapping in SQL Server Destination 

• Double Click SQL Server Destination and select Mapping Tab
• Update the mapping for Date and Amount Columns like below

We are done with creating the package. Now let's run and test the package

Package executed successfully and transmitted 9 rows to SQL Server Destination Table.

Now let's run a query in SQL Server Management Studio and check the table

SELECT * FROM CreditCardDetails

Sum up Time Field in SQL Server

Yesterday, one of a member Murali Krishna Rayudu in a Facebook Group MSBI Query Cracker's post the requirement to calculate Total Working Hours. In the scenario there was a column with Time Data Type and had the values for hours worked and the requirement was to sum up the time to calculate Total Hours worked. At first go, it looks straight forward as we have aggregate function  SUM to sum up column values, but it's not because sum of a column with TIME data type is not supported in SQL Server

NOTE: Time Data Type was introduced in SQL Server 2008

Let's try to sum up the values of a column with Time Data Type with SUM

declare @tab table(col time)
insert into @tab values
('01:15:23'),('12:15:41'),('15:45:12')
select SUM(col) from @tab

Look, we get the above error. So, it is concluded that aggregate functions not work on Time Data Type in SQL Server.

Solution to above I provided the following script to calculate the same on the post itself and thought of sharing the same on by blog. In the below script I have provided two ways.

declare @tab table(col time)
insert into @tab values
('01:15:23'),('12:15:41'),('15:45:12')

--Solution-1
select right('0'+cast(sum(DATEPART(hh,col)) + (sum(DATEPART(mi,col)) + (sum(DATEPART(S,col))/60))/60 as varchar),2)
       +':'+right('0'+ cast((sum(DATEPART(mi,col)) + (sum(DATEPART(S,col))/60))%60 as varchar),2)
       +':'+ right('0'+ cast(sum(DATEPART(s,col))%60 as varchar),2)
from @tab

--Solution-2
select cast(secs/3600 as varchar)
       +':'+cast((secs-(secs/3600)*3600)/60 as varchar)
       +':'+cast(secs%60 as varchar)
from( 
   select sum((DATEPART(HH,col)*3600)+(DATEPART(mi,col)*60)+DATEPART(S,col)) secs
   from @tab
) t

Filter Parameter in SSRS

In this article you will lean what is Filter Parameter in SSRS and how it works with an example.

Filter Parameter(s) are used to filter the result on the report once the data is fetched from the database. It is alternate to Query Parameter to filter the result, but not efficient as it filters the data on the front end which lead to fetching all the data from the database and increasing the network flow

Let's create a report to see how Filter Parameter works.

Step 1: Create a simple Tabular Report

• First Create a simple Tabular Report using above link. Once report is created we would apply Filter Parameter on the report.

Step 2: Add and configure Parameter

• On Report Data Tab, Click Parameters and Add Parameters

 

• Configure Report Parameter properties as below and click OK

• This will add GroupName Parameter under Parameters like below.

Step 3:  Add Filter Parameter

• In Report Data Tab right click DataSet1 under Datasets and then click Dataset Properties

• Select Filter Tab and Add Filter. Click fx and select Parameter GroupName as value in the pop up window.

We are done with adding Filter Parameter to the Report.

Now let's build the Solution and run the report. The Parameter GroupName that we created in step 2 is appearing in the report to filter the result set.

Enter Manufacturing in the text-box and click View Report

Look at the output, the report data is filtered with Group Name as Manufacturing