SQL:SSIS registry package configuration

Create two registry key directly under HKEY_CURRENT_USER

Create a SSIS project with two tasks:

Execute Process Task and Data Flow Task

When you define the Execute Process Task, two properties are specified. They are:

After you double-click on the Data Flow task and Navigate to Data Flow pane, you add an OLEDB Connection Manager and set its connection string similar to the following:

Data Source=QUEEN;Initial Catalog=AdventureWorks;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;

Save the SSIS project, click on Control Flow pane, click SSIS menu and select Package Configuration Wizard. This time, choose the Registry as the SSIS Package Configuration Locations.

Then, when you execute your SSIS package, the value of Arguments in Execute Process Task will be replaced by the value of registry MyPackage1. The value of ConnectionString will be replaced by the value of MyPackage2 registry key.

You can run your package with different values without modify your SSIS package.
===

To save the package configuration to database table:

Open the dbo.[SSIS Configurations] table, identify the row with Configuration Filter set to example1, modify the configuredValue, and rerun the package:

SQL:SSIS package configuration

What does "SSIS uses the package configuration to update the values of properties within packages at run time" mean?

Save the package configuration to Environment Variables

Create environment variables:ConnectStr and MyFile

Create an SSIS project;

Create a Package Configuration

After the configuration, value of environment variable ConnectStr will replace the ConnectionString target property; The value of environment variable MyFile will replace the Arguments of Execute Process Task.

Your package will execute against different values when the values of Environment variables change.

Telnet and Exchange

http://support.microsoft.com/kb/153119

If you are having problems sending over SMTP between two Exchange servers, you Telnet on the sending server to connect to port 25 on the destination server.

1. Connect to your SMTP server
telnet vanbackup.vanstudents.com 25

2. Sent by a client to identify itself, usually with a domain name, the start of an ESMTP session.
ehlo test.com
250-VanBackup.vanstudents.com Hello [192.168.35.22]
250-TURN
250-ATRN
250-SIZE
250-ETRN
250-PIPELINING
250-DSN
250-ENHANCEDSTATUSCODES
250-8bitmime
250-BINARYMIME
250-CHUNKING
250-VRFY
250-X-EXPS GSSAPI NTLM LOGIN
250-X-EXPS=LOGIN
250-AUTH GSSAPI NTLM LOGIN
250-AUTH=LOGIN
250-X-LINK2STATE
250-XEXCH50
250 OK

3. tell the receiving SMTP server who the message is from
MAIL FROM: admin@test.com
250 2.1.0 admin@test.com....Sender OK

4.tell the receiving SMTP server whom the message is to:

(After the Subject line, press the ENTER key twice. To end the message, the last line must be with only one dot.) Temporiarily enable the anonymous relay, if SMPT virtual server does not allow the anonymous relay, you will get the error message: unable to relay for phil@eidam.com.

RCPT TO: phil@eidam.com
550 5.7.1 Unable to relay for phil@eidam.com
RCPT TO: phil@eidam.com
550 5.7.1 Unable to relay for phil@eidam.com
RCPT TO: administrator@vanstudents.com
250 2.1.5 administrator@vanstudents.com
DATA
354 Start mail input; end with .
Subject: Test Message


This is a test.
.
250 2.6.0 Queued mail for delivery

5. To close the connection, type
QUIT
===
Advanced testing

You can use a delivery receipt to test mail in both directions.

RCPT TO: phil@eidam.com notify=success,failure

Disable the open relay!!

SQL:FOREACH LOOP

Import data from many flat files into a table

Creating a destination table

USE [AdventureWorks]
GO
CREATE TABLE [dbo].[Currency](
[AverageRate] [numeric](18, 8) NULL,
[InceptionDate] [datetime] NULL,
[CurrentRate] [numeric](18, 8) NULL
)

Creating flat files and Save them in C:\AOL folder

Sample1.txt:

1.00010001;9/3/2001 0:00;0.99960016

1.00020001;9/4/2001 0:00;1.001001001

1.00020004;9/5/2001 0:00;0.99990001

1.00020002;9/6/2001 0:00;1.00040015

Sample2.txt

1.00050025;9/7/2001 0:00;0.99990001

1.00050026;9/8/2001 0:00;1.001001001

1.00050045;9/9/2001 0:00;1

1.00010001;9/10/2001 0:00;1.00040016

1.00020004;9/11/2001 0:00;0.99990001

1.00020004;9/12/2001 0:00;1.00110121
Creating one file importing package:

BIDS--new integration project--rename the package.dtsx to Lesson 1.dtsx

Connection to AOL Folder manager will access the c:\sample1.txt, sample2.txt, etc.

General--FileName: C:\AOL\sample1.txt

Advanced -- the column names and data type

Queen.AdventureWorks connection manager will access the AdventureWorks database on SQL Server:QUEEN.

Drag the Data Flow item onto the Control Flow pane and double click it to navigate onto the Data Flow pane

Double-click on Flat File Source and make sure that the column names are correctly mapped. And then, double-click on the OLE DB Destination, click on the Mapping, you should have the following:
Execute the package;

In Query Window, SELECT * FROM dbo.Currency. All data in sample1.txt should be imported into dbo.Currency table of AdventureWorks database.

===

Import data from many flat files --FOREACH LOOP

Copy the Lesson 1.dtsx package and paste it to create the Lesson 2.dtsx package;

Double-click the Lesson 2.dtsx package;

You are in the Control Flow Pane. Drag the FOREACH Loop Container onto the Control Flow Pane; double-click it to open the FOREACH LOOP Editor. Folder: C:\AOL and Files: Sample??.txt.

Variable Mappings:

Drag the Data Flow Task into Foreach Loop Container

Select "Connection to AOL Folder", Properties pane:

Execute the Lesson 2.dtsx package; Data from both Sample1.txt and Sample2.txt are imported into Currency table.

SQL:Integration Services-SSIS--Creating a data flow task

BIDS

Step 1:

Step 2:Double-click on the Data Flow Task to navigate onto Data Flow pane

The package is saved as dtsx file.

How to save a copy of dtsx file to MSDB database?

• Close the project
• Open it again
• In the solution explorer, double-click the project,

You can save the package to SQL Server.
==

How does the Lookup transformation work?

SQL:report security--default security roles

Browser
May view folders, reports and subscribe to reports.

The BROWSER cannot deploy a report and cannot upload files (resources).
Content Manager
May manage content in the Report Server. This includes folders, reports and resources.
My Reports
May publish reports and linked reports; manage folders, reports and resources in a users My Reports folder.
Publisher
May publish reports and linked reports to the Report Server. What is a resource?
When you upload a file to report server, the file is a resource.
Publisher can create a report by BIDS and deploy it to the REPORT SERVER. However, the publisher cannot RUN or execute the report definition (cannot Start execution from report definition without publishing it to Report Server). And, the publisher can upload a graphics address.jpg. But it cannot view the content.
Report Builder
May view report definitions.
===
How to assign the site-wide security?
http://localhost/reports
Site Settings --Security--Configure site-wide security

System Administrator
View and modify system role assignments, system role definitions, system properties, and shared schedules.
System User
View system properties and shared schedules and Execute Report Definitions.

By default, BUILTIN\Administrators is the member of System Administrator role and Content Manager role.

SQL:Linked report

A linked report is a shortcut to the base-report but with its own properties: parameters, execution, and history. For example, you create a base report with the following query:
Dataset 1:AdventureWorks
SELECT FirstName, LastName, e.Title
FROM HumanResources.Employee AS e
JOIN Person.Contact AS c ON e.ContactID = c.ContactID
WHERE e.Title IN (@Title)
Dataset2: TitleList
SELECT DISTINCT Title FROM HumanResources.Employee

Then, you can create a linked report with Title specified as Accountant,Marketing Assistant,Engineering Manager. When a user views the linked report, it only includes the 3 categories information.





==


cached report


When the first user opens the report, the report is placed a temporary copy of the report in a cache . When the subsequent users open the report, the report is returned from cache instead of being processing again. Cached reports will be expired at the set time.





report snapshot


When you want to create a report during off-peak period, you can schedule the report creation--snapshot. A snapshot report will not expire. It can be overwritten by the new one. A report history has many report snapshots.

In the site settings, you can define the number of snapshots are stored in a report history.

SQL:report subscription--Data-Driven--TimedSubscription

Every 10 minutes, reports are delivered to a shared folder \\\queen\aol. Each TITLE has a report created.

First, create a base report definition:

Dataset 1:AdventureWorks

SELECT FirstName, LastName, e.Title

FROM HumanResources.Employee AS e

JOIN Person.Contact AS c ON e.ContactID = c.ContactID

WHERE e.Title IN (@Title)

Dataset2: TitleList

SELECT DISTINCT Title FROM HumanResources.Employee

Data Source must be defined as "Credentials stored securely in report server".

Creating a data-driven subscription:

Defining the recipient list:

SELECT DISTINCT Title FROM HumanResources.Employee

Extensions:

Other extensions have the quite similar explanation.

Last, deliver reports based on the Title parameter. If you select the static value, all reports are same but are automatically delivered to all recipients.

From \\\queen\aol folder, you will see the reports created:

===

Deliver reports through E-Mail

First, Run "Reporting Service Configuration"

Email Settings must be turned GREEN.

And then, create a table or modify the existing table with Email Address data.

Last, create a data-driven subscription with email delivery selection.

==

Useful example

You create a report named SalesDeviation, which includes Sales Target and Sales Representative's actual sales. All sales representatives do not meet their goals must be automatically received a deviation report.

You can accomplish the task by creating a data-driven report.

Create a data-driven subscription to the SalesDeviation report. Then, for the subscription's recipient, specify a Transat-SQL query that returns the names of sales representatives who have fallen short of their goals.

SQL:report subscription--standard--one copy delivery

Why do you create a report subscription and how?

Assuming you have created a report with the following queries:
Dataset 1:AdventureWorks

SELECT FirstName, LastName, e.Title
FROM HumanResources.Employee AS e
JOIN Person.Contact AS c
ON e.ContactID = c.ContactID
WHERE e.Title IN (@Title)

Dataset2: TitleList
SELECT DISTINCT Title FROM HumanResources.Employee

The report is based on Dataset1:AdventureWorks. The report parameter:Title is automatically set. You need to modify some settings as shown below:
From report manager http://localhosts/reports, you can manually view the report--this is on-demand.



Scheduled report delivery is the report subscription.
First, access the report manager: http://localhost/reports and create the data source connection as follows --stored the User Name and its password in the report server--unattended.

Second, make sure the SQL Server Agent service starts.

Third, create a subscription for the report as PDF documents(deliver them to \\\Vision\SQLServer shared folder.

Every 5 minutes, a new report is created.
At the bottom of the screenshot, you notice the Report Parameter Values. You can select different value or values for the report.

If data of HumanResources.Employee table is changing constantly, the reports will reflect the change.

SQL:creating a parameterized report

My Computer Name is Vision. The default SQL Server is installed.
Business Intelligence Development Studio
New Project --Report Server Project Wizard


SELECT FirstName, LastName, e.Title
FROM HumanResources.Employee AS e
JOIN Person.Contact AS c
ON e.ContactID = c.ContactID
WHERE e.Title IN (@Title)

Click on Layout tab
Report Menu--Report Parameters
Because the query has a parameter (@Title), the report automatically includes it. Configuring the parameter as the follows:

Multi-value --checked

Non-Query--checked with 3 static values: Design Engineer, Accountant, and Marketing Assistant.

Deploy the report;

=================

Available value From query

Create a new dataset called title with statement "SELECT DISTINCT title FROM HumanResources.Employee":

Click on Layout tab, Report Menu--Report Parameters, this time select FROM QUERY with dataset:TitleDeploy the report;

http://localhost/reports

Click the View Report button;

SQL server and Kerberos Authentication

Apply Service Pack 2 on ALL SQL Server computers, including the clients. If the SP2 is not installed, Kerberos authentication may not be established.

Scenario

Domain:VIP.COM

Domain controller: QUEEN

The default SQL server instance runs under Local System account. The SPN is automatically registered.

setspn -l queenRegistered ServicePrincipalNames for CN=QUEEN,OU=Domain Controllers,DC=VIP,DC=COM: MSSQLSvc/queen.VIP.COM:1433

Workstation:XP with client components installed. After establishing a connection to QUEEN by specifying TCP:QUEEN, run the statement:

Select net_transport,auth_scheme from sys.dm_exec_connections

However, if you connect to the SQL Server instance from the QUEEN computer itself, even you specify the TCP:QUEEN, the auth_scheme is still NTLM.

If the service account is a domain account, e.g. VIP\sqlservice, you must manually register the SPN for it. SetSPN -A MSSQLSvc/Queen.VIP.COM:1433 VIP\sqlservice. You must raise your domain function level to Windows server 2003 level. In Active Directory Users and Computers, set the DELEGATION property to be trusted.

To force the connection to use Named Pipe, specify np:queen when connecting to SQL Server.

SQL:Linked server --SPN --Security Mapping

You must apply SQL Server SP2 on all SQL computers, including the clients.

• Creating a Linked server to TIGER SQL Server.

EXEC sp_addlinkedserver 'TIGER','SQL SERVER'
Making sure the security is set to self-mapping, as shown below:

Becaue the SQL Server Instance runs under the LocalSystem account, its SPN is automatically registered. You can check the SPN:
setspn -L QUEEN
Registered ServicePrincipalNames for CN=QUEEN,OU=Domain Controllers,DC=VIP,DC=COM:
MSSQLSvc/queen.VIP.COM:1433
Because the service account of TIGER SQL SERVER Instance is a domain user account without administrator privileges, you must register ServicePrincipalNames for CN=sqlservice,CN=Users,DC=VIP,DC=COM

setspn -A MSSQLSvc/Tiger.VIP.COM:1433 VIP\SQLService

• Creating two domain users: a1 and a2;


• Creating two logins for them in both SQL Server instances;


• In TIGER SQL Server, creating a database user in AdventureWorks database and assigning it the SELECT permission on AdventureWorks.Person.Address table;


• At laptop computer, login as user a1, open the query window that connects QUEEN SQL Server instance; Run the distributed query statement, SELECT TOP 6 * FROM TIGER.AdventureWorks.Person.Address


• At laptop computer, login as user a2, open the query window that connects QUEEN SQL Server instance; Run the distributed query statement, SELECT TOP 6 * FROM TIGER.AdventureWorks.Person.Address

No permission to run the statement.

• Understanding the IMPERSONATE button

If Windows user mapping exists, distributed query goes ahead. Otherwise, not be made. Or Be made without using a security context; or Be made using this security context--specify a user account.

• Explicit-mapping without impersonating
  

EXEC sp_addlinkedsrvlogin 'TIGER',False,'VIP\a1','SQLuser','password'

EXEC sp_addlinkedsrvlogin 'TIGER',False,'VIP\a2','SQLuser','password'

The TIGER SQL SERVER instance must be in MIXED security mode. The remote user can only be a SQL Server login.

"Login Failed for user 'NT Authority\ANONYMOUS' LOGON" shows.

Reason:

Lou:SQL Server does not have a SPN for its SQL Server service registered.

"Login Failed for user ' ', the user is not associated with a trusted SQL Server connection".
Reason:Norton SQL Server does not have the account defined and no permission assigned.

SQL Server Log

"The SQL Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x2098, state: 15. Failure to register an SPN may cause integrated authentication to fall back to NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies."

When both SQL Server instances start using Local System account or with a domain account that is a member of Administrators group,

How to enable the delegation tab shown above?

• Raise the domain function level to Windows server 2003


• SetSPN -A MSSQLSvc/Queen.VIP.COM:1433 sqlservice

If you have multiple instances installed within the same server, you don't need the SQL Service SPN registration and the service account is trusted for delegation. Even I manually delete the SPN registration and disable the service account delegation, the self-mapping of linked server works fine as long as both instances have the Windows login name defined and the necessary permissions assigned.

SQL:linked server

Working at DataServer1

Creating a linked server to DataServer2, which has the default SQL Server instance installed.


EXEC sp_addlinkedserver
@server='Linked_to_DataServer2',
@srvproduct='',
@provider='SQLNCLI',
@datasrc='DataServer2'

Or,

EXEC sp_addlinkedserver @server='DataServer2', @srvproduct='SQL Server'

====

If you have a named instance:SQLExpress installed, you could create a linked server as:

EXEC sp_addlinkedserver @server='Linked_to_DataServer2_Express', @srvproduct='', @provider='SQLNCLI', @datasrc='DataServer2\SQLExpress'

Or,

EXEC sp_addlinkedserver @server='DataServer2\SQLExpress', @srvproduct='SQL Server'

Notes: make sure that SQL EXPRESS accepts connection remotely.

===

How to create a linked server to ORACLE database?

• Provider Name:MSDAORA --Microsoft OLE DB Provider for Oracle
• SQL*Net alias for Oracle database --how to?

Oracle home--Net8 Assistant --Net8 Configuration > Local > Service Naming

• Enter a Net Service Name:MyOracle
• Select TCP/IP as the protocol
• Enter the hostname of the database server machine. The port number will be 1521 by default.

EXEC sp_addlinkedserver
@server='DataServer2 Oracle',
@svrproduct='oracle',
@provider='MSDAORA',
@datasrc='MyOracle'

===

If you connect to an access database (C:\msoffice\test.mdb), you run the following:

EXEC sp_addlinkedserver
@server = 'Connect_to_MyAccess',
@provider = 'Microsoft.Jet.OLEDB.4.0',
@srvproduct = 'OLE DB Provider for Jet',
@datasrc = 'C:\MSOffice\test.mdb'
GO

Explicit User Mapping

You cannot create a user mapping for a domain account.
EXEC sp_addlinkedsrvlogin 'Norton','FALSE','VANSTUDENTS\TEST','VANSTUDENTS\TEST','password'
EXEC sp_droplinkedsrvlogin 'Norton','VANSTUDENTS\TEST'

You should map a domain user to a SQL Server account in the other SQL Server. The following statement works fine for the distributed query, as long as the User alice has the necessary permission on the database objects.

EXEC sp_addlinkedsrvlogin 'Norton','FALSE','VANSTUDENTS\TEST','alice','alice'