SSIS – An error occurred in the requested FTP operation. Detailed error description: The password was not allowed

Possibly save 4 hours of your time: When you deploy a package with FTP Connection Manager you can get this error in which it says the password was not allowed.

The SQL Server Agent job is set up to use environment variables and the variables are also setup. However the job still fails when it try to connect to the FTP server. So for some reason the Environment variables are not working. The following environment variables are there (i.e. naming convention may be different from yours)

  1. FTPConnection_ServerName
  2. FTPConnection_ServerPort
  3. FTPConnection_ServerUserName
  4. FTPConnection_ServerPassword

The problem was that these variables are not directly used by the FTP Connection Manager in the package yet. You can right click the FTP Connection Manager and choose Parameterize. By choosing the Use existing parameter, that will link to the package parameter used in this package.

Parameterize right click

Parameterize.png

P.S. > If you are not using environment variables in your deployment, then you may also want to change protection level by right click Properties on the project and the package to Do not save sensitive data. However this is not recommended. Please use at own risk. There are other scripting solutions so that it is not required to deploy the password. Please google that.

do not save sensitive data

Professional Microsoft SQL Server 2014 Integration Services (Wrox Programmer to Programmer)

Advertisements

SSIS – Project Parameters, Package Parameters, User Variables

Possibly save 1 hour of your time: There are three types of parameters in SSIS that you can configure to use by your packages.

  1. Project parameters
  2. Package parameters
  3. User variables

You can access project parameters from the Team Explorer >> Project.params.

Project Parameters

Next you can access the package variables by double clicking the package to open the designer and choose tab Parameters.

Package Variables

Finally, you also have user variables. You can right click on the Control Flow designer and choose User Variables.

User Variables

Professional Microsoft SQL Server 2014 Integration Services (Wrox Programmer to Programmer)

Chrome requires https for input text fields

Possibly save 1 hour of your time: In October 2017, Chrome will be enforcing security warning for non https website that has input text fields. Your client may need to know this and decide to comply so their traffic will no face with not secure warning.

You will need to buy and bind the SSL certificate to your website.

See reference link for more details on how to setup for Azure website:

https://docs.microsoft.com/en-us/azure/app-service-web/app-service-web-tutorial-custom-ssl

Finally, you want to enforce https on your website so all HTTP requests will be redirect as a HTTPS request. One way to do that is through web.config but you will have to install URL Rewrite extension.

<?xml version="1.0" encoding="UTF-8"?>
<configuration>
<system.webServer>
<rewrite>
<rules>
<!-- BEGIN rule ELEMENT FOR HTTPS REDIRECT -->
<rule name="Force HTTPS" enabled="true">
<match url="(.*)" ignoreCase="false" />
<conditions>
<add input="{HTTPS}" pattern="off" />
</conditions>
<action type="Redirect" url="https://{HTTP_HOST}/{R:1}" appendQueryString="true" redirectType="Permanent" />
</rule>
<!-- END rule ELEMENT FOR HTTPS REDIRECT -->
</rules>
</rewrite>
</system.webServer>
</configuration>

C 6.0 and the .NET 4.6 Framework

sql server seed identity reset

Possibly save 1 hour of your time: Sometimes after deleting some reference data in a table one want to change the identity to reseed at 1 or a certain value. The next time you insert a new record it will reseed starting at 1.

DBCC CHECKIDENT ('YourTableName', RESEED, 0);
GO

Introducing Microsoft SQL Server 2016: Mission-Critical Applications, Deeper Insights, Hyperscale Cloud

dacpac post deployment script not running

Possibly save 1 hour of your time: In your database project, you may set it to build as a dacpac. Then you can use this dacpac to deploy scripts to your database. However, after you setup a post deployment script, it still didn’t seem to execute after you deploy the dacpac to your target database. Here are some guidance:

  1. If you haven’t already, you can add post deployment script by right click any folder in your project and choose Add >> Script. This script should contain your script or point to another script using notation :r .\myfile.sql. Use doubt quotes to wrap file path/name with space.
  2. After add script, right click the file and ensure Build Action is set to PostDeploy.
  3. Now build the project.
  4. Go to bin folder and find the dacpac. You can right click on this file and unpack it somewhere. Once unpacked, please verify there is a file name postdeploy.sql with the script code inside. This should be it you script will be execute when you deploy your dacpac. Make sure you are targeting the right database and verify using the right database. I got burned once looking at a different staging database.

Command for deploying dacpac:

“C:\Program Files\Microsoft SQL Server\130\DAC\bin\sqlpackage.exe” /Action:Publish /SourceFile:”Your Database.dacpac” /TargetDatabaseName:YourDatabaseName /TargetServerName:localhost /p:ExcludeObjectTypes=”ApplicationRoles;DatabaseRoles;Permissions;RoleMembership;Users;Credentials;ServerRoleMembership;ServerRoles” /p:BlockOnPossibleDataLoss=False

Introducing Microsoft SQL Server 2016: Mission-Critical Applications, Deeper Insights, Hyperscale Cloud

Error: SQL71006: Only one statement is allowed per batch. A batch separator, such as ‘GO’, might be required between statements.

Possibly save 1 hour of your time: This error happens when I am adding a post deploy script as part of my database project and then do a build.

When you add this script file to your project, you need to right click the properties and set Build Action to None. This should solve the issue.

Exam Ref 70-764 Administering a SQL Database Infrastructure

Caching big data in application using MemoryCache

Possibly save 2 hours of your time: I have only encountered this more recently in an application that deals with big dataset.
In this scenario, the application needs to provide a feature to download full datasets. And this dataset is huge (~75MB to ~125MB) in size. And there are many datasets.
It took awhile to query this data and do some calculation and send it back to the client app. So we decide to cache this data at the application level using System.Runtime.Caching.MemoryCache for subsequent faster performance. As you can see, the problem can become bigger as more datasets are downloaded and cached.
There are few options in no particular order:
  1. More expensive but easiest solution may be to increase memory on physical server to account for the size of the cache that can grow to.
  2. Recycle the application pool after reaching maximum virtual memory usage. See here for specifics.
  3. Set the CacheMemoryLimit on the MemoryCache object. This sets the amount of memory on the computer, in bytes, that can be used by the cache.

 

 

 

SSIS – Foreach Loop Container and Execute SQL Task

Possibly save 2 hours of your time: When you work on SSIS to do ETL, you may often encounter the need to loop through some records and process each record one at a time.
You can use the following steps:
  • Execute SQL Task – Get the records that need to be processed
  • Foreach Loop Container – Loop through each record
    • Execute SQL Task – Process one record at a time
ssis_foreachloop_1

Create two user variables:

  • i.e. CollectionIDs (Object) – One for collection of the IDs to be processed
  • i.e. CollectionID (Int32) – the current ID to be processed

 

The following will get the Full result set based on a select query. In this case as you can see on the SQLStatement, we select a primary key ID column from a table based on some criteria input. i.e. select ID from someTable i inner join anotherTable d on i.SourceID = d.SourceID where d.Title = ?

ssis_foreachloop_2

You may use a parameter input via Parameter Mapping in your query (i.e. select id from table where name = ‘someVariable’).

ssis_foreachloop_3

Now you want to store your Result Set into a user variable for looping through later.

ssis_foreachloop_4

In the Foreach Loop Editor, you can use Foreach ADO Enumerator, and select the collection user variable.

ssis_foreachloop_5

We want to a user variable to store the current processed id.

ssis_foreachloop_6

You can specify a query or stored procedure to process each record.

i.e. [schema].[usp_ProcessRecord] NULL, ?

The above example has two input variables where one is always NULL and the ? represents an input parameter.

ssis_foreachloop_7

In this example, the input is a large integer.

ssis_foreachloop_8