Flat Files Archiving in SSIS

In Microsoft Visual C# 2008:

2 Parameters need to declare:
FlatFilePath: String Type (To hold the URL)
SourceFileDir: String Type (To hold the Source folder URL)


using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.IO;

namespace ST_f580a072cab2401eab429eeca3b97e74.csproj
{
[System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{

#region VSTA generated code
enum ScriptResults
{
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
};
#endregion

public void Main()
{
int len = Dts.Variables["FlatFilePath"].Value.ToString().Length;
string ActiveDir = Dts.Variables["FlatFilePath"].Value.ToString().Substring(0,len -1);
string NewPath = System.IO.Path.Combine(ActiveDir, "ArchFolderName" + string.Format("_{0:yyyy-MM-dd_hh-mm-ss}", DateTime.Now));

System.IO.Directory.CreateDirectory(NewPath);

DirectoryInfo dirSrc = new DirectoryInfo(Dts.Variables["NewSourceFileDir"].Value.ToString());
DirectoryInfo dirDest = new DirectoryInfo(NewPath + "\\");
FileInfo[] Files = dirSrc.GetFiles();
if (Files.Length > 0)
{
foreach (FileInfo aFile in Files)
{
if (File.Exists(dirDest + aFile.Name))
{
File.Delete(dirDest + aFile.Name);
}
aFile.MoveTo(dirDest + aFile.Name);
}
}
Dts.TaskResult = (int)ScriptResults.Success;
}
}
}

Flat File Existence Check in SSIS

In Microsoft Visual Basic 2008:

Need to declare 2 variables
bolFileExists Boolean type
FlatFilePath string type (to store the flat file path)




Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.IO

System.AddIn.AddIn("ScriptMain", Version:="1.0", Publisher:="", Description:="") _
System.CLSCompliantAttribute(False) _
Partial Public Class ScriptMain
Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

Enum ScriptResults
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
End Enum

Public Sub Main()

Dim fileLoc, fileName As String
If Dts.Variables.Contains("User::FlatFilePath") = True Then
fileLoc = CStr(Dts.Variables("User::FlatFilePath").Value)
fileName = "FolderName\TxtFileName.txt"
If File.Exists(fileLoc + fileName) Then
Dts.Variables.Item("User::bolFileExists").Value = True
Else
Dts.Variables.Item("User::bolFileExists").Value = False
End If
Dts.TaskResult = ScriptResults.Success
Else
Dts.TaskResult = ScriptResults.Failure
End If
End Sub

End Class

Configuration of Performance Point Services on SharePoint 2010

To work on PPS firstly install the Dashboard Designer
PerformancePoint Dashboard Designer is a tool that you can use to create dashboards, scorecards, and reports and publish them to a SharePoint site. Dashboard Designer is part of PerformancePoint Services in Microsoft SharePoint Server 2010.
You use PerformancePoint Dashboard Designer to create, edit, and publish dashboards to Microsoft SharePoint Server 2010.
If PerformancePoint Services in Microsoft SharePoint Server 2010 is configured for your organization and you have the necessary permissions, you can install or launch Dashboard Designer. The best way to open Dashboard Designer for the first time is to use your web browser to install it from the Business Intelligence Center.
Important We recommend that you use Internet Explorer to launch Dashboard Designer for the first time. After that, you can launch Dashboard Designer by using the Start menu (Click Start > All Programs > SharePoint > PerformancePoint Dashboard Designer).
• If you are using Mozilla Firefox as your Web browser, make sure that Microsoft .NET Framework Assistant 1.1 is installed on your computer before you attempt to install Dashboard Designer.
• If you are using Safari as your Web browser, make sure to use either Internet Explorer or Firefox to install Dashboard Designer for the first time. Dashboard Designer does not launch from the Business Intelligence Center when you use the Safari browser.
The Business Intelligence Center is a pre-built Web site, or site template, that is optimized to help you manage the working elements of business intelligence (BI) reporting: scorecards, dashboards, data connections, status lists, status indicators, and so on. You can customize a Business Intelligence Center site as much as you like, or you can just start by using its built-in tools.
If you don’t have web application created follow below steps:
1. Firstly create the web application or use the already created web application and create a site collection Template with Business Intelligence Center.
Navigate to Central Administration Manage Application Management [Above Ribbon] New Web Application
After the first steps follow below steps:
If you already have web application created follow below steps:
1. Navigate to Central Administration Create Site Collection
a) Specify the in which Web application you want to create site collection
b) select Template Enterprise Business Intelligence Center
c) Specify the User who has admin rights

2. After that you will find the page (Business Intellengence Center)

Now it’s time to download the designer
3. In the Business Intelligence Center, click a section, such as Monitor Key Performance, Build and Share Reports, or Create Dashboards. Additional information about each area is displayed in the center pane: Click a link that says, “Start using PerformancePoint Services.”

4. The PerformancePoint site template opens. Click Run Dashboard Designer.
5. If you have the necessary permissions assigned to you in SharePoint Server, Dashboard Designer will install and open.

After you have opened Dashboard Designer at least once, you can open it by using additional methods, such as the following:
• Set up a shortcut on your desktop to the SharePoint site that contains your dashboard items. Then, double-click the desktop shortcut to open Dashboard Designer.
• Click Start, and then click All Programs. Locate SharePoint, and then click PerformancePoint Dashboard Designer.
Now Test whether you are able to communicate with Data Source and create the PPS components. Follow below steps:
Try to create new data source and see whether you are able to connect to particular database.
If not you will find error:

Now See whether the services is running on SharePoint
1. Navigate to Central Administration Applications Management [Services Application ] :Manage Services on server
a) See that Performance Point Services and Secure Stored Services should be started.
2. Navigate to Central Administration Applications Management [Services Application ] :Manage Services Application
a) See that Performance Point Services and Secure Stored Services and their proxy account should be started.
b) If No proxy account is there for above to services then need to create it by navigating to above ribbon New Performance Point Service Application or Secure Stored Services Application
c) See that both application services & there proxy account should be started.
3. Navigate to Central Administration Applications Management [Services Application ] :Configure Service Application Association
a) See that your web application should have Performance Point Service & Secure Stored Service Proxy account with default proxy group
Here’s the fix:
4. Navigate to Central Administration Manage Service Applications Click on Secure Store Service
 
5. Click Generate New Key from the ribbon
6. Type in a pass phrase and click OK
After this below message will appear
There are no Secure Store Target Applications in this Secure Store Service Application. You can create a new Target Application from the Manage Target Applications group in the Edit ribbon group
7. Create a Target application
a) Give ID, Display name, Contact email , target application type- individual
b) Specify the username & password depends on the admin rights
c) Specify the credential that as admin rights to allow access.
8. Go to the Target id and set the credentials

Set the credentials
9. Navigate to Central Administration Manage Service Applications PerformancePoint Service Application Performance Point Service Application Settings
10. Configure the unattended service account
11. Start using Dashboard Designer
12. Test whether the data source should connect to the appropriate database

Deploying SSRS reports through rs.exe utility

The rs utility is a script host that can be used to perform scripted operations. This utility is used to run Microsoft Visual Basic scripts that copy data between report server databases, create items in the report server database, publish reports, etc. The report servers that are configured for SharePoint integration mode do not support the rs utility.
Syntax:
rs {-?}
{-i input_file=}
{-s serverURL}
{-u username}
{-p password}
{-e endpoint}
{-l time_out}
{-b batchmode}
{-v globalvars=}
{-t trace}

-?
(Optional) Displays the syntax of rs arguments.
-i input_file
(Required) Specifies the .rss file to execute. This value can be a relative or fully qualified path to the .rss file.
-s serverURL
(Required) Specifies the Web server name and report server virtual directory name to execute the file against. An example of a report server URL is http://examplewebserver/reportserver. The prefix http:// or https:// at the beginning of the server name is optional. If you omit the prefix, the report server script host tries to use https first, and then uses http if https does not work.
-u [domain\]username
(Optional) Specifies a user account used to connect to the report server. If -u and -p are omitted, the current Windows user account is used.

-p password
(Required if -u is specified) Specifies the password to use with the -u argument. This value is case-sensitive.
-e
(Optional) Specifies the SOAP endpoint against which the script should run. Valid values are the following:
• Mgmt2010
• Mgmt2006
• Mgmt2005
• Exec2005
If a value is not specified, the Mgmt2005 endpoint is used. Note that the Mgmt2000 value is discontinued in this release and is no longer a valid value. For more information about the SOAP endpoints and deprecated features
-l time_out
(Optional) Specifies the number of seconds that elapse before the connection to the server times out. The default is 60 seconds. If you do not specify a time-out value, the default is used. A value of 0 specifies that the connection never times out.
-b
(Optional) Specifies that the commands in the script file run in a batch. If any commands fail, the batch is rolled back. Some commands cannot be batched, and those run as usual. Only exceptions that are thrown and are not handled within the script result in a rollback. If the script handles an exception and returns normally from Main, the batch is committed. If you omit this parameter, the commands run without creating a batch.
-vglobalvar
(Optional) Specifies global variables that are used in the script. If the script uses global variables, you must specify this argument. The value that you specify must be valid for global variable defined in the .rss file. You must specify one global variable for each –v argument.
The -v argument is specified on the command line and is used to set the value for a global variable that is defined in your script at run time. For example, if your script contains a variable named parentFolder, you can specify a name for that folder on the command line:
rs.exe -i myScriptFile.rss -s http://myServer/reportserver -v parentFolder="Financial Reports"
Global variables are created with the names given and set to the values supplied. For example, -v a="1" -v b="2" results in a variable named a with a value of "1" and a variable b with a value of "2".
Global variables are available to any function in the script. A backslash and quotation mark (\") is interpreted as a double quotation mark. The quotation marks are required only if the string contains a space. Variable names must be valid for Microsoft Visual Basic; they must start with alphabetical character or underscore and contain alphabetical characters, digits, or underscores. Reserved words cannot be used as variable names.
-t
(Optional) Outputs error messages to the trace log. This argument does not take a value.
Note: To run the tool, you must have permission to connect to the report server instance you are running the script against. You can run scripts to make changes to the local computer or a remote computer. To make changes to a report server installed on a remote computer, specify the remote computer in the -s argument.
RS.exe is located at \Program Files\Microsoft SQL Server\100\Tools\Bin. You can run the utility from any folder on your file system.
Using the RS.EXE utility:
• It is a Command-line utility.
• Used for performing Deployment and Administrative tasks programmatically.
• Executes the RSS file. [Reporting Services Script file]
Steps:
• Hope you created sample report here my report name is MyReport.rdl
• Create .rss file
o Open notepad and copy the below code and save it as .rss file
o Inside the code make appropriate changes i.e. DestinationFolder, ReportSource in file.

Dim definition As [Byte]() = Nothing
Dim warnings As Warning() = Nothing
Dim DestinationFolder As String = "MyReports"
Dim parentPath As String = "/" + DestinationFolder
Dim ReportSource As String

Public Sub Main()
Try

'Location of the rdl file
ReportSource = "C:\Reports"

rs.Credentials = System.Net.CredentialCache.DefaultCredentials
'get particular rdl file
If rs.GetItemType("/MyReports") = Microsoft.SqlServer.ReportingServices2005.ItemTypeEnum.Folder Then
rs.DeleteItem("/MyReports")
End If
'creates folder on report server
rs.CreateFolder("MyReports", "/", Nothing)
Console.WriteLine("Parent folder [MyReports] created successfully.")
'method called to create datasource used in the report
'CreateDataSource(name, extension, connectionstring)
CreateReportDataSource("MyDataSource", "SQL", "Data Source=(local);Initial Catalog=AdventuresWorks")
'method called to deploy the report
PublishReport("MyReport")

Console.WriteLine("Tasks completed successfully.")

Catch ex As Exception
Throw ex
End Try
End Sub

Public Sub CreateReportDataSource(ByVal name As String, ByVal extension As String, ByVal connectionString As String)
'Data source definition.
Dim definition As New DataSourceDefinition()
definition.CredentialRetrieval = CredentialRetrievalEnum.Integrated
definition.ConnectString = connectionString
definition.Enabled = True
definition.Extension = extension

Try
rs.CreateDataSource(name, "/MyReports", False, definition, Nothing)
Console.WriteLine("Data source: {0} created successfully.", name)
Catch e As Exception
Console.WriteLine("ERROR creating data source: " + name)
Throw e
End Try

End Sub

Public Sub PublishReport(ByVal reportName As String)
Try
Dim stream As FileStream = File.OpenRead(ReportSource + "\" + reportName + ".rdl")
definition = New [Byte](stream.Length) {}
stream.Read(definition, 0, CInt(stream.Length))
stream.Close()


rs.CreateReport(reportName, parentPath, False, definition, Nothing)
Console.WriteLine("Report: {0} published successfully.", reportName)

Catch e As Exception
Console.WriteLine("ERROR while Publishing report: " + reportName)
Throw e
End Try
End Sub

The sample RSS file creates a data-source, which points to the Northwind database on the (local) sql server instance using Windows authentication. The function used is: CreateDataSource(name, extension, connectionstring)

Example:
CreateDataSource("MyDataSource", "SQL", "Data Source= (local);Initial Catalog=Northwind")
It also publishes a sample report using the CreateReport function.
CreateReport(NameofReport, ParentFolder, Overwrite, ReportDefinition, Properties)

Example:
CreateReport(MyReport, /MyReports, False, definition, Nothing)
If report contains any external images, then it has to be also deployed as a resource. This is not required if the image is embedded in the report. The function used is: CreateResource(ImageName, ParentFolder, Overwrite, Definition, MIME type, Properties)

Example:
CreateResource(ImageName, parentPath, False, definition, "image/jpeg",Nothing)
For executing the rss file, open the command prompt and set the directory to D:\MyReports and run the following command:
rs -i MyRSS.rss -s http://localhost/ReportServer
Now your .rss file is created then placed this file to appropriate folder. i.e. C:/Users/Users_name/your rss file
• Open Command prompt Runcmd
• Run below cmd
rs -i MyRSS.rss -s http://localhost/reportserver

After successful execution, MyReports folder will be created on the report server and Mydatasource will get created and report will be deployed. It can be viewed using the Report Manager.

10-Minute Video Introduction to Scrum

http://www.allaboutagile.com/10-minute-video-introduction-to-scrum/