SQL Server-ing you

A blog about SQL Server

Archive for the ‘Uncategorized’ Category

SSIS – How to get the last date of a month?

leave a comment »

I need to get the first and last date of a month in SSIS. These will be used to process partitions in my SSAS Cube afterwards.

The riddle answer is: The last day of the month is the day before the first day of next month.

1) Create 3 variables: Date, MonthBeginning and MonthEnding

2) Change MonthBeginning property “evaluate as expression to true” and edit the expression as follow
(DT_WSTR,4)YEAR(@[User::Date])¬† + “/” + (DT_WSTR,4)MONTH(@[User::Date]) + “/01”
3) Change MonthEnding property “evaluate as expression to true” and edit the expression as follow:
(DT_WSTR,4)YEAR(DATEADD(“dd”,-1,DATEADD(“mm”,1,(DT_DATE) @[User::MonthBeginning]))) + “/” + (DT_WSTR,4)MONTH(DATEADD(“dd”,-1,DATEADD(“mm”,1,(DT_DATE) @[User::MonthBeginning]))) + “/” + (DT_WSTR,4)DAY(DATEADD(“dd”,-1,DATEADD(“mm”,1,(DT_DATE) @[User::MonthBeginning])))

I like my dates in the yyyy/mm/dd format hence the big expression above otherwise it returns something looking like this: mm/dd/yyyy. I hate this.

yyyy/mm/dd format makes it easier for me since I just need to replace / with “” (empty string) to get a nice integer 20091001 and 20091031 which will represent the foreign key in my fact table ūüôā yay.

Then you can go along and use this to partition your cube and so on.

To summarize: we get the first day of the month we seek, say 2009/10/01. We add one month: 2009/11/01 and we remove one day: 2009/10/31. Bingo. Last date of the month.

Written by Nicolas de Fontenay

2009/10/29 at 8:18 am

a send-mail function for powershell

leave a comment »

I’ve been working a lot with powershell.

Our data warehouse processes are going to be controlled by powershell which in return calls SSIS procedures.

Every steps are logged for success or failure. Everything is tracked.

At powershell level however I needed to be informed if there’s a string malformation when dtexec is called for exemple.

So I’ve built this send-mail function which I think will be useful to anyone required to send mails:

function Send-Email
{
param
(
[string]$message,
[string]$subject,
[string]$to,
[string]$from,
[string]$attachment
)
$SmtpClient = new-object system.net.mail.smtpClient
$MailMessage = New-Object system.net.mail.mailmessage
$SmtpClient.Host = “smtp.mail.com”
$mailmessage.from = ($from)
$mailmessage.To.add($to)
$mailmessage.Subject = $subject
$mailmessage.Body = $message
if ($attachment)
{
$att = new-object Net.Mail.Attachment($attachment)
$MailMessage.Attachments.Add($att)
}
$smtpclient.Send($mailmessage)
}

usage:

send-mail “some message” “my subject” “to@anaddress.com” “from@me.com”

or

send-mail “some message” “my subject” “to@anaddress.com” “from@me.com” “D:\Test.txt”

The function checks whether $attachment is null or not and will attach a document if it finds any.

Written by Nicolas de Fontenay

2009/09/01 at 7:59 am

Posted in Uncategorized

Tagged with , , ,

Picture needs

leave a comment »

I definitely need to be able to store pictures here for my SSIS needs.

Written by Nicolas de Fontenay

2009/07/17 at 2:26 am

Posted in Uncategorized

10 good habits with SQL Server

leave a comment »

This is a number of good habits to have when working with databases:

1) Don’t use dynamic SQL.¬† That is concatenate some string to form a SQL query then execute it using
exec (@mysqlcommand). This will forbid you and your users from re-using the execution plan set for this query. If it’s frequently used, it can be sinking your performance.

2) Store your tables outside of the Primary file group. Split them by function. As your DB grows and your company is successful you will be able to move your data files belonging to a filegroup to another disk improving your performance. Taking advantage of RAID 10 and RAID 5.

3) Store your tempdb to a different disk from where your transaction log and data files are (that would be 3 disks or Raid system). If¬† you can’t afford that, group tempdb and transaction log together and put your data files to a different disk.

4) Know your database intimately. How much does it grow every months, how much is the business expected to grow. Is it smooth or does it double suddenly. That will allow you to define the size of your data file when you create it. Save the disk space for this data file allows for the data inserted in table belonging to it to be contiguous. Less fragmentation, better performance.

4) Look at the execution plan.¬† If you’re learning how to write good¬† sql, looking at it and trying different ways will help. On the long run, you’ll learn how to identify bottle necks and understand the different methods used by the SQL Server engine.

5)  The join command is not a filter. That one is a tricky one. exemple:

if I do :

select a.column1, a.column2, b.column1
from tablea a
inner join tableb b
on a.id = b.id
where b.column1 =¬† ‘somefilter’

That will produce good results as long as the database is small.

But as it grows big and reach a few million rows, the table a will still need to be scanned from top to bottom to define which rows matches b.id for which b.column1 = ‘somefilter’.

If tablea is the big table, that’s a serious problem. Answer: filter a. Is it running on all rows or is it for the last day of inserted data? it could be this instead:

select id,  column1,  column2
into #tempa
from tablea
where date = @yesterday

select a.column1, a.column2, b.column1
from #tempa a
inner join tableb b
on a.id = b.id
where b.column1 =¬† ‘somefilter’

6)¬† If you’ve got to use the like command,¬† try to avoid using filters looking like this: ‘%myfitler%’.
You won’t be able to use an index with it because the SQl Server engine can’t tell where to look. It could be anything. Instead use:¬† like ‘myfilter%’ That way the SQL Server engine knows that you’re looking for something starting with ‘m’.

7)¬† Give rights on a need to know basis and ask for reasons when you’re being asked for privileges. Data are sensitive and in the end you’re the one in charge.

8) Do good backups and test them regularly. This point would be a long post in itself but… Really. I’ve seen places making backups and feeling safe like that. Better feel truly unsafe than wrongly safe.

9) When deploying solutions, use methods that won’t leave password clear in text files. The best way I’ve found is to stick my stored procedures in SSIS which is then protected either with my user key or a password of its own stored in a configuration file.

10) Have fun doing what you do. Databases are sensitive. Pressure is often high. If you’re doing a good job, nobody will notice because it means nothing is breaking, so really have a lot of fun and pleasure implementing something great. It will make you feel good ūüôā

Written by Nicolas de Fontenay

2009/07/14 at 4:03 am

SSIS odds

leave a comment »

I Just realised that it’s possible for a component to hide behind a container such as for loop or sequence container.

If a component is suddenly missing, check under those !

Written by Nicolas de Fontenay

2009/07/08 at 9:56 am

Posted in Uncategorized

Tagged with ,

How to get a table definition if it’s a temporary table

leave a comment »

It works pretty much like for any other tables. You will need to be using the correct database first:

USE MyDatabase
GO

select            a.name, b.name, c.name, b.max_length, b.[precision], b.scale
from              sys.objects a
inner join    sys.columns b
on                   a.[object_id] = b.[object_id]
inner join    sys.types c
on                   b.system_type_id = c.system_type_id
where¬†¬† ¬† ¬† ¬† ¬† [type] = ‘U’
and¬†¬† ¬†¬†¬† ¬†¬†¬† ¬† ¬†¬† a.name = ‘some_table_name’
order by      column_id

If it’s a temporary table however there’s a little trick.

The first thing to change is trivial:

USE tempdb
GO

select            a.name, b.name, c.name, b.max_length, b.[precision], b.scale
from              sys.objects a
inner join    sys.columns b
on                   a.[object_id] = b.[object_id]
inner join    sys.types c
on                   b.system_type_id = c.system_type_id
where¬†¬† ¬† ¬† ¬† ¬† [type] = ‘U’
and¬†¬† ¬†¬†¬† ¬†¬†¬† ¬† ¬†¬† a.name like¬† ‘#temp%’

order by      column_id

Things to know:

1) A temporary table is a user generated table and therefore its type is ‘U’
2) The result can be a lot more than what you would expect. For #temp, you can get #temp_____________________________________________0000000039A1

This is to ensure that 2 tables with identical names from 2 different users won’t be created.

Written by Nicolas de Fontenay

2009/05/06 at 5:00 am