Public/SetDataImportSQLMetadata.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
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
function SetDataImportSQLMetadata {
  param(
    [DataImportFile]$a
    ,[System.Data.DataTable]$dataAsDataTable
    ,[switch] $VarcharMax
  )
  Add-Type -AssemblyName System.Web


  $out = New-Object myOut
  
    Write-Log "Quering sql server to get target schema" Verbose
  $metaDataSQL = "Select count(*) 'cnt' from sys.objects obj
  inner join sys.schemas sch on obj.schema_id = sch.schema_id and sch.name = '$($a.schemaName)'
where obj.name = '$($a.tableName)';"

  $tableMetaData = Invoke-Sqlcmd -ServerInstance $a.destServerName -Database $a.destDatabase -Query $metaDataSQL | Select-Object -ExpandProperty cnt

  Write-Log "Generating the sql to create a new SQL table from this data table" Verbose
  $a.sqlprojCreateScript = New-SQLTableStatementFromDataTable -dataTable $dataAsDataTable -FQTableName $a.FQTableName -VarcharMax:$VarcharMax
  #If table does not exist, generate create table sql
  if ($tableMetaData -eq 0) {
    Write-Log "Doing this stuff" Verbose
    $SQLCreateTable = New-SQLTableStatementFromDataTable -dataTable $dataAsDataTable -FQTableName $a.FQTableName -VarcharMax:$VarcharMax

    $a.sqlCommand = $SQLCreateTable
    $a.ImportSummary.DoesTableNeedToBeCreated = 1
    #Add the XML needed to add these tables to a SSDT database project. IE, We can copy and paste this output into ClarityCustom so that these tables are part of the proper Clarity_Custom database
    $a.sqlprojIncludes += " <Build Include=`"$schemaName\Tables\$($a.FQTableName).sql`" />"


    $a.fileHTMLReport += "Table does not exist for $([System.Web.HttpUtility]::HtmlEncode($a.FQTableName)). Will create with the SQL below.<br>Include the following in the database project file<br><code><pre>$([System.Web.HttpUtility]::HtmlEncode($a.sqlprojIncludes))</pre></code>"

  }
  #else, check to see if we need to add any columns
  else {
Write-Log "Doing that" Verbose

    #Query the database to modify the local DataTable to match the deployed schema. Generate alter scripts to get the deployed shema to match any new columns
    $dbOrdered = @()

    $importSumamry = Get-DataImportFileSummary
    function QueryDB {
      $noChanges = $true
      $sqlQuery = "select col.name, column_id from sys.columns col
  inner join sys.objects obj on col.object_id = obj.object_id and obj.name = '$($a.tableName)'
  inner join sys.schemas sch on obj.schema_id = sch.schema_id and sch.name = '$($a.schemaName)'"

      $dbTableMetaData = Invoke-Sqlcmd -ServerInstance $a.destServerName -Database $a.destDatabase -ConnectionTimeout 0 -Query $sqlQuery
      foreach ($col in $dbTableMetaData) {
        if (!($dataAsDataTable.Columns[$col.Name])) {
          $importSumamry.ColumnsInDBNotInFile += $col
          $importSumamry.NumColumnsNotInFile += 1
        }

        $sqlQuery = "exec sp_describe_first_result_set @TSQL = N'select [$($col.name)] from [$($a.schemaName)].[$($a.tableName)]'"
        $dataType = Invoke-Sqlcmd -ServerInstance $a.destServerName -Database $a.destDatabase -ConnectionTimeout 0 -Query $sqlQuery

      }
      foreach ($col in $dataAsDataTable.Columns) {
        $columnName = $col.ColumnName
        $b = $dbTableMetaData | Select-Object -ExpandProperty name | Where-Object { $_ -eq $columnName }
        if (!($b -ne $null)) {
          #$noChanges = $false
          Write-Log "$columnName exists in the file but not in the database. Altering table to add new varchar(max) column"
          $InLocalAndNotDB += $col.ColumnName
          $importSumamry.ColumnsAddedToDB += $col
          $importSumamry.NumColumnsAddedToDB += 1
          $a.sqlCommand += "ALTER TABLE [$($a.schemaName)].[$($a.tableName)]
ADD [$($columnName)] VARCHAR(MAX);"

          
          #Invoke-Sqlcmd -ServerInstance $destServerName -Database $destDatabase -ConnectionTimeout 0 -Query $alterSQL
        }
      }

      if ($noChanges) {
        foreach ($col in $dbTableMetaData) {
          if ($dataAsDataTable.Columns[$col.Name]) {
            $dbOrdered += $col.Name
          }

        }
        $columnIndex = 0
        foreach ($columnName in $dbOrdered)
        {
          $dataAsDataTable.Columns[$columnName].SetOrdinal($columnIndex);
          $columnIndex++;
        }
      }
      else {
        QueryDB }
    }
    QueryDB

    Write-Log "Getting import summary packaged together" Verbose
    $a.ImportSummary = $importSumamry

    #Write-DataTable -ServerInstance $destServerName -Database $destDatabase -TableName $FQTableName -data $dataAsDataTable
    $a.fileHTMLReport += "<h3>Columns in file and not in database (need to be added to DB)</h3>"
    $a.fileHTMLReport += $a.ColumnsAddedToDB | Sort-Object -Property "Ordinal" | New-HTMLTable -setAlternating $false -Properties ColumnName,DataType,Ordinal
    $a.fileHTMLReport += "<br>"
    $a.fileHTMLReport += "<h4>Columns in database and not in file (will be null with this import)</h4>"
    $a.fileHTMLReport += $a.ColumnsInDBNotInFile | Sort-Object -Property "column_id" | New-HTMLTable -setAlternating $false -Properties name,column_id
  }
  $out.metadata = $a
  $out.DataTable = $dataAsDataTable
  Write-Output $out
} Export-ModuleMember -Function SetDataImportSQLMetadata