Posts Tagged ‘SQL Server’

Using sFTP in SQL Server SSIS

On several occasions I have had to use SSIS to export data from a SQL database, and then transfer that data to a third party via sFTP.  SQL Server SSIS has an FTP task built in but it does not natively support sFTP.  There are several commercial products (Eldos Software, /n Software, CozyRoc) out on the market that can be used to add that functionality into SSIS but I am going to talk about using a free utility called WinSCP to accomplish this task.

The first step is to download and install WinSCP, you will need to do this on the SQL server that will be running the SSIS package.  Once you have installed WinSCP you should create a connection to the distant sFTP server in WinSCP so you can test and confirm that you can connect to it.  Create and name a saved session so that you can call that session from your script later.  I will name this session “MySecureFTP”.


Once you have done that you will need to create a WinSCP script to transfer your files.  For examples of scripts see the WinSCP documentation.  Here is a very basic script that I created to copy a csv file.

option batch on

option confirm off

open MySecureFTP

put c:\outbound\*.csv /inbounddirectory/


After testing the script by running it from a command prompt and confirming that it works, you are ready to step into SSIS and call it from your workflow.

In Microsoft Visual Studio in your SSIS Package, go to the Control Flow tab and from your toolbox add an “Execute Process Task” to the workflow.  It should look like this after you’ve added it.


Double click the Execute Process Task to open the Task Editor.  You should now have a window like this.


Go to the Process section on the left and then you will need to fill out some information.


In the executable line you will need to fill out the correct path to the WinSCP executable.  Mine was in the (x86) directory but it may be different for your computer.

Then in the Arguments line you will need to put the /script= switch and then the path to that script.  I also included the /log= switch to log the execution to a file.  That way I can go back and review the FTP connection and make sure everything was a success. 

You may need to add an entry for the WorkingDirectory if you do not use absolute paths in your script.  I didn’t require it. 


Now you can build your SSIS package and deploy it.  That’s all that you need to do. 


One last comment that I’ve found is that if you’ve created a stored session in WinSCP it stores the value in the registry under the HKey\Current_User folder and depending on what credentials you use to run the SQL Job you may not have access to that registry key.  One option to fix this is to switch WinSCP to use an INI so all the stored sessions are stored in the INI.  That way the script can still access the stored session.  Another way to fix this is to set the SQL Job to run as a different user but that involves setting up a Proxy in SQL Server Agent which I will cover in another post.    


Read Full Post »

This is a script I wrote to use when I need to get the quarter begin and end dates for SQL.

declare @date datetime

declare @begdate datetime

declare @enddate datetime

set @date = getdate()

--get first day of CURRENT qtr 

set @begdate = dateadd(q, datediff(q, 0, @date), 0)

--get last day of CURRENT qtr 

set @enddate = dateadd(q, datediff(q, 0, dateadd(q, 1, @date)), -1)

select @date as today, @begdate as QtrBegin, @enddate as QtrEnd

Read Full Post »

Once you have kicked off your report subscription you might want to see the status. Again, if you don’t want to log onto the SQL Server Reporting Services web interface you can do this with a simple SQL Query.

/*Active (Running) subscriptions*/



rs.scheduleid AS jobid,



FROM reportserver..catalog c

INNER JOIN reportserver..subscriptions sub
ON (c.itemid = sub.report_oid)

INNER JOIN reportserver..activesubscriptions asub
ON (sub.subscriptionid = asub.subscriptionid)

INNER JOIN reportserver..reportschedule rs
ON (sub.subscriptionid = rs.subscriptionid)

This query will return the Report Name, the JobID, description and the LastStatus.

Read Full Post »

The previous post showed a sql script that is useful for finding all your Reporting Services jobs. Now that you have that information, lets say you’d like to kick off a scheduled report that either already ran, or you’d like to run again but you don’t want to have to step through all the settings for that subscription in the Reporting Services web interface. Instead you can use the Job ID that you got in the previous result set and use it to start the report job. I use this script every month to manually kick off a scheduled report that generates client invoices.

/*  Report Server Rerun subscription  */

--Execute Monthly Web Statement Subscripion

EXEC msdb..Sp_start_job @job_name = ‘A251BE76-0453-4A8F-8C98-69439B881B23’

--Client Holdings

EXEC msdb..Sp_start_job @job_name = ’57AD46EF-A46F-41A7-83F3-9430B51CEDE8′

--Standard subscription

EXEC msdb..Sp_start_job @job_name = ‘DC1C9E50-D18E-4A69-9452-2274D21232D7’

Read Full Post »

This script is a useful one that I have at my office.  I use it often to find all the SQL Server Reporting Services scheduled reports that exist on a SQL Reporting Server instance.  It list the job name, job description, the scheduled date and time and the status of the last time it was run.

/*Information about the subscriptions on your Report Server*/

SELECT c.name,

       rs.scheduleid as JobID,






FROM   reportserver..catalog c WITH(nolock)

       INNER JOIN reportserver..subscriptions sub WITH(nolock)

         ON ( c.itemid = sub.report_oid )

       INNER JOIN reportserver..reportschedule rs WITH(nolock)

         ON ( c.itemid = rs.reportid

              AND sub.subscriptionid = rs.subscriptionid )

       INNER JOIN reportserver..schedule sch WITH(nolock)

         ON ( rs.scheduleid = sch.scheduleid )

       INNER JOIN msdb..sysjobs sj WITH(nolock)

         ON ( CAST(rs.scheduleid AS SYSNAME) = sj.name )

       --sysname equivalent to nvarchar(128)

       INNER JOIN msdb..sysjobschedules sjs WITH(nolock)

         ON ( sj.job_id = sjs.job_id )

       INNER JOIN msdb..sysschedules ss WITH(nolock)

         ON ( sjs.schedule_id = ss.schedule_id )

-- WHERE c.Name like ‘ISS %’
-- A good way to filter by report name.


Read Full Post »