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