Execute SFTP Task in SSIS

Sometimes you need to automate the upload of a file to an FTP or SFTP. Thought I would share the way that I use the SFTP task in SSIS to upload files.

Firstly create the variables to use in the SFTP task. Variables are an easier way to manage things without going into the task, so you can change the password in the variable and not have to open up the SFTP task.

Next you need to complete the SFTP Task Editor.

What you need to take note of above that is very important is that you must remember to place the Remote File name. So this would be the name of the file you are uploading. Once the above is filled in you can click ok and your SFTP task should be ready to go without any red cross on it.

Example:
My ETL consists of generating a file to upload, then uploading the file and then sending a mail to notify users the files have been uploaded.

Hope this helps you with using the SFTP task in SSIS.

 

Using a ZIP Task in SSIS

The ZIP file task is a great feature that SSIS has for when you need to move/email a large amount of data. Also if you have multiple files you want to move to the same location, this makes it easier for you, so you can move just one file instead of many.

I prefer to use variables which I can change if the location changes over using hard-coded values. The first step is to create the variables on the package:

Next step is to place the Zip task in your Control Flow area. Then configure it as follows:

This will then compress and zip your files in the source you set. You can then use a Process System task to move this file to where you require it or use an email task to email the file to people.

Setting an Alert on a Stored Procedure

Recently I came across an issue where I needed to set an alert that when a report is populated it needs to send out an alert. Unfortunately in SSRS you can only do subscriptions and I have not found a way to do those kinds of alerts on an SSRS report.

A workaround I found for this was to set an alert on the stored procedure the report is using. (Hoping you are using stored procedures for SSRS reports and not a large query in the report dataset) You can do this by applying the IF statement with the Execute dbmail proc. See below:

CREATE PROCEDURE dbo.DepartmentAlert
AS
SELECT *
FROM HumanResources.EmployeeDepartmentHistory

--If there are more than or equal to 1 record pulled back it will fire the email.	
IF
(
	SELECT	COUNT(*)
	FROM HumanResources.EmployeeDepartmentHistory
) >= 1
BEGIN
	EXEC msdb.dbo.sp_send_dbmail @profile_name = 'Alerting'
								,@recipients = 'jeanne.c17@gmail.com'
								,@body = 'Department history available'
								,@subject = 'Department History Availability'
								,@body_format = 'HTML'
	;
END
;

Next I created an SSIS package that uses a SQL execute task to execute my Stored Procedure. Then scheduled this to run every few hours and when it meets the IF statement validity then the mail is sent out to alert whoever is in the @recipients list.

Hope this helps you as much as it helped me.

Run a PowerShell Script from SSIS

It can be confusing to know which tool in the SSIS toolbox to use when trying to execute a PowerShell script from within SSIS.

The best task to use is an Execute Process Task. Under General you can give it a name and a description.

On the process section you fill in all the necessary details. Such as Executable, Arguments and Working Directory.

Once this is complete you can then execute your PowerShell script from within SSIS.

SSIS Save to XML Output

There isn’t much online about saving an output of data to an XML file. When trying with a file system task it throws errors. There is a workaround for this.

You can use a data flow task in SSIS to export to a flat file that is an XML file. You simply need to have a query that outputs in XML format, for example you can execute a stored procedure that returns the data in XML format:

SELECT [<?xml version="1.0"?> <batch>] = X.XMLString
FROM
(
SELECT XMLString = '<Messages xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://tempuri.net/Wonga.Payments.Csapi.Commands"><CreateTransaction><ApplicationGuid>' + CONVERT(VARCHAR(36), ApplicationID) + '</ApplicationGuid><PostedOn>' + CONVERT(VARCHAR(19), X.EffectiveDate, 126) + '</PostedOn><Scope>' + CASE
WHEN AmountToPost < 0 THEN '2'
WHEN AmountToPost > 0 THEN '1'
ELSE '0'
END + '</Scope><Type>' + '18' + '</Type><Amount>' + CONVERT(VARCHAR(10), AmountToPost) + '</Amount><Mir>' + '0' + '</Mir><Currency>710</Currency><Reference>' + Reference + '</Reference><SalesforceUsername>User</SalesforceUsername></CreateTransaction></Messages>'
FROM
(
SELECT *
FROM #DataSet
) X
UNION
SELECT '</batch>'
) X
ORDER BY CASE
WHEN X.XMLString LIKE '<?%' THEN 1
WHEN X.XMLString LIKE '<M%' THEN 2
ELSE 3
END

EXEC dbo.GenerateXML WITH RESULT SETS
((
[<?xml version="1.0"?> <batch>] VARCHAR(MAX)
))

You use the Execute XML as the source from the database and then you use a flat file as your destination with .xml as the extension.

This should solve your problem of outputting to an XML file.

Limiting SSIS to a Single File Type

A while ago I was having some issues with SSIS and moving files. There was a hidden file called thumbs.db

When SSIS was running the move file task it would fail because it can’t place duplicate files in the same location. The thumbs.db file was the problem. Even though it was hidden as a windows file, SSIS still saw it and wanted to move it.

This is where limiting SSIS to a specific file type saves you from this problem.

To do this you filter on the Foreach Loop Container. You select the container and then you click on Properties.

In the Properties right at the top you shall see FileSpec. There you can set it to the file type you would like to filter/limit for.

And that’s how you limit SSIS to a specific file type!

SSIS Error – the path for isserverexec.exe cannot be found

Recently been working with getting multiple SQL Instances on one machine to work. When I uninstalled an old one it affected the latest one. Then all of a sudden my ETL jobs failed. It did not make sense at all seeing as I removed a different instance.

Got the following error when trying to run my ETL jobs or when trying to deploy the jobs:
“the path for isserverexec.exe cannot be found”

Looking for solutions online, they recommend going into the registry. Which didn’t work for me.

I ended up using the install file and running it to add features to an existing connection. And there is was Integration services was not installed for the instance that I was getting the error with.

Installed SSIS on the current instance that needed it and all worked perfectly after that and the jobs ran as perusal.

Hope this helps someone else in the future.

Setting Up SSIS in SQL Server Management Studio

Before you can deploy a SSIS project you need to set up SSIS in SQL Server Management Studio. When installing SQL you will select to install SSIS, however, it does not setup the SSISSDB or configure any settings for SSIS.

Here are the steps for setting up SSIS in SQL Server Management Studio.

Step 1:
Right-click on Integrations Services Catalogs and select Create Catalog.

Step 2:
Check the box next to Enable CLR Integration and set a password for your SSISDB and click Ok.

You will then see you SSISDB listed under Databases and under Integration Services Catalogs.

Now you will be able to deploy your SSIS project to your server where you have set up SSIS.

The next post will be on deploying an SSIS project.

MDS Backup Strategies

Recently a colleague and I had a discussion about which method is the best to use for backing up MDS (Master Data Services) for disaster recovery. We came to the conclusion it all depended on the SQL environment version you were recovering to. Below are the 2 different methods for backing up an MDS database.

Model Backup
Pros:

  • Fast to create, can backup specific versions
  • Will always be able to deploy a model to a MDS install
  • Can be automated via SSIS
  • Can be restore to a server version that is +1, 0 and -1

Cons:

  • If you want to save backups of different models, then the models will have to be backed up separately.
  • Security is not retained; it will have to be redone. Can take about 2 minutes per user. On a small model this should not be a problem, however, when scaled out it could be very time consuming.
  • Saving more than one backup of a model would require a script/maintenance task to remove backups older than X amount of time.

When deploying the model, the option DeployClone must be used, not DeployNew. DeployNew will remove the MUID and therefore will not allow any changes to be deployed to that model at a later stage. DeployClone keeps it all intact and will allow for future updates.

Database Backup
Pros:

  • Security is retained
  • Simple to set-up and maintain
  • Easy to restore
  • Maintenance tasks to clean up old backups

Cons:

  • Cannot be restored to a server version less than the current version.

The database backup for MDS can be scheduled just like other backups. You can use the maintenance wizard to set up the backup and clean up task on a schedule.

If you know what version the disaster recovery environment will be on, for example the same version as the current MDS server version or higher, then it is simpler to use the Database Backup method. If you are uncertain of what version the disaster recovery version will be then you would want to use the Model Backup method in case you have to restore to a version less than the current MDS server version.

Hope this helps you to choose what is best suited for your environment!

Special thanks to Janlo Du Toit for the discussion on these methods 🙂