- Converting the SSIS to SSIS2016+
- Using SSIS Project Model
- SSIS Project Parameters
- Package Parameters
- New techniques/tricks/approach/design
- Package Part
- A main folder, in my case it is “C:\P_C\2018\SQL2016\SSIS\Txt2SQL” and it will be set in the Package Parameter called “uPara_MainFolderPath”
- Create 2 Sub folders in the above folder “SampleFile”, “ToBeProcessed”
- A Sample CSV file called “SampleFile.csv” with data in the “Samplefolder”
Over look at the SSIS Project and package
Variables at 3 Levels, "Project", "Package Parameter" and "Package Variables"
Create 2 Variables, one for your destination SQL server and one for your destination DB name.
- uPara_MainFolderPath: Mandatory folder that will contain all the required folder related to this ETL package, some folders can be changed/redirected with the next 2 variables.
- uPara_BackupMainFolderPath: if you don’t want the backup folder to be in the same folder as “uPara_MainFolderPath” you can change it by adding a valid path.
- uPara_ToBeProcessedFolder: if you don’t want the source folder to be in the same folder as “uPara_MainFolderPath” you can change it by adding a valid path.
Source and Destination Connection Objects
How To Test?
- Set your SQL server name and SQL DB Name in the project parameters.
- Create the destination table (You can use DestinationClientTable.sql).
- Create a folder in my case it is “C:\P_C\2018\SQL2016\SSIS\Txt2SQL”.
- Copy the above Path in the “uPara_MainFolderPath” variable.
- Create 2 Sub folders 1 – SampleFile 2- ToBeProcessed.
- You must have your Sample.csv file copied in the SampleFile folder (I have provided one).
- Copy all of your source files into the ToBeProcessed folder or you can use the Sample.csv to get the ball rolling.
- Run your SSIS package
- Using windows explorer go to the same path as defined in “uPara_MainFolderPath” variable, you will see a sub folder named “BackupFolder”.
- Now your files have moved from ToBeProcessed folder to the BackupFolder folder under todays “…\BackupFolder\yyyy-mm\yyyy-mm-dd-HHmmss\”
- Also please check your destination table, my sample uses the [dbo].[Client] table.
Please note if…
- If your ToBeProcessed folder (Source Folder) is located on another folder/machine you can enter the valid path in the “uPara_ToBeProcessedFolder” variable.
- If your Backup folder is located on another folder/machine you can enter the valid path in the “uPara_BackupMainFolderPath” variable.