none
Loading flat files via powershell

    Question

  • Hi,

    I’m Sam. I am trying to load flat files using powershell script. I want to load only the columns I need from the flat files.

    The flat files are being loaded into SQL sever database.

    The flat file doesn’t have any headers.

    MyFlatfile.txt sample:

    Row1Col1,Row1Col2,Row1Col3,Row1Col4

    Row2Col1,Row2Col2,Row2Col3,Row2Col4

    Row2Col1,Row2Col2,Row2Col3,Row2Col4

    Row2Col1,Row2Col2,Row2Col3,Row2Col4

    In the above flat file I don’t want to load the entire flat file columns into my destination table. Instead, I want to load only selected columns, say Columns1 and columns2 alone. I use powershell to accomplish my task.

    These are the steps how I load the file in destination tabe.

    I use 2 tables in the process.

     “Dest_Table_XXXXXXXX” – My target table.

    Where, XXXXXXXX is current date. I maintain the destination table day wise.

    Eg: Dest_Table_20170320, Dest_Table_20170321.. ect..

    “Temp_Table” – Temporary table for processing.

    The “Dest_Table_XXXXXXXX” has all the column in “Temp_Table”  and also other columns.

     

    1.        Truncate “Temp_Table”.
    2.        Insert all the flat file contents into “Temp_Table” using bulk insert.
    3.        Add column to “Temp_Table” like filename, and other user defined columns and update them (Derivated Columns).
    4.        Select only the columns that I need from “Temp_Table”, map them by  SqlBulkCopy  ColumnMappings and insert into target table “Dest_Table_XXXXXXXX” using SqlBulkCopy.
    5.        Remove the derivaled columns that were added in step 3.
    6.        Now I repeat the steps 1-5 for each file in a FTP path say “D:\FTP_Files\Load”. The files are being pushed in the FTP path periodically.
    7.        Once the file is successfully loaded in destination table in step 4, I move the file to a folder named “Archive”. In our scenario, it is “D:\FTP_Files\Load\ Archive”.

    My question is, I have made the PowerShell script run successfully. But the time taken to load each file seems to increase when the flat file size also increases.

    Smaller the file size, quicker the file loads into destination table say in 2-4seconds. Larger the file size, It takes more time to load into destination table like in 20-30 seconds. Is there any way to speed up the process when the flat files are larger??


    Monday, March 20, 2017 5:44 AM

All replies

  • You need to ask this question in the SQLServer forum.  The issue is the server and not the script.


    \_(ツ)_/

    Monday, March 20, 2017 9:07 AM
    Moderator
  • You need to ask this question in the SQLServer forum.  The issue is the server and not the script.


    \_(ツ)_/

    Hi,

    I have also developed an equivalent SSIS package. The same larger files loads quickly in SSIS package, but comparatively the loading duration is reduced while executing via my PowerShell script. 


    Tuesday, March 21, 2017 4:25 AM
  • SQLBulkCopy is not really executed by PowerShell.  It is executed by the local SQL client software.  It is the same as the BCP utility.  PowerShell just launches the activity.  PS never directly touches the data.  SSIS runs on the server so there is no network latency.


    \_(ツ)_/

    Tuesday, March 21, 2017 4:44 AM
    Moderator