SSIS Package Documentation
Overview / Purpose
The purpose of this SSIS package is to facilitate the movement of files between Dropbox and the local system. The primary workflow involves retrieving data from SQL Server, processing it, and then moving the resulting files back to a Dropbox archive.
Variables Information
take screenshot of variable window and put it here in document
Dependency :
Task by Task Explanation
1. Execute SQL Task:
3. File System Task(Explain here)
5. Expression Task 2(Write Task)
7. Copy Items from 'Local File' to 'Dropbox'
Dependency :
Dropbox Password: Required for executing premium tasks.
Ensure that the Dropbox password is available for successful execution. Additionally, check if any specific files or folders need to exist before the package runs.
Task by Task Explanation1. Execute SQL Task:
Get Query Group Information from SQL
To get group information and store the result set in object variable.
SQL Query:sqlCopy code
SELECT AG.PUNBR, AG.GRNBR, LEFT(AC.INCFROM, 6) AS StartDate, LEFT(AC.INCTHRU, 6) AS EndDate FROM dbo.AggContract AC INNER JOIN dbo.AggGroup AG ON AC.[CONTRACT] = AG.[CONTRACT] WHERE LEFT(AC.INCTHRU, 6) = FORMAT(DATEADD(m, -1, GETDATE()), 'yyyyMM') AND RIGHT(AC.[CONTRACT], 1) <> 'M'
2. Foreach Loop Container
SQL Query:sqlCopy code
SELECT AG.PUNBR, AG.GRNBR, LEFT(AC.INCFROM, 6) AS StartDate, LEFT(AC.INCTHRU, 6) AS EndDate FROM dbo.AggContract AC INNER JOIN dbo.AggGroup AG ON AC.[CONTRACT] = AG.[CONTRACT] WHERE LEFT(AC.INCTHRU, 6) = FORMAT(DATEADD(m, -1, GETDATE()), 'yyyyMM') AND RIGHT(AC.[CONTRACT], 1) <> 'M'
2. Foreach Loop Container
Fetch each row from result set and loading 4 columns in 4 variables for each occurence.
3. File System Task(Explain here)
Use to create folder/directory for destination
4. Expression Task(Write task)
As from foreach loop we have all required value assinged to all variable from each row.
so, prepare a sql query to call execute proce with all requried parameters for each row.
5. Expression Task 2(Write Task)
here we prepare foler path and connect wtih grnbr and date with .xls
6. Script Task(Explain Here)
so, here we are preparing full file path to be saved on destination. It is just a string so save the required file on required location.
we are not using script task , it is just for dummy for future use of any maniuplation
7. Copy Items from 'Local File' to 'Dropbox'
lcoal files to drop box folder
8. Send Email Task
sending email to notify business users
This SSIS package comprises a series of tasks orchestrated to retrieve, process, and move files seamlessly between Dropbox and the local system. The workflow is designed to handle data retrieval from SQL Server, utilize expressions and scripts for necessary operations, and finally, transfer files and send email notifications. The provided SQL queries fetch essential information, and the package's success relies on a defined Dropbox password and any pre-existing files or folders necessary for execution. For variable details, refer to the attached photo of the variable window.
This SSIS package comprises a series of tasks orchestrated to retrieve, process, and move files seamlessly between Dropbox and the local system. The workflow is designed to handle data retrieval from SQL Server, utilize expressions and scripts for necessary operations, and finally, transfer files and send email notifications. The provided SQL queries fetch essential information, and the package's success relies on a defined Dropbox password and any pre-existing files or folders necessary for execution. For variable details, refer to the attached photo of the variable window.
.png)
