[ETL] Importing UNIX files

Coworker had a problem importing a data file - one column per line, but was running into problems and had to invoke Code Page 65001 in his SSIS script. Took forever to fix, and forever to process. Looked at the file - UNIX.

So, the easy T-SQL way to do it:

CREATE TABLE deleteme (resultant VARCHAR(MAX))



BULK INSERT deleteme

   FROM '\\server\path\file.rpt'

   WITH 

      (

         ROWTERMINATOR = '0x0a'

      )

[Tuning] Disk usage over time, checking deltas

Not sure where this came from, if I wrote it or someone else did. Practically, it looks before and after to tell you which files are getting used. The commented out bit was because I was investigating WRITELOG delays.

--as always, drag-copy to get the full text.

DECLARE @compare TABLE (NAME sysname, type_desc varchar(10), physical_name VARCHAR(200), sample_ms BIGINT, 

num_of_bytes_written BIGINT, num_of_bytes_read BIGINT, size_in_gb INT)

DECLARE @compare2 TABLE (NAME sysname, type_desc varchar(10), physical_name VARCHAR(200), sample_ms BIGINT, 

num_of_bytes_written BIGINT, num_of_bytes_read BIGINT, size_in_gb INT)



INSERT INTO @compare 

SELECT 

  master_files.NAME, 

  master_files.type_desc,

  master_files.physical_name, 

  vfs.sample_ms,

  vfs.num_of_bytes_written, 

  num_of_bytes_read, 

  size_on_disk_bytes/1024/1024/1024 AS Size_in_GB

FROM sys.dm_io_virtual_file_stats(null, null) vfs

INNER JOIN MASTER.sys.master_files 

 ON vfs.[file_id] = master_files.[file_id] 

 AND vfs.database_id = master_files.database_id

 --WHERE type_desc = 'log'

ORDER BY (num_of_bytes_read + num_of_bytes_written) DESC



WAITFOR DELAY '00:00:15'



INSERT INTO @compare2

SELECT 

  master_files.NAME, 

  master_files.type_desc,

  master_files.physical_name, 

  vfs.sample_ms,

  vfs.num_of_bytes_written, 

  num_of_bytes_read, 

  size_on_disk_bytes/1024/1024/1024 AS Size_in_GB

FROM sys.dm_io_virtual_file_stats(null, null) vfs

INNER JOIN MASTER.sys.master_files 

 ON vfs.[file_id] = master_files.[file_id] 

 AND vfs.database_id = master_files.database_id

-- WHERE type_desc = 'log'

ORDER BY (num_of_bytes_read + num_of_bytes_written) DESC





SELECT old.NAME, old.physical_name, new.sample_ms - old.sample_ms AS time_elapsed,

new.num_of_bytes_written - old.num_of_bytes_written AS delta_num_bytes_written,

new.num_of_bytes_read - old.num_of_bytes_read AS delta_num_bytes_read, old.size_in_gb

FROM @compare old INNER JOIN @compare2 new ON old.physical_name = new.physical_name

ORDER BY (new.num_of_bytes_written - old.num_of_bytes_written) DESC

[Trick] get midnight fast

I've used this in the past, but technically it's sloppy.

select convert(char(8),getdate(),112)

So these days I'm using this:

select DATEADD(dd, DATEDIFF(dd,0,getdate()), 0)