程式筆記

需求重點

• 是否處理INSERT/全量/增量?
• 需要IMPORT/EXPORT
• 是否要處理遠端檔案(different physical computer)．ex：Azure SQL Database 不支援從 Windows 檔案讀取。
• 是否有權限執行? DB連線或執行xp_cmdshell權限? EXE/BAT檔執行權限?

資料檔案及格式

• 檔案編碼(UTF8/BIG5)為何
• 欄位內容是否需在轉檔時同步檢查內容
• 檔案欄位與資料表欄位順序/欄位數是否不同
• DataSource為何
• 第一行是否為欄位名稱

處理重點

• 是否有錯誤就全部回復(recovery)? 或是部份進去? 或是直接中斷後面資料都沒進去?
• 可設定錯誤筆數上限, 否則全部回復
• 列出錯誤的資料列數與錯誤的欄位名稱
• 處理LOG明細
• 可設BATCHSIZE (or ROWS_PER_BATCH)
• 可check constraints/trigger firing
• 可同步(多來源)轉檔(concurrent)(because of lock way)
• 有BULK API支援-Minimally logged operations
  • high-speed data loading(Table Indexes/Rows in table/TABLOCK/Trace Flag 610/Sorted Input Data)

    • Whether the table has constraints or triggers, or both.
      The recovery model used by the database.
      Whether the table into which data is copied is empty.
      Whether the table has indexes.(資料依照clustered index 排序)
      Whether TABLOCK is being specified.
      Whether the data is being copied from a single client or copied in parallel from multiple clients.
      Whether the data is to be copied between two computers on which SQL Server is running.
    • 資料來源：https://technet.microsoft.com/en-us/library/dd425070%28SQL.100%29.aspx?f=255&MSPPError=-2147217396

  • Metadata-Only Operations(Drop/Truncate/Merge/Partition SWITCH)
  • trnsaction log問題案例

工具

• Transact-SQL
  • BULK INSERT
    • 只能匯入不能匯出
  • INSERT … SELECT(OPENROWSET)
    • 不能使用bulk load parameters, like commit size(BATCHSIZE/ROWS_PER_BATCH) / check constraints / trigger firing
  • SELECT INTO
    • 不能使用BATCHSIZE or ROWS_PER_BATCH
    • 目標資料表需在default filegroup.
    • 不能同時多來源insert
• SSIS
  • SQL Dest-SSIS需和DB同台主機跑
  • OLE DB Dest-SSIS可和DB不同台主機跑
  • 因bulk insert產出的Log不夠清楚(ex:Row 3 File Offset 170 ErrorFile Offset 0 - HRESULT 0x80004005), 可用ssis的bulk insert轉入後, 再把錯誤資料導到另一個table(If you want to get more information, you can also try to use BCP or SSIS which provider different error log file.)
• ASP.Net
  • DataTable+資料庫的資料表值參數(Table Valued)
  • SqlBulkCopy-只能insert
• Command Line
  • BCP
  • SQLCMD