BI - SSIS ( Basics II )

First Steps to create a Package\

Lessons on video are on

http://www.wrox.com/WileyCDA/Section/id-814197.html

1) Create a Connection Manager

• This will create a connection for the project or the entire solution.
• This can e change in the properties.

2) Define the Control Flow

• Define the tasks
• Recommendation - Change the default name for something more explicit

3) Package Encryption

• The XML by default have a default encryption using Windows user key
• To change use, Properties Panel -> EncryptSensitiveWithUserKey
• Review the encryption when deploy in a productive environment

4) Upgrade your package

Variables

In the blank space, left click and select Variables.

Then some window will be open to declare the variables.

Script Task

Examples of code:

• In Visual Basic, creating a variable

Dts.Variables(“strFileName”).Value = strInternal

• In Visual Basic, reading default variables.

Dts.Variables(“strFileName”).Value = “newvalue”

MsgBox(Dts.Variables(“strFileName”).Value)

• In Visual Basic, displaying a variable

Dim strInternal As String

strInternal = “test”

MsgBox(strInternal)

• In Visual Basic, modifying the Data Connection

Dts.Connections(“AdventureWorks2012”).ConnectionString = _

“Data Source=localhost;Initial Catalog=AdventureWorks2012;” + _

“Provider=SQLNCLI10.1;Integrated Security=SSPI;”

• In Visual Basic, validating if a file exist

If File.Exists(Dts.Variables(“strFileName”).Value) Then

Dts.Variables(“bolFileExist”).Value = True

Else

Dts.Variables(“bolFileExist”).Value = False

End If

• In Visual Basic, checking if the file is in use.

Try
File.SetLastAccessTime(Dts.Variables(“strFileName”).Value, Today)
Catch e As Exception
Dts.Variables(“bolFileInUse”).Value = True
End Try

Execute SQL Task

http://www.wrox.com/WileyCDA/Section/id-814197.html

You can execute a SQL command and the result be assigned to a variable.

Flat File Source

Use Fast Parse as best practice

1. Right-click the Flat File Source or Data Conversion Transformation, and

click Show Advanced Editor.

2. In the Advanced Editor dialog box, click the Input and Output Properties tab.

3. In the Inputs and Outputs pane, click the column for which you want to

enable Fast Parse (shown in Figure 18-18).

4. In the Properties window, expand the Custom Properties node, and set the

FastParse property to True.

5. Click OK.