SSIS Flow 2 Doc

 

SSIS Package Documentation

Overview / Purpose

The purpose of this SSIS package is to facilitate the movement of files between Sql Source and generated flat files in local system. The primary workflow involves retrieving data from the SQL Server, processing it, and then moving to archive. 

Variables Information

take a screenshot of the variable window and put it here in the document 

Dependency :
No dependecy 

Task by Task Explanation

1. Data Flow Task:

    Data flow has two component source as OLEDB and destination as flat file. In source, Using sql query to fetch data from datbase and loading that data into a Flat File. 

    Row Count : We are also counting the total number of row which are loaded. 

2. Script Task- 

    Title- Set All Variable(Explain here)

    Here we are setting up variable like Archive Path

    Attached a screenshot of script task , so variable maniuplation will be presented in this documnet.

 

3. File system Task:  

    Title - Rename Files from the Previous Task

    File we generated in Task 1 Data flow , now we are renaming that Flat file.

    Here our source variable is StaticFileName and our destination variable is Rename File name. please refer in variable windo for all tyeps of variables. 

4. Execute Process task (Write task)

    Using WinSCP commands, we are opening connectoing for FTP location

5.  File system Task:  

    Title- Archive Axos fee claim file to Import (Write Task)

    Now, we are moving file to Archive location. 

6. Execute SQL Task (Explain Here)

    Here, we are updating this sql table [HlthPC].[npnhp].LastRun -> IsVerfied column based on various busienss condition.

    UPDATE [HlthPC].[npnhp].LastRun

SET IsVerified = 1, RowCount = 0

WHERE ProcessName = 'EOMAXOSBankFees'

    AND IsVerified = 0

    AND CONVERT(DATE, RunDate) = CONVERT(DATE, GETDATE())

    AND MiscField = 'Claim';

Other Dependent things

SQL Job -  < name >

    In this, we will set this package so it can be triggered in server. 

Power Automate - < name >

    Using power automate we will trigger/start sql job.

Another Package: Axom Fee Claim B...

1. File system Task:  

    Title- FF_Copy Data File

    Here we are copying the file from previous task.

2. Data Flow Task

    Here, we are generating flat files from SQL Query. 

3. File system Task:  

    Title- Rename File

    Here, we are renaming csv file.

4. Send Mail Task

    Title- Email Mem Notification

    To notify business this will send email after completion.