Bulk Insert Files From Template With File Name –Powershell

Alter table  [dbo].[myRegions]
drop column filename  

 --1Generate XML format file 
 DECLARE @sql VARCHAR(4000) 
SET @sql='bcp test1.dbo.myRegions format nul -c -x -f  "C:\temp\test\myFormatmyRegions.xml" -T -t\t -S'+ @@servername
exec master..xp_cmdshell @sql

--Add a filename column to the target table
Alter table  [dbo].[myRegions]
Add filename varchar(200) null


 --    -t"|" for pipe delimited
 --   -t\t for tab delimited
 --   -t, for comma delimited


Import-Module -Name 'SQLPS' -DisableNameChecking  

$svrname = "MC047012\MSSQL2014"


#Change default timeout time from 600 to unlimited
$svr =  new-object ('Microsoft.SqlServer.Management.Smo.Server') $svrname 
$svr.ConnectionContext.StatementTimeout = 0


$files = Get-ChildItem $workdir
for ($i=0; $i -lt $files.Count; $i++) {
$dt = (get-date).AddDays(-$i).ToString("yyyMMdd") 
$strFileName = "raw_US_$($dt).txt"
If (Test-Path "C:\temp\test\$strFileName"){

$query = @" 
BULK INSERT test1.dbo.myRegions from '$($workdir)$($strFileName)' WITH (FIELDTERMINATOR = '\t', FIRSTROW = 2, FORMATFILE = 'C:\temp\test\myFormatmyRegions.xml');
Update test1.dbo.myRegions SET filename= '$($strFileName)' WHERE filename is null;


Invoke-Sqlcmd -ServerInstance $svr.Name  -Database master  -Query  $query 


Catch [Exception]
 write-host "--$strFileName "$_.Exception.Message




Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s