-
Notifications
You must be signed in to change notification settings - Fork 0
/
AutoImport_Comma.ps1
63 lines (44 loc) · 2.03 KB
/
AutoImport_Comma.ps1
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
## One file at a time (slow and unnecessary)
##AutoImport_Comma -server "PEIRANLIU17B7" -database "ETLCourse" -filepath "\\Mac\Home\Desktop\SSISCode\Code\inflationdata.csv" this is for only one file
##
$multipaleFiles = Get-ChildItem "\\Mac\Home\Desktop\SSISCode\Code\" -Filter *.csv
foreach( $a in $multipaleFiles){
$x=$a.FullName
AutoImport_Comma -server "PEIRANLIU17B7" -database "ETLCourse" -filepath $x
}
Function AutoImport_Comma ($server, $database, $filepath)
{
## Gets the staging table name
$tName = $filepath.Split("\") | Select-Object -Last 1
$tName = $tName.Split(".") | Select-Object -First 1
## Builds the structure of the staging table structure
$staging = Get-Content $filepath | Select-Object -First 1
$staging = $staging.Replace(",","] VARCHAR(MAX), [")
$staging = "IF OBJECT_ID('" + $tName + "') IS NOT NULL BEGIN DROP TABLE " + $tName + " END CREATE TABLE [" + $tName + "] ([" + $staging + "] VARCHAR(MAX))"
## Sets the connection and command parameters
$scon = New-Object System.Data.SqlClient.SqlConnection
$scon.ConnectionString = "SERVER=" + $server + ";DATABASE=" + $database + ";Integrated Security=true"
$buildstaging = New-Object System.Data.SqlClient.SqlCommand
$buildstaging.Connection = $scon
$buildstaging.CommandText = $staging
## Opens a connection and creates the staging table
$x = 0
$scon.Open()
$buildstaging.ExecuteNonQuery()
$scon.Close()
$scon.Dispose()
$x = 1
if ($x = 1)
{
$scon = New-Object System.Data.SqlClient.SqlConnection
$scon.ConnectionString = "SERVER=" + $server + ";DATABASE=" + $database + ";Integrated Security=true"
$bulkinsert = New-Object System.Data.SqlClient.SqlCommand
$bulkinsert.Connection = $scon
$bulkinsert.CommandText = "EXECUTE stp_BulkInsert_Comma '$filepath', $tName"
$scon.Open()
$bulkinsert.ExecuteNonQuery()
$scon.Close()
$scon.Dispose()
}
}