SSIS - How to deploy a package to SSIS from Visual Studio

• Ensure you can connect to Integration Services from SSMS
• SSMS 17.9.1 is the last version that can connect to SSIS properly
• https://docs.microsoft.com/en-us/sql/ssms/release-notes-ssms?view=sql-server-ver15#1791
• This a link with multiple versions of SSMS : https://sqlserverbuilds.blogspot.com/2018/01/sql-server-management-studio-ssms.html 
• Create SSIS Catalog
• From your SQL Server>Integration Services Catalog
• Right Click>Create Catalog
• Right click>New folder
• Publish Project in catalog
• Inside Catalog>right click on Projects folder>Deploy Project
• Select files from \Users\AdmBob\source\repos\projectname
• enter password>Apply
• Refresh
• Publish>Overwrite? click yes
• Schedule Package
• From Job, Right click, new job, new step
• Type: SQL Server integration package
• Package Source:SSIS Catalog
• Package: Local path for project (copy from \Users\AdmBob\source\repos\projectname)
• Define job schedule

Columbus /ToIncrease - Quantity (Base) available must not be less than x in Bin Content Location Code

• This is happening becasue UofM conversion factors are causing a 0.00001 difference in the total inventory calculation
• Go to Inventory Setup>Near Zero qty Value
• Set a value of 0.00001
• It will add this value to any entries that find a difference when attempting to register 

SSIS and SQL Management Studio compatibility

 

The SSMS that works is the 17.9.1 version . Link: https://docs.microsoft.com/en-us/sql/ssms/release-notes-ssms?view=sql-server-ver15#1791

This a link with multiple versions of SSMS : https://sqlserverbuilds.blogspot.com/2018/01/sql-server-management-studio-ssms.html

SQL - How to delete large numbers of records without locking tables

https://stackoverflow.com/questions/20499270/delete-statements-locks-table#:~:text=As%20others%20have%20pointed%20out,deletes%20until%20it%20is%20done.

• If you delete more than around 5000 records, the entire table gets locked for the duration of the delete
• If you split up the job into a loop of 1000 record delete bursts, it never locks the entire table

 declare @MoreRowsToDelete bit

set @MoreRowsToDelete = 1

while @MoreRowsToDelete = 1

begin

    delete top (1000) CRONUS$Preaction from CRONUS$Preaction where Date < '2020-07-01 00:00:00.000'

    if not exists (select top 1 * from CRONUS$Preaction where Date < '2020-07-01 00:00:00.000')

        set @MoreRowsToDelete = 0

end

Business Central - VSCode - extension could not be deployed because already deployed in another tenant

 Increment the version in the app.json file

SSIS - Cannot access *.ispac because it is in use by another process

https://ajitpatra.com/2018/03/29/ssis-the-process-cannot-access-the-file-ispac-because-it-is-being-used-by-another-process/

• Go to Task Manager–> Details Tab.
• Locate the process “DtsDebugHost.exe“. Kill this process. There might be multiple instances of this process. Kill all of them.

Business Central - View all Objects or Run tables in the web Client

https://codethecoffee.wordpress.com/2019/11/18/how-to-view-the-object-details-of-business-central-extensions-without-using-vs-code/

Go to this link, then filter on the app package id

 http://localhost:8080/BC150/?table=2000000058

Azure SQL DTU Calculator

https://dtucalculator.azurewebsites.net/

• run the powershell below for an hour
• upload the file to the above website

<#

    .SYNOPSIS

    Collect counters required for DTU Calculator and log as CSV.

    .DESCRIPTION

    Collect counters required for DTU Calculator and log as CSV. 

    Default log file location is C:\sql-perfmon-log.csv.

    Counters are collected at 1 second intervals for 1 hour or 3600 seconds.

    No support or warranty is supplied or inferred. 

    Use at your own risk.

    .PARAMETER DatabaseName

    The name of the SQL Server database to monitor.

    .INPUTS

    Parameters above.

    

    .OUTPUTS

    None.

    .NOTES

    Version: 1.0

    Creation Date: May 1, 2015

    Modified Date: June 17, 2016

    Author: Justin Henriksen ( http://justinhenriksen.wordpress.com )    

#>

Set-ExecutionPolicy -Scope Process -ExecutionPolicy Unrestricted -Force

$ErrorActionPreference = "Stop"

$VerbosePreference = "Continue"

cls

Write-Output "Collecting counters..."

Write-Output "Press Ctrl+C to exit."

$counters = @("\Processor(_Total)\% Processor Time", 

"\LogicalDisk(_Total)\Disk Reads/sec", 

"\LogicalDisk(_Total)\Disk Writes/sec", 

"\SQLServer:Databases(_Total)\Log Bytes Flushed/sec") 

Get-Counter -Counter $counters -SampleInterval 1 -MaxSamples 3600 | 

    Export-Counter -FileFormat csv -Path "C:\sql-perfmon-log.csv" -Force