yourlastname_yourfirstinitial_BCS425_Assignment2.docxStudent Name
Student ID 2
Assignment 2 – XYZ Corp.
Submitted By
Course
Professor
Date
Introduction
We have given the scenario of XYZ Corp. that operates with two branch offices – New Jersey and New York. The organization wants to merge data from all sources into one centralized location. For this we used SQL Server Integration service (SSIS) that will merge data from various sources to one destination. The main objective of the project is to gather/ merge all data sources into one destination (SQL Server) which is centralized server for the organization.
SSIS Service
SQL Server Integration Service (SSIS) is the Microsoft SQL Server's component which is mainly used to migrate a large number of data sets. This merge can be from multiple files whether it is text, CSV or Excel file. It is fast as well as a flexible data warehouse tool that is used for data extraction, data loading and various data transformation tasks whether it's cleaning, aggregating or merging. With the help of SSIS data migration from multiple sources to SQL Server becomes easy. It can also include various graphical tools as well as many wizards that can be helpful in performing workflow functions such as sending emails, FTP operations, data source as data destination.
About the Project
In this project, we have given the data in two different types of files. One is text and other is csv file which is similar to txt or flat file. We have created a package named NewPackage.dtsx. We have used ForEach Loop container that can run every file in its background. In Foreach loop container, we have used two tasks – Data Flow task and File system task. Bother are connected with each other. Data Flow task will analyse the actual flow of data whereas file system task will collect all data from multiple source files.
On the other hand, we have created a table with similar table structure in SQL Server Management Studio. As we are using our project to be run dynamically as any number of files can be taken as source file, therefore, we have created two variables – CurrentFile and FlatFileDestination. Both will be of type String. CurrentFile will read data from the source files whereas FlatFileDestination will take the data to the destination which is SQL Server table. The path chosen in case of FlatFileDestination is the path of the folder where we have saved our source data files. In our case, I have created a folder Source on the the desktop and copied both data files in this folder.
In case of Data Flow File, we have to select the source data from which data will be copied to destination folder. Hence, we have taken one flat file source in this and selected any of the source file by browsing from File Name. In case of Advanced tab, we have changed the data type to int and decimal of AccNo, ZipCode and CreditLimit as these are numeric in case of Destination.
Flat Source File will then be connected to ADO Net Destination. Here, we have to select database server name in the connection. Then select the database in which we have to copy the data. Select the table from the list. In case of mapping, select the source column names from the list, so that destination columns will be automatically mapped. Leave CustomerKey blank.
After selecting source and destination locations in Data Flow and Flat File Task, we have to edit the properties of Foreach Loop. In the collection tab, select the source folder and in the Files tab enter ExcelData*.*. I have selected ExcelData as I have changed both file names to ExcelData with file extension .txt and .csv files.
In case of Mapping, change variable to CurrentFile. After clicking on File system task, we have to make some changes in the properties. We have selected Destination variable to FlatFileDestination and changed OverwriteDestination to Yes. Tis is because no error will be generated and the data will be overwritten.
Then we have executed our package. It run successfully. We can check our data in SQL Server as the table will be loaded with same data.
LastnameFirstname_BCS425_Assignment2/Integration Services Project1/bin/Development/Integration Services Project1.ispac
Package.dtsx
8
NewPackage.dtsx
8
C:\Users\Shikha\Desktop\Destination
"dbo"."DimXYZCustomers"
0
30
true
Microsoft.ADONETDestination
false
false
false
false
false
false
false
false
false
false
false
false
false
false
false
false
false
false
false
Version="sql12">
design-time-name="Package">
Capacity="4" xmlns="clr-namespace:Microsoft.SqlServer.IntegrationServices.Designer.Model.Serialization;assembly=Microsoft.SqlServer.IntegrationServices.Graph" xmlns:mssgle="clr-namespace:Microsoft.SqlServer.Graph.LayoutEngine;assembly=Microsoft.SqlServer.Graph" xmlns:assembly="http://schemas.microsoft.com/winfx/2006/xaml">
Size="150.4,41.6"
Id="Package\Foreach Loop Container\Data Flow Task"
TopLeft="44.9142898306555,6.54117788625011" />
Size="156.8,41.6"
Id="Package\Foreach Loop Container\File System Task"
TopLeft="39.2000040190561,83.6235319084775" />
Id="Package\Foreach Loop Container.PrecedenceConstraints[Constraint]"
TopLeft="118.857146924856,48.1411778862501">
StartConnector="{assembly:Null}"
EndConnector="0,35.4823540222274"
Start="0,0"
End="0,27.9823540222274">
Capacity="5">
End="0,27.9823540222274" />
HeaderHeight="43"
IsExpanded="True"
PanelSize="281.6,181.6"
Size="281.6,224.8"
Id="Package\Foreach Loop Container"
TopLeft="196.57143191902,51.3882360148182" />
design-time-name="Package\Foreach Loop Container\Data Flow Task">
Capacity="4" xmlns="clr-namespace:Microsoft.SqlServer.IntegrationServices.Designer.Model.Serialization;assembly=Microsoft.SqlServer.IntegrationServices.Graph" xmlns:mssgle="clr-namespace:Microsoft.SqlServer.Graph.LayoutEngine;assembly=Microsoft.SqlServer.Graph" xmlns:assembly="http://schemas.microsoft.com/winfx/2006/xaml">
Size="150.4,41.6"
Id="Package\Foreach Loop Container\Data Flow Task\Flat File Source"
TopLeft="266.285718820533,36.7058828677273" />
Size="179.2,41.6"
Id="Package\Foreach Loop Container\Data Flow Task\ADO NET Destination"
TopLeft="322.285719774207,135.811766610591" />
Id="Package\Foreach Loop Container\Data Flow Task.Paths[Flat File Source Output]"
TopLeft="341.485718820533,78.3058828677273">
StartConnector="{assembly:Null}"
EndConnector="70.4000009536743,57.5058837428638"
Start="0,0"
End="70.4000009536743,50.0058837428638">
Capacity="5">
End="0,24.7529418714319" />
Point1="0,24.7529418714319"
Point2="0,28.7529418714319"
Point3="4,28.7529418714319" />
End="66.4000009536743,28.7529418714319" />
Point1="66.4000009536743,28.7529418714319"
Point2="70.4000009536743,28.7529418714319"
Point3="70.4000009536743,32.7529418714319" />
End="70.4000009536743,50.0058837428638" />
design-time-name="Package\Foreach Loop Container\Data Flow Task\ADO NET Destination">
DataSourceViewID
Project.params
@Project.manifest
{b07348fd-db43-426b-953c-67712fe612b2}
Integration Services Project1
1
0
0
2020-02-23T07:34:16.8772592+05:30
LAPTOP-ICKIMO71\Shikha
LAPTOP-ICKIMO71
AQAAANCMnd8BFdERjHoAwE/Cl+sBAAAAXgEH8fWeD0CzhCwPYGI+vwAAAAACAAAAAAAQZgAAAAEAACAAAAAfLtsuEylYZPddw3yQhN03gx1nuBWefwG4pIuKV5HODAAAAAAOgAAAAAIAACAAAADVBWvthG4REcGRd2Pp6Lq5WUgOr0RYhYxg3x7IH39BNZAAAACuErE9ltW7u0vUw1hvWiIBRlyooQfp7jAUjB13VhPmo1pK4vSNmzBpGVsLXDHVJ5h4A5f/jF22NE/7t2RYYvLpmZoTNoF+V5uf2EEKTLuY1sbVgbPMGCoWJcnogFbaKTwCBkj/6dc4rCKorNf93DhaeqoGrbEwF+jZjwCDLVZ4tqT9KjZOs/XwfXkum2/DEc1AAAAAcZhQ54spHVRKT+Ph8+Kzy+lMfyn2tDxXTMeaaYhViworvyBkokgV7T1/8w700h5ho6U4ZJifzQ73Cukxu8jmAQ==
1
{4AA706AE-5E70-423D-8D09-6291CFA8E1CB}
Package
1
0
0
{DFC08BCA-D001-406E-9E1C-635EC724AA70}
8
1
{3015FD6A-29B8-4FD8-8C25-35105D982A3D}
NewPackage
1
0
1
{A640E975-469A-4C7B-892E-9E48975AF6CE}
8
1
0
0
0
true
3
0
0
0
1252
9
0
0
0
true
3
0
0
0
C:\Users\Shikha\Desktop\Source\ExcelData.txt
18
0
0
0
0
9
0
0
0
Delimited
18
...