How to effectively transfer and filter data source in SSIS via a variable

Last weekend, I got this question from our customer. I think that the most effective way is to use DataReader source in SSIS. Please refer to the following steps:
1. Drag a DataReader Source to your Data Flow panel, set the Connection
Manager and initial SQL Command. You can just input a string without any
filter now, such as "SELECT * FROM Orders".

2. Drag a OLE DB Destination to your Data Flow panel, set the DataReader
Source as its input, then set its Connection Manager and destination table,
and map the columns.

3. Right click the Data Flow panel, select Variables, create a variable
(assuming that the name is SQL_FilterSource) with String data type, and
initialize its value with a T-SQL statement like "SELECT * from orders".
You can also use a filtered query statement as its value. Also, you need to
set its property "EvaluateAsExpression" as True.

4. Click the Data Flow panel, select the Properties window, select
Expressions, click the button (...), select the Property as [DataReader
Source].[SqlCommand], input "@[User::SQL_FilterSource]" to the expression
field. You can also do this via clicking the button "...", and drag the
variable to the Expression field.

5.Save your package.

Then you can run it via dtexec command like the following:
dtexec /FILE "F:\Projects\ISPFilter\ISPFilter\filter_source_transfer.dtsx"
/SET
"\Package\FilterSourceTask.Variables[User::SQL_FilterSource].Properties[Valu
e]";"SELECT * FROM ORDERS WHERE ORDERID<10800"

--Wacle

Implement INITCAP function in SQL Server

To convert a normal string into initial captial form, SQL Server does not provide the same function as well as Oracle. However we can implement by ourselves.
For example:
create function initcap (@text varchar(4000))
returns varchar(4000)
as
begin
declare @counter int,
@length int,
@char char(1),
@textnew varchar(4000)

set @text = rtrim(@text)
set @text = lower(@text)
set @length = len(@text)
set @counter = 1

set @text = upper(left(@text, 1) ) + right(@text, @length - 1)

while @counter <> @length --+ 1
begin
select @char = substring(@text, @counter, 1)
if @char = space(1) or @char = '_' or @char = ',' or @char = '.' or @char = '\' or @char = '/' or @char = '(' or @char = ')' or @char='"' or @char='''' or @char=':' or @char='?' or @char='"'
begin
set @textnew = left(@text, @counter) + upper(substring(@text, @counter+1, 1)) + right(@text, (@length - @counter) - 1)
set @text = @textnew
end

set @counter = @counter + 1
end

return @text
end

How To: Check whether all columns with same name in a database are identity columns

DECLARE @identity_col_name nvarchar(20)
DECLARE @tablename nvarchar(50)
DECLARE @colname nvarchar(50)
DECLARE @strSQL nvarchar(4000)
DECLARE @flag int

--SET identity column name
SET @identity_col_name = 'DEX_ROW_ID'

CREATE TABLE #temp_identities
(
TableName nvarchar(50),
ColumnName nvarchar(50),
IdentityValue int,
Flag int
)

DECLARE ident_cursor CURSOR
FOR
select t.name as TableName,c.name as ColName, 'Flag'=case COLUMNPROPERTY
(c.id,c.name,'IsIdNotForRepl')
when 1 then 2
else 1
end
from sysobjects t join
syscolumns c on t.id=c.id where t.xtype='U' and c.name=@identity_col_name
and COLUMNPROPERTY (c.id,c.name,'isidentity')=1
order by TableName

OPEN ident_cursor
FETCH NEXT FROM ident_cursor
INTO @tablename, @colname, @flag
WHILE @@FETCH_STATUS = 0
BEGIN
SET @strSQL = N'INSERT INTO #temp_identities VALUES(''' + @tablename +
''',''' + @colname + ''',' + 'IDENT_CURRENT(''' + @tablename + '''),' +
cast(@flag as varchar(10)) + ')'
exec sp_executeSQL @strSQL
FETCH NEXT FROM ident_cursor INTO @tablename, @colname,@flag
END

CLOSE ident_cursor
DEALLOCATE ident_cursor

INSERT INTO #temp_identities select t.name as TableName,c.name as
ColumnName, 0 as IdentityValue, 0 as Flag from sysobjects t join
syscolumns c on t.id=c.id where t.xtype='U' and c.name=@identity_col_name
and COLUMNPROPERTY (c.id,c.name,'isidentity')=0
order by TableName

/* Output the temp result */
--0 = No
--1 = Yes
--2 = Yes (Not for Replication)
SELECT * FROM #temp_identities order by TableName
DROP TABLE #temp_identities

How to retrieve the SQL Server startup service account

DECLARE @serviceaccount varchar(100)EXECUTE master.dbo.xp_instance_regreadN'HKEY_LOCAL_MACHINE', N'SYSTEM\CurrentControlSet\Services\MSSQLSERVER', N'ObjectName', @ServiceAccount OUTPUT, N'no_output'
SELECT @Serviceaccount

SQL Server 2005 brings light on Row-to-Column and Column-to-Row conversion

In SQL Server 2005, a new feature has been included to facilitate conversions of row-to-column and column-to-row.
Before SQL Server 2005, it will spend us much efforts to write such a T-SQL statement. Now, everything changed since
PIVOT and UNPIVOT have been introduced.
Let us see two typical conversion scenarios which can be resolved by PIVOT and UNPIVOT now in SQL 2005.

Column-to-Row Conversion
Take the sample database Adventureworks for example. I would like to query the non-flagged and flagged vacation
hours in average. Generally we can get the result by executing:
SELECT SalariedFlag, AVG(VacationHours) AS AVG_VHours FROM HumanResources.Employee GROUP BY SalariedFlag
The output is something like the following:
SalariedFlag AVG_VHours
----------- ------------
0 12
1 30

However if I would like to get the result as something like:
NON_FLAGGED(0) FLAGGED(1)
------------------------- ----------------
12 30

it is very convenient to get this by executing the following statement:
SELECT [0] AS NON_FLAGGED, [1] as FLAGGED
FROM
(
SELECT SalariedFlag, VacationHours
FROM HumanResources.Employee
) AS H
PIVOT
(
AVG(VacationHours)
FOR SalariedFlag IN ([0], [1])
) AS Pvt


Row-to-Column Conversion
Think about the following scenario, if there is a table like this:
create table SALES
(
SALESID int identity(1,1) primary key,
SH int,
BJ int,
XM int,
NJ int,
COUNTDATE datetime
)

INSERT INTO SALES VALUES(3000,4000,5000,6000,'2007-05-01')
INSERT INTO SALES VALUES(4000,4000,8000,7000,'2007-06-01')
INSERT INTO SALES VALUES(9000,10000,7000,7000,'2007-10-01')

I would like to retrieve the results like this:
2007-05-01 SH 3000
2007-05-01 BJ 4000
2007-05-01 XM 5000
2007-05-01 NJ 6000
2007-06-01 SH 4000
......

Now we can use UNPIVOT to get the result as we expected:
SELECT COUNTDATE, LOCATION, SALEVALUE
FROM
(
SELECT SH,BJ,XM,NJ,COUNTDATE FROM SALES
) V1
UNPIVOT
(SALEVALUE FOR LOCATION IN (SH,BJ,XM,NJ)) AS V2
ORDER BY COUNTDATE,LOCATION

Brief intro of Recovery Models in SQL 2005

What is Recovery Model?
Recovery Model is actually a transaction log way. It has three types: Simple, Bulk-Logged and Full.
Simple recovery model leads to database engine mimimal transaction log for most operations and truncating the transaction log after each checkpoint. In this mode, "backup log" and "restore log" operations are not allowed;
Bulk-logged recover model leads to mimimal transaction log for bulk operations such as BULK INSERT and SELECT INTO. You can restore the database to the end of the log backup.
Full recover model has the database engine to log all operations into the transaction log.

Query and Change Recovery Model
We can query database recovery models by running the following statement:
SELECT name, recovery_model_desc FROM sys.databases

To change the recovery model of a database, you can use SSMS (SQL Server Management Studio) or run "ALTER DATABASE SET RECOVERY {SIMPLEBULK_LOGGEDFULL}".

-------------------------------------------------------------------
This article is owned by Wacle.
If you have any questions, please contact wacle.wang@gmail.com
-------------------------------------------------------------------

Configure SQL 2005 Database Mail by using stored procedures

Today I tried the new feature of sending email via SQL Server 2005 database mail which replaces the old SQL mail in SQL Server 2000. Of course, using SQL Server Management Studio is a convenient way to configure the database mail, but I would like to talk about how to use stored procedures to configure and send an email in SQL Server 2005.

The configuration components in SQL Server 2005 include two parts: one is Database Mail Account configuration; the other is Database Mail Profile configuration. SQL Server 2005 provides the following stored procedures in msdb database to manage the configurations:
sysmail_add_account_sp
sysmail_add_principalprofile_sp
sysmail_add_profile_sp
sysmail_add_profileaccount_sp
sysmail_configure_sp
sysmail_delete_account_sp
sysmail_delete_principalprofile_sp
sysmail_delete_profile_sp
sysmail_delete_profileaccount_sp
sysmail_update_account_sp
sysmail_update_principalprofile_sp
sysmail_update_profile_sp
sysmail_update_profileaccount_sp

Assume that you have an internet email account testemail@126.com, you would like to send an email from the email address in SQL 2005. Then you can refer to the steps as follows:
1. Configure your SQL Server instance to allow SQL Database Email.
sp_configure 'show advanced options',1
Go
reconfigure
Go
sp_configure 'Database mail XPs',1
Go

2. Add an email account.
execute msdb.dbo.sysmail_add_account_sp
@account_name='Test DBMail Account',
@description='Test mail account for Database mail',
@email_address = 'testemail@126.com',
@display_name='Test Database mail',
@mailserver_name = 'smtp.126.com',
@mailserver_type = 'SMTP',
@port = 25,
@username = 'testemail',
@password = 'testemail',
@use_default_credentials = 0,
@enable_ssl = 0
Go

3. Add an profile
execute msdb.dbo.sysmail_add_profile_sp
@profile_name='Test DBMail Profile',
@description = 'Profile for testing Database mail'
Go

4. Relate the account to the profile
execute msdb.dbo.sysmail_add_profileaccount_sp
@profile_name='Test DBMail Profile',
@account_name='Test DBMail Account',
@sequence_number = 1
Go

5. Add profile principal and set it as the default profile
execute msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = 'Test DBMail Profile',
@principal_name = 'public',
@is_default = 1
Go

After the above configurations, test sending an email via the following stored procedure:
execute msdb.dbo.sp_send_dbmail @profile_name = 'Test DBMail Profile',
@recipients = 'test@myemailserver.com',
@subject = 'Test Database Mail',
@body = 'Phenix Sun lost in the game 6. Nash cried to go back home.'
Go

To check if your email has been sent, you can run "select * from msdb.dbo.sysmail_mailitems" to check if the sent_status is 1. If your email fails to be sent, run "select * from msdb.dbo.sysmail_event_log" to check the error information.

As you can see, it is also very easy of configurations to send an email in SQL Server 2005.


-------------------------------------------------------------------
This article is owned by Wacle.
If you have any questions, please contact wacle.wang@gmail.com
-------------------------------------------------------------------