Home› User Guide› Getting Started› Quick Start Guide› Deploy

Deploy

Run the generated Northwind loading files once, then check the warehouse data and its sales totals. A successful package run and matching data checks together establish the result.

Have the files and empty NorthwindWarehouse destination from Generate your warehouse, pipeline and Power BI project ready. Keep Northwind unchanged during the comparison. Use the Windows execution account and 64-bit Integration Services 2022 runtime recorded in your setup record.

Check and validate the loading files

  1. Compare the generated files with the execution list saved during generation. Include each selected .dtsx file once, in the recorded order, and use its checked .dtsConfig file. Several tables can share one package; a package that loads three tables still runs only once.

    If a path, setting or file changed, repeat the generated-file checks. Those checks cover the AnalyticsCreator definitions and connection to CFG.SSIS_Configurations; validation below checks whether each package can run. They do not by themselves reveal every effective connection or override in the generated package. If you cannot confirm its actual source and write destination, have your administrator or AnalyticsCreator support review the package and applicable configuration before executing the load. Keep the files unchanged during that review. Passing another configuration path does not guarantee that it overrides every embedded setting.

  2. Open a query window in SQL Server Management Studio (SSMS) on the destination server and select NorthwindWarehouse, or your recorded destination. Run the following read-only check. If you chose a different destination name, replace NorthwindWarehouse in the guard and its message with that name. The table names shown are the tutorial mappings; if you recorded different generated names, replace the bracketed schema and table names with those exact mappings.

    IF DB_NAME() <> N'NorthwindWarehouse'
    THROW 51100, 'Select NorthwindWarehouse before continuing.', 1;
SELECT @@SERVERNAME AS ServerName, DB_NAME() AS DatabaseName;
SELECT N'Customers' AS TableName, COUNT_BIG(*) AS RowCount FROM [IMP].[Customers]
UNION ALL SELECT N'Orders', COUNT_BIG(*) FROM [IMP].[Orders]
UNION ALL SELECT N'Products', COUNT_BIG(*) FROM [IMP].[Products]
UNION ALL SELECT N'Order Details', COUNT_BIG(*) FROM [IMP].[Order Details];

    All four counts must be 0, and no load may already have been attempted for this destination. If either condition is false, use a fresh warehouse for another attempt. A missing-table error means generation or the recorded mapping needs correction before loading.

  3. Choose where to run the loading commands. Both options use the same generated files and the Integration Services runtime prepared earlier. Choose one option for this load, then use the shared commands below.

    Option A — Use Windows PowerShell

    Open Windows PowerShell on the Windows computer prepared to run the load, using your recorded execution account. Continue with the account and shell check below.

    Option B — Load your data from Visual Studio Code

    Visual Studio Code gives you a terminal inside the editor. Integration Services still performs the load; the warehouse structure was deployed during generation.

    1. If needed, download and install Visual Studio Code for Windows on the computer prepared to run the load.

    2. Open a local Visual Studio Code window using your recorded execution account, then open the folder containing your generated files.

    3. Select Terminal → New Terminal. Open the dropdown beside the terminal's + button and choose Windows PowerShell.

    4. If Windows PowerShell is missing from that dropdown, choose Select Default Profile → Windows PowerShell, then select Terminal → New Terminal again. This sets Windows PowerShell as the default for new terminals; an already open terminal keeps its previous shell. Microsoft explains this choice in Windows PowerShell terminal profiles.

    In your chosen terminal, run these commands to check the account and shell:

    whoami
$PSVersionTable.PSEdition
$PSVersionTable.PSVersion.ToString()

    What you should see: your recorded Windows execution account, Desktop, and a version beginning with 5.1. If these differ, open Windows PowerShell with the recorded account before continuing. Keep this same terminal open through validation and loading. Run the validation block first, then run the loading block in this same terminal after validation succeeds and you have reviewed its logs. In Visual Studio Code, enter commands in the terminal; opening a script in the editor does not run it.

    For help opening or switching terminals, see Microsoft's Visual Studio Code terminal guide.

  4. Run the block below in your chosen Windows PowerShell terminal. Enter each full package path in the recorded order; press Enter on an empty line after the last path. Enter the checked configuration path when prompted. If SSIS 2022 is installed elsewhere, replace $DtexecPath with the actual 2022 path from your setup record.

    DTExec.exe runs SSIS packages. The /VALIDATE option checks them without executing their load. This procedure deliberately requires version 16.x, matching the 2022 generation target. A SQL Server 2025 Database Engine installation does not by itself establish that this runner is present.

    $ErrorActionPreference = 'Stop'
$Validated = $false
$DtexecPath = 'C:\Program Files\Microsoft SQL Server\160\DTS\Binn\DTExec.exe'
if (-not (Test-Path -LiteralPath $DtexecPath -PathType Leaf)) {
    throw 'Locate the SSIS 2022 runner recorded during setup.'
}
if ((Get-Item -LiteralPath $DtexecPath).VersionInfo.ProductMajorPart -ne 16) {
    throw 'This route needs SSIS 2022 (16.x). Check setup and the generation target.'
}
$ConfigurationPath = Read-Host 'Full path to the checked .dtsConfig file'
if (-not (Test-Path -LiteralPath $ConfigurationPath -PathType Leaf) -or
    [IO.Path]::GetExtension($ConfigurationPath) -ne '.dtsConfig') {
    throw 'The configuration file was not found.'
}
$ConfigurationPath = (Resolve-Path -LiteralPath $ConfigurationPath).Path
$Packages = @()
while ($true) {
    $PackagePath = Read-Host 'Full .dtsx path, or Enter to finish the list'
    if ([string]::IsNullOrWhiteSpace($PackagePath)) { break }
    if (-not (Test-Path -LiteralPath $PackagePath -PathType Leaf) -or
        [IO.Path]::GetExtension($PackagePath) -ne '.dtsx') {
        throw 'A selected package file was not found.'
    }
    $PackagePath = (Resolve-Path -LiteralPath $PackagePath).Path
    if ($Packages -contains $PackagePath) { throw 'Enter each package only once.' }
    $Packages += $PackagePath
}
if ($Packages.Count -eq 0) { throw 'Enter the selected package paths.' }
$LogFolder = Split-Path -Parent $ConfigurationPath
$AttemptFile = Join-Path $LogFolder 'first-load-attempted.txt'
if (Test-Path -LiteralPath $AttemptFile) {
    throw 'A load was already attempted. Use the fresh-warehouse procedure.'
}
$CheckedFiles = @($ConfigurationPath) + $Packages
$CheckedHashes = @{}
foreach ($File in $CheckedFiles) {
    $CheckedHashes[$File] = (Get-FileHash -LiteralPath $File -Algorithm SHA256).Hash
}
$RunStamp = Get-Date -Format 'yyyyMMdd-HHmmssfff'
for ($Index = 0; $Index -lt $Packages.Count; $Index++) {
    $PackagePath = $Packages[$Index]
    Set-Location -LiteralPath (Split-Path -Parent $PackagePath)
    $Log = Join-Path $LogFolder "validate-$Index-$RunStamp.log"
    $SavedPreference = $ErrorActionPreference
    try {
        $ErrorActionPreference = 'Continue'
        & $DtexecPath /FILE $PackagePath /CONFIGFILE $ConfigurationPath /VALIDATE /REPORTING V 2>&1 |
            Tee-Object -FilePath $Log -ErrorAction Stop
        $Code = $LASTEXITCODE
    } finally { $ErrorActionPreference = $SavedPreference }
    "Package: $PackagePath. Validation exit code: $Code. Log: $Log"
    if ($Code -ne 0) { throw 'Validation failed. Read the saved log.' }
}
foreach ($File in $CheckedFiles) {
    if ((Get-FileHash -LiteralPath $File -Algorithm SHA256).Hash -ne $CheckedHashes[$File]) {
        throw 'A file changed during validation. Validate the current files again.'
    }
}
$Validated = $true

    What you should see: validation exit code 0 for every selected package and a separate log in the configuration folder. Read the logs before continuing. If any check fails, use the recovery steps.

Run the first load

  1. Run this block in the same Windows PowerShell terminal used for validation, either in its own window or inside Visual Studio Code. It verifies that the checked files have not changed, records the attempt, then executes each package once. It stops after the first failed package.

    if (-not $Validated) { throw 'Validate all selected packages first.' }
foreach ($File in $CheckedFiles) {
    if ((Get-FileHash -LiteralPath $File -Algorithm SHA256).Hash -ne $CheckedHashes[$File]) {
        throw 'A checked file changed. Validate the current files again.'
    }
}
if (Test-Path -LiteralPath $AttemptFile) {
    throw 'A load was already attempted. Use the fresh-warehouse procedure.'
}
$AttemptStream = [IO.File]::Open($AttemptFile, [IO.FileMode]::CreateNew)
$AttemptStream.Dispose()
$LoadStamp = Get-Date -Format 'yyyyMMdd-HHmmssfff'
for ($Index = 0; $Index -lt $Packages.Count; $Index++) {
    $PackagePath = $Packages[$Index]
    Set-Location -LiteralPath (Split-Path -Parent $PackagePath)
    $Log = Join-Path $LogFolder "load-$Index-$LoadStamp.log"
    $SavedPreference = $ErrorActionPreference
    try {
        $ErrorActionPreference = 'Continue'
        & $DtexecPath /FILE $PackagePath /CONFIGFILE $ConfigurationPath /REPORTING V 2>&1 |
            Tee-Object -FilePath $Log -ErrorAction Stop
        $Code = $LASTEXITCODE
    } finally { $ErrorActionPreference = $SavedPreference }
    "Package: $PackagePath. Load exit code: $Code. Log: $Log"
    if ($Code -ne 0) { throw 'Load failed. Keep the log and follow recovery.' }
}

    Wait for all packages to finish. Exit code 0 means execution completed successfully; any other code needs investigation. Keep the attempt marker and logs. They record execution, while AnalyticsCreator's deployment log records generation and deployment.

  2. Record the execution account, time, source and destination, package paths, configuration path and each exit code in your setup record. Keep logs locally because they can contain connection details. Continue directly to the data checks below.

Check the loaded Northwind data

  1. In SSMS, open a fresh query window on NorthwindWarehouse. Download 03-check-warehouse.sql and compare its four schema/table mappings with the generated names in your setup record. They are filled in as IMP.Customers, IMP.Orders, IMP.Products and IMP.[Order Details]. Change only mappings that differ, entering schema and table separately without brackets. Keep the expected-database guard set to your destination.

    The column variables use the original Northwind names. If your mapping renamed a column, update its variable too. Use the original source identifiers, including both OrderID and ProductID for an order line.

  2. Run the whole file. It reads the warehouse and compares the selected columns for every row against the pinned Northwind sample, using temporary tables in this session. It does not change permanent data.

    What you should see: four PASS rows, both difference columns equal to zero, and no SQL error. The expected counts are Customers 91, Orders 830, Products 77 and Lines 2,155. It also rejects repeated complete keys. Equal counts alone are not enough.

  3. Compare the sales and date result grids with these answers. The sales calculation uses the order-line price, quantity and discount; it excludes freight and assigns no currency.

    Expected Northwind warehouse results
    CheckExpected answer
    Order lines / units2,155 / 51,317
    Net sales, all years1,265,793.29
    1996 / 1997 / 1998 net sales208,083.99 / 617,085.35 / 440,623.95
    First / last order date4 July 1996 / 6 May 1998
    Customer QUICK net sales110,277.32

    The query converts Discount to four decimal places, calculates UnitPrice × Quantity × (1 − Discount), and rounds each line to two decimals before summing. Grouping uses Orders.OrderDate. Rounding only the final total gives a different answer. Customers FISSA and PARIS have no orders and appear with zero sales in the customer result.

Check the generated sales fact

  1. Download 04-check-sales-model.sql. Set its expected database to your deployed destination. Confirm its fact view against the actual name recorded during generation; DWH.[FACT_Order Details] is the requested example, not a substitute for that check.

    Replace the six REPLACE_… field values using the fact's technical SQL column names and their origins in the model definition. Enter names without brackets. The script adds brackets safely.

    Map the fact back to the original Northwind values
    Script variableRequired field origin
    @FactOrderIDOrder Details.OrderID
    @FactProductIDOrder Details.ProductID
    @FactCustomerIDOrders.CustomerID, through the order relationship
    @FactQuantityOrder Details.Quantity
    @FactUnitPriceOrder Details.UnitPrice, not Products.UnitPrice
    @FactDiscountOrder Details.Discount

    Use the original business identifiers, not newly generated dimension keys. Compare the script's imported table and column mappings with the ones that passed the preceding check. If a source-origin field is missing from the fact, correct the model before continuing.

  2. Open the edited file in a fresh SSMS query window on the warehouse and run the whole file. It compares every normalized fact line with the checked imported order line and its customer assignment. It also rejects null values and repeated OrderID/ProductID combinations.

    What you should see: FactValueCheck = PASS, both difference counts equal to zero, 2,155 unique order lines, 51,317 units and net sales 1,265,793.29. The final result begins PASS: fact grain, business keys, customer assignments, sales values and totals match. A repeated key or differing value means the model or field mapping needs correction. Do not accept matching totals alone.

    Dimension definitions can include an explicit unknown member. Inspect their generated definition before interpreting a row count that differs from the source. This fact check does not execute the Power BI measures; the optional report checks those separately.

  3. Save both query outputs with the warehouse location and load time. Record the four import results, confirmed fact/field mappings, fact result and sales total in your setup record. The checks cover all rows for the listed customer, order, product and line columns; they do not compare every other Northwind or analytical column.

When every package succeeds and both the imported-data and sales-fact checks match, the Northwind load and sales model are ready. You can finish here or open the generated Power BI project to explore the same results.

If validation, loading or data checks fail

  1. Keep the log or query output and read the first relevant error. Check the runtime path and version, package/configuration paths, server, destination and mappings against your setup record.

  2. For a connection error, check the execution account, installed MSOLEDBSQL19 provider, database access and certificate settings. Confirm that CFG.SSIS_Configurations exists where the generated configuration expects it. A successful connection in AnalyticsCreator does not establish that the load account has the same access.

  3. If only validation failed and loading never started, correct the cause. Regenerate if a deployment or model setting changed, check the files again, then repeat validation. The destination must still be empty.

  4. If loading was attempted, keep the warehouse and inspect its counts. Failed packages can leave rows behind, and an earlier package may have succeeded. Do not delete the attempt marker or rerun the package list against that destination. Use the fresh-warehouse procedure below.

  5. If warehouse values differ, repeat the source Northwind check. If the source still matches, review selected tables, filters, keys and column conversions. A missing value, duplicate key or difference in text length, decimal scale or dates needs correction before another load. Do not delete rows simply to make totals agree.

  6. If imports pass but the fact fails, first check the six source-origin field mappings. Then inspect the fact's joins for missing matches or multiplied lines. Preserve the failed output, correct the model and regenerate it. If another load is needed, use a fresh destination below. Repeat both import and fact checks after correction.

Start another attempt in a fresh warehouse

Use this procedure after a partial failure or to repeat the exercise. It preserves the previous result without depending on an unverified append-or-replace rule.

  1. Keep the source, warehouse settings and previous warehouse. Follow the warehouse preparation steps to create another empty destination with an unused name, such as NorthwindWarehouse_Retry1, and give the execution account the required access.

  2. Create new output and configuration folders, for example C:\ACNorthwind\Retry1\Output and C:\ACNorthwind\Retry1\Config. In the exercise deployment, set the warehouse server/database to the new destination, Directory to the new output folder and Config file path to the new configuration folder. Keep the same intended imports selected.

  3. Repeat generation and output inspection. Confirm the new configuration and package connections identify the new warehouse and its four targets are empty. Preserve the old files, attempt marker and logs.

  4. Update the setup record and the expected-database guards in this page's count query, 03-check-warehouse.sql and 04-check-sales-model.sql to the new destination. Open a new Windows PowerShell terminal using either terminal option, validate the newly generated files, run them once and repeat all the data checks. If reporting afterward, use the regenerated Power BI project for the same new warehouse.

Microsoft's DTExec reference explains validation, configuration precedence, execution and exit codes.