Extracting Data from SQL to CSV files using SSIS and PowerShell 7.x

 Objective

I decided to combine SSIS and PowerShell 7.0 to create one new ETL SSIS package that transfers CSV files to SQL server using a configuration table, the objective is to create a config table in SQL that contained all the information about an ETL like 

1 - Source SQL table

2 - Selected fields

3 - Filters like the t-sql WHERE clause 

4 - Tracking mechanism

5 - Destination File Path 

6 - Destination File name and extension

7 - etc...

.

Requirements

    The powershell script will read CSV files and insert the data into a SQL Server destiantion table, the parameters are as mentioned.

1. [String]$srcSQLServerConnectionString = "Server = ABC ; Database = XYZ; Integrated Security = True;"
2. [String]$srcSELECTQuery = "SELECT Fields  FROM tblXYZ"
3. [String]$desFilePath = "<DRIVER>:\...\ETLFolder\FileToBeSFTPed"
4. [String]$desFileName = "YYYY-MM-DD HHMMSS FileName"
5. [String]$desFileExtension = "csv"                                 OR "txt"
6. [String]$desColumnDelimiter = "TAB"                       # "TAB"   "`t"    |    ;    `

    At this point the SSIS package will read the config table, start a "Tracking mechanism" (getting the MAX identity field or MAX modified date) then create a final T-SQL string for the PowerShell.

            Some of the config table are as mentioned

    Source Fields 

1. [srcTableSchema] nvarchar(20)
2. [srcTableName] nvarchar(200)
3. [srcFieldList] nvarchar(2000)
4. [srcWHEREClause]                 nvarchar(1000)
5. etc...

    Destination Fields

1. [desFolder] nvarchar(2000)
2. [desFileName] nvarchar(50)
3. [desFileExtension] nvarchar(50)
4. ....
5. [srcPreExecute_usp] nvarchar(50)
6. [srcPostExecute_usp_Success] nvarchar(50)
7. ....

Please note…

• PowerShell script may need to be registered
• The SSIS has an internal logging system
• Three custom SQL stored procedures can be used/set within the configuration table for each ETL , one for PreETL, second PostETLSuccess and Finally PostETLFailure

Do you want the code? Click here (From Google).