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 [...]

Read Full Post »

Get Quarter begin and end dates

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 »

Running Subscriptions

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*/ SELECT c.name, rs.scheduleid AS jobid, sub.DESCRIPTION, sub.laststatus FROM reportserver..catalog c INNER JOIN reportserver..subscriptions subON (c.itemid = sub.report_oid) INNER JOIN reportserver..activesubscriptions asubON (sub.subscriptionid = asub.subscriptionid) INNER JOIN reportserver..reportschedule rsON (sub.subscriptionid = rs.subscriptionid) This query will return the Report [...]

Read Full Post »

Using the Job ID to rerun a subscription

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 [...]

Read Full Post »

SQL Server Reporting Services Jobs

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 [...]

Read Full Post »