Create your First SSIS Project Step by Step
SSIS as an “outdated” ETL tool, is still massively used by organizations. Today we are going to install SSIS and build the first SSIS project to bring any text file in a folder into our MSSQL database.
First of all, please leave a thumb and follow me for more “straight to work” articles.
Second of all, don’t get frustrated if VS 2019 crashes on your Windows 11.
Installation of Visual Studio and SSIS
- Visual Studio Community 2019(Free) download link: Click Here
It seems like the SSIS extension is not available on the 2022 version yet.
2. After the download: Create a new project -> search blank project
Navigate to the menu bar: extension -> Search SSIS and it will bring you to the SSIS installation page.
IMPORTANT: CLOSE VS WINDOW BEFORE INSTALLING SSIS EXTENSION
3. After SSIS installation, reopen VS, create a new Project, and search Integration Services to create the project.
4. Create a text file and copy the data source from below.
Name, Rating, Num_People_Read,Category,Date
DynamicReader,3.5,268,Books,712014
DynamicReader,3.5,268,Books,712014
DynamicReader,3.5,268,Books,712014Create a table with column names and types as nvarchar(50) for convenience.
5. Right-click Connection Manager and add a new connection: ABO.NET.
C# and VB are supported languages in VS. You can run Python or R script as well, which will require CMD to play the role instead of native features in SSIS (Hooray Microsoft Family). Use windows authentication or service account to establish the Server and Database connection. Rename the connection to DBConnection(so I can mask server in the code, thanks).
6. Control Flow -> Right Click Canvas ->Variables -> Add Variables Icon(YES THEY DON’T HAVE ICON HINT BUT IT IS THE FIRST ONE).
Var1: Filepath, String, YOUR_FILE_PATH
Var2: TableName, String, YOUR_TABLE_NAME
7. Right-Click canvas and add a Script Task. Edit the task and embed the two variables we created into ReadOnlyVariables.
8. Replace the main function with the following code (with comments of course).
https://gist.github.com/zhangzixuan1997/e850c1fc904b470769a9e578cdaab5379. Save and execute the Script Task.
10. SSMS check:
