Azure .Net Core App Service fails to start 503 error

Was playing with a POC app recently. I pushed out a web deploy to an Azure App Service. Everything built and deployed without issue. The app fired up and nothing just the vanilla error message returned. I checked the site logs via Kudu and nothing jumped out. Tried another deploy and no dice. So where do we go from here? read more

SQLCMD / Dacpacs

Had a need to run a dacpac script that wasn’t part of pre or post deployments. You can use sqlcmd to do that for you by issuing the following:-

Sqlcmd –S <server>\<instance> -v <variablename>=<variablevalue> -i <sql script to run>.sql

So…

Sqlcmd –S localhost\myinstance -v databasename=mydatabase -i myscript.sql

SSIS Troubleshooting – Reporting

When running SSIS components through SQL Server Agent jobs sometimes an error may appear and when viewing the job history you’ll see the helpful “To view the details for the execution, right-click on the Integration Services Catalog, and open the [All Executions] report”. If you attempt this you may get an “Out Of Memory” exception due to the sheer amount of logging your SSIS component is producing.

So how do you troubleshoot?

There’s a way of querying the report through an SQL query and you can filter down the noise accordingly to something similar to the below:

SELECT TOP 15 *, event_message_id,MESSAGE,package_name,event_name,message_source_name,package_path,execution_path,message_type,message_source_type
FROM (
SELECT em.*
FROM SSISDB.catalog.event_messages em
WHERE em.operation_id = (SELECT MAX(execution_id) FROM SSISDB.catalog.executions)
AND event_name NOT LIKE '%Validate%'
)q
/* Put in whatever WHERE predicates you might like*/
WHERE event_name = 'OnError'
ORDER BY message_time DESC

Kudos to the following link that refreshed my memory on this:-

Azure Connection Strings

Connection string data should always be removed of obfuscated in source control.  There are many approaches to accommodating this. Scott Hanselmann has a great post on this.  Azure makes this even easier if you’re hosting a web app that’s referencing a database somewhere.

To enable simply click on the web app in question through the Azure dashboard.   Click on the “Configure” tab and look for the “Connection Strings” section about 3 quarters down.  In the “NAME” enter the name of your connection and in the “VALUE” enter the connection string.  So for example in my web.config I have the following:

<connectionStrings>
 <add name="DB_Dev" connectionString="Server=tcp:mydbserver.database.windows.net,1433;Database=mydatabase;User Id=Bob;Password=mysecretpassword;Encrypt=True;TrustServerCertificate=False;" />
 </connectionStrings>

This then appears in Azure config as follows:

I can then change my local dev config settings to whatever local dev instance I’m working on.

SSIS is replacing null values with Zeros for integers

Hit an issue recently where a csv file was being transformed through an SSIS package but persisting to the database as zero value integers for all null value columns.

Spent time examining table constraints through trying to establish why this was occurring.  It’s not the SSIS package because the file preview isn’t showing zeros right?   Nope, guess again it is the SSIS package.   Take a look at the Flat File flow component and tick the box as follows, job done.