Lift and shift SSIS package

We use Sql Server Integration Services (SSIS) to import text files into our database. The dtsx files are deployed into the MSDB on the Sql Server. Everything works well with deployment with a powershell DSC script. But times change and we must deploy the packages to the catalog. Here is what we did.

Convert

The first step is to convert the project. We used the “Convert to Project Deployment Model” wizard that is started from the Project menu or the context menu in the Solution Explorer. Because this is a lift-and-shift we don’t replace the configuration bij parameters, so in the step “create parameters” we unselect everything. After the conversion we can still open the “Package Configuration” and see the properties are set from there.

Build

The reason we converted the project is because we need an .ispac file for the release to the catalog. During the build the ispac file is created. We gather alle dtsx and ispac files in an atrifact.

Deploy

Deployment will take the new ispac files in the artifact. With the code sample from microsoft (see references) we install the ispac into the catalog with DeployProject. This change is in the powershell DSC.

Conslusion

Our SqlJobs had to load the package from the catalog now. This was a minor change:  

SET @Command = N'/SQL "\"\FOLDER\PACKAGENAME\"" /SERVER "\"' + @Server + '\"" /CHECKPOINTING OFF /REPORTING E’
SET @command=N'/ISSERVER "\"\SSISDB\FOLDER\PROJECTNAME\PACKAGENAME\""  /SERVER "\"' + @Server + '\"" /CHECKPOINTING OFF /REPORTING E'

After these steps the SSIS packages are in the catalog. The impact on the system is minimal because the sqljob remains on the same location and handles the location change. Happy dev, happy dba.

References

Posted in Development | Tagged , , , | Leave a comment

iOS 14 Wi-Fi Private Address

I’ve setup my Wifi router with timed access. This allows internet for certain time slots and denies access outside these time slots. After installing iOS 14 on both my iPad and iPhone I noticed no internet for me too. But I have my own rules based on the MAC address of the devices.

Turns out there is a Private Address option (enabled by default) that spoofs your MAC address to reduce tracking across different Wifi networks. I had to turn that off to get internet. Here is where you can find it. Go into settings > Wi-Fi and press the little i after the Network to get to the page in the screenshot. If you flip the switch on Private Address everything is alright.

Seems to be a feature not a bug: https://support.apple.com/en-us/HT211227

Posted in Uncategorized | Leave a comment

Webdeploy connectionstrings to xTokenize

We have an asp.net webapplication (full framework) and deploy it using release manager. The bits are in an artifact that is created during build with the contents of the webdeploy zip and a powershell desired state configuration (dsc) script to install te webapplication. Somewhere in the powershell script we use the xTokenize DscResource to set the connectionstrings and other settings.

mage courtesy of Kittikun Atsawintarangkul / FreeDigitalPhotos.net

In our initial setup we used the token file approach that is the default. Microsoft provided us with an example: https://www.powershellgallery.com/packages/xReleaseManagement/1.0.0.0/Content/Examples%5CxTokenize_TokenFile.ps1

The steps during build and release are:

  1. build performs web.config transformation to remove the debug (and other things),
  2. build replaces the web.config connectionstring with webdeploy tokens,
  3. build adds the web.token.config file to the artifact,
  4. release copies the web.config to the wwwroot directory,
  5. xTokenize replaces the web.config with the web.token.config,
  6. xTokenize replaces the tokens in web.config with actual values

It worked, but we had to copy every change in web.config to web.token.config. This felt cumbersome and error prune.

There was another example without the need of a token file: https://www.powershellgallery.com/packages/xReleaseManagement/1.0.0.0/Content/Examples%5CxTokenize_NoTokenFile.ps1 This could be combined with the web.config transformation to eliminate the need for a web.token.config. No more double administration and less steps in the build/release.

  1. build performs web.config transformation to remove the debug (and other things) also replaces the connectionstrings with the __TOKENS__ ,
  2. release copies the web.config to the wwwroot directory,
  3. xTokenize replaces the tokens in web.config with actual values

To stop the build from replacing the connectionstrings we defined a publish profile. The publish profile creates a pubxml file with the same name as the profile. We added the following line to the pubxml to stop the transformation of connectionstrings:

<AutoParameterizationWebConfigConnectionStrings>false</AutoParameterizationWebConfigConnectionStrings>

Remember to use the /p:PublishProfile=YOUR_PROFILE_NAME in the build, else the connectionstrings are transformed since it is the default behaviour.

Now our web.release.config contains some more lines that replace the connectionstrings with the __TOKENS__ (we think that is a good thing) and the web.token.config had been removed.

The results are the same working webapplication, but the way to get there is less crooked.

Posted in Uncategorized | Tagged , , , , , | Leave a comment

Table boulder challenge

my table boulder attempt

Posted in Uncategorized | Tagged | Leave a comment

Webapi core 2.1 and jQuery (CORS)

I’ve written before about Cordova, AngularJs, WebApi and CORS. (Cross Origin Resource Sharing) Now we have te same hurdle to take with our dotnet core 2.1 webapi.

Since the webapi is on a webserver and accessible to third parties we decided to keep the access fine grained. We will only allow CORS for GET operations on 1 resource. This can be achieved with policies. Below is the dotnet code to add the policy and use it only on the GET operation.

// add this to ConfigureServices
services.AddCors(o => {
    o.AddPolicy("AllowGet", builder => {
        builder.AllowAnyOrigin()    // allow everybody
               .WithMethods("GET")  // only allow GET
               .AllowAnyHeader()    // enable preflight
               .AllowCredentials(); // needed for auth
    });
});

// in the controller
[HttpGet]
[EnableCors(PolicyName = "AllowGet")]
public async Task<IActionResult> Get() {
    var result = "your data";
    return Ok( result );
}

Now that the webapi supports CORS we can call the GET from our page with jQuery.

$.ajax({
    type: "GET",
    url: url,
    crossDomain: true,
    cache: false,
    // needed for preflight
    xhrFields: {
        // needed for auth
        withCredentials: true
    },
    success: function (response) {
        // do something with response
    },
    error: function (response, status, error) {
        console.write(error)
    }
});
Posted in Development | Tagged , , , | Leave a comment