SSIS: Last Row is Being Skipped During Import

Fix the package by changing the TextQualifier. Edit the dtsx package using a text editor.

<DTS:Property DTS:Name="TextQualifier"><none></DTS:Property>

The text qualifier is getting changed to _x003C_none_x003E for some reason.

SSRS: Dynamic Parameters and Subject

Sample:

update EmailSubscription
set Parameters = '<ParameterValues><ParameterValue><Name>ReportDate</Name><Field>ReportDate</Field></ParameterValue><ParameterValue><Name>ReportType</Name><Field>ReportType</Field></ParameterValue></ParameterValues>',
DataSettingParameters = '<Field><Alias>ReportDate</Alias><Name>ReportDate</Name></Field><Field><Alias>ReportType</Alias><Name>ReportType</Name></Field>',
CommandTextParameters = ',ReportDate = CONVERT(VARCHAR, CAST(dateadd (day, -1,GETDATE()) AS DATE), 121),ReportType = case EOMONTH(dateadd (day, -1, getdate())) when dateadd (day, -1, getdate()) then 3 else 1 end',
SubscriptionName = 'KPIFinancialReportDailyAndMonthlyPDF',
Subject = '[case EOMONTH(dateadd (day, -1, getdate())) when dateadd (day, -1, getdate()) then ''Monthly Financial KPI Report'' else ''Daily Financial KPI Report '' end + CONVERT(VARCHAR, CAST(dateadd (day, -1,GETDATE()) AS DATE), 121)]'
where EmailSubscriptionID = 409

SSRS: Report in PDF has extra blank page

Issue: Report in PDF has extra blank page.
Solution: On the IDE, go to Report Properties, set ConsumeContainerWhitespace to True.

Talend: Special Characters are Changed into ?

Issue: I got a tRest component that connects to an API URL. It extracts some json data and outputs to csv. Some characters on my csv file are getting converted to "?"

Fix: Change the Encoding under Advanced Settings for FileOutputDelimited component from ISO-8859-15 to UTF-8

How To Fix Orphaned SQL Server User

First, make sure that this is the problem. This will lists the orphaned users:

EXEC sp_change_users_login 'Report'

If you already have a login id and password for this user, fix it by doing:

EXEC sp_change_users_login 'Auto_Fix', 'user'

If you want to create a new login id and password for this user, fix it by doing:

EXEC sp_change_users_login 'Auto_Fix', 'user', 'login', 'password'

SQL: String Function - Reverse

To get the filename of a fully-qualified path

declare @filename varchar(255) = '\\data\share\IT\yes\Myfile.csv'

select
substring(reverse(@filename),1,charindex('\',reverse(@filename)) ) 'Reverse',
reverse(substring(reverse(@filename),1,charindex('\',reverse(@filename)) ) ) 'Filename with slash',
reverse(substring(reverse(@filename),1,charindex('\',reverse(@filename)) -1 ) ) 'Filename',
substring(@filename,1,charindex(reverse(substring(reverse(@filename),1,charindex('\',reverse(@filename)) ) ),@filename)) 'Path',
SUBSTRING(@filename,1,LEN(@filename)-(CHARINDEX('\',REVERSE(@filename))-1)) AS SamePath

SQL: Query to get Database Role Members

Script used to get the roles in the database and their members.

create table ##RolesMembers
(
    [Database] sysname,
    RoleName sysname,
    MemberName sysname
)

exec dbo.sp_MSforeachdb 'insert into ##RolesMembers select ''?'', '''' + r.name + '''', '''' + m.name + ''''
from [?].sys.database_role_members rm
inner join [?].sys.database_principals r on rm.role_principal_id = r.principal_id
inner join [?].sys.database_principals m on rm.member_principal_id = m.principal_id
-- where r.name = ''db_owner'' and m.name != ''dbo'' -- you may want to uncomment this line';

select distinct * from ##RolesMembers
order by [Database], [RoleName]


----------------------

select
[Login Type]=
case sp.type
when 'u' then 'WIN'
when 's' then 'SQL'
when 'g' then 'GRP'
end,
convert(char(45),sp.name) as srvLogin,
convert(char(45),sp2.name) as srvRole,
convert(char(25),dbp.name) as dbUser,
convert(char(25),dbp2.name) as dbRole
from
sys.server_principals as sp join
sys.database_principals as dbp on sp.sid=dbp.sid join
sys.database_role_members as dbrm on dbp.principal_Id=dbrm.member_principal_Id join
sys.database_principals as dbp2 on dbrm.role_principal_id=dbp2.principal_id left join
sys.server_role_members as srm on sp.principal_id=srm.member_principal_id left join
sys.server_principals as sp2 on srm.role_principal_id=sp2.principal_id