Downloading SSRS report from command line

rs Utility is used to download SSRS report from command line. rs.exe is normally found under program folder.

It requires a script to download file. Lets make the name of the script download.rss. It is vb.net and it should be in .rss extension file.

Public Sub Main()
  Dim format as string = "EXCELOPENXML"
  Dim fileName as String = "E:\DownloadReport\RfsReport\Major Projects for CSOC Reporting - "+Date.Now.ToString("MM-dd-yy")+".xlsx"
  Dim reportPath as String = "/RFS Reports/Major Projects - Singleview CSOC"

  'Delete file if exists
  If System.IO.File.Exists( fileName ) = True Then
System.IO.File.Delete( fileName )
  End If

  ' Prepare Render arguments
  Dim historyID as string = Nothing
  Dim deviceInfo as string = Nothing
  Dim extension as string = Nothing
  Dim encoding as string
  Dim mimeType as string
  Dim warnings() AS Warning = Nothing
  Dim streamIDs() as string = Nothing
  Dim results() as Byte

  rs.LoadReport(reportPath, historyID)

  results = rs.Render(format,  deviceInfo, extension, _
   mimeType, encoding,  warnings, streamIDs)

  ' Open a file stream and write out the report
  Dim stream  As FileStream = File.OpenWrite(fileName)
  stream.Write(results, 0, results.Length)
  stream.Close()
End Sub

Now run the following command

c:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\rs.exe -i c:\download.rss -s http://[Report server name]/Reportserver -e Exec2005'

BCP bulk export and import

To export data

declare @sql varchar(8000)select @sql = 'bcp [Database_name].[schema_name].[Table_name] out c:\Table_name.txt -c -t"|^" -T'

exec master..xp_cmdshell @sql



To import data

declare @sql varchar(8000)select @sql = 'bcp [Database_name].[schema_name].[Table_name] int c:\Table_name.txt -c -t"|^" -T'

exec master..xp_cmdshell @sql

SSRS exporing as Excel 97-2003 (.xls) and Excel 2012 (.xlsx)

Format should be excel, for downloading as .xlsx file format.

http://tocgjtim1pv.bns.bns/Reportserver?/RFS%20Reports/Major+Projects+-+Singleview&rs:Format=excel


Format should be EXCELOPENXML, for downloading as .xlsx file format.

http://localhost/Reportserver?/RFS%20Reports/Major+Projects+-+Singleview&rs:Format=EXCELOPENXML

SSIS Package batch export and import

1.

You can create batch export command by following sql.

select 'dtutil /SQL "' +[p].[name] + '" /ENCRYPT FILE;"F:\SSISPackages\'+[p].[name]+'.dtsx;1"'
from msdb.dbo.sysdtspackages90 [p]

Output command will be like this.

   dtutil /SQL "SSIS_Package_Name" /ENCRYPT FILE;"F:\SSISPackages\SSIS_Package_Name.dtsx;1"

Here 1 means, copy all the sensitive data with user key.
Here we are exporting SSIS packages from SQL server to F:\SSISPackages directory.

Run these batch commands in Souce SSIS server's command prompt.

Copy all the .dtsx file from source to destination server.

2.
Batch SSIS packages import commands can be generated by following sql

select 'dtutil /FILE "C:\Users\s5142805\Downloads\SSIS pkg\' +[p].[name] + '.dtsx" /COPY SQL;"/'+[p].[name]+'"'
from msdb.dbo.sysdtspackages90 [p]

Output will be like this.

dtutil /FILE "C:\Users\s5142805\Downloads\SSIS pkg\ABMCashOut - Import v2.dtsx" /COPY SQL;"/ABMCashOut"

Here we are importing SSIS packages from C:\Users\s5142805\Downloads\SSIS pkg\ to SQL server directory.

Run these batch commands in Destination SSIS server's command prompt.