Thursday, October 30, 2008

This is something I don't want to write again...


CASE WHEN DATEPART(WEEKDAY, GETDATE()) = 1 THEN 'Sunday' ELSE
     CASE WHEN DATEPART(WEEKDAY, GETDATE()) = 2 THEN 'Monday' ELSE
         CASE WHEN DATEPART(WEEKDAY, GETDATE()) = 3 THEN 'Tuesday' ELSE
             CASE WHEN DATEPART(WEEKDAY, GETDATE()) = 4 THEN 'Wednesday' ELSE
                 CASE WHEN DATEPART(WEEKDAY, GETDATE()) = 5 THEN 'Thursday' ELSE
                     CASE WHEN DATEPART(WEEKDAY, GETDATE()) = 6 THEN 'Friday' ELSE
                         'Saturday'
                     END
                 END
             END
         END
     END
END AS Today

Wednesday, April 16, 2008

SSIS: Read/Write Package Variables Inside Script Task

To pull a SSIS Package variable into the Script Task you're working on use this


Dim vars As Variables
Dts.VariableDispenser.LockOneForRead("User::VariableName", vars)

Dim variable As String = vars("VariableName").Value.ToString()

vars.Unlock()


and then use your variable as you need.

To write to a SSIS Package variable in a Script task that you're working on, so future tasks in the package can use it, use the following


Dim writeVars As Variables
Dts.VariableDispenser.LockOneForWrite("User::VariableName", writeVars)
writeVars(0).Value = "Hello World"

writeVars.Unlock()


If you're using a variable to hold a value established in a Script task for use in further tasks, make sure its scope is not limited to just the script task we've made here.

Thursday, January 31, 2008

Error On SSIS Package Load

If you ever get:

System.ArgumentException: Value does not Fall within The expected Range.
at Microsoft.SqlServer.Dts.Runtime.Application.LoadPackage(String filename, IDTSEvents event, Boolean loadNeutral)...

Make sure you check the string of the package name.

Monday, October 22, 2007

Zipping Using Commands Through terminal

Here's the command to zip a folder or file using the terminal in OSX

zip -r ToBeNameOfZipFile.zip FolderName

Getting Just Date from DateTime

Here it is...

SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))

Thanks Mr. Lubarsky

Monday, June 18, 2007

Triggers

So, learned something interesting the other day about triggers.

Lets say you have a stored procedure that receives a handful of rows as mine did via XML. These rows could contain entries that need to be inserted into table A, others rows may contain entries that should update already existing entries in table A. So net net there could be 3 situations case where all rows are updates, all rows are new entries and must be inserted, or some rows are updates and some are inserts. My initial approach to this was as follows.

Insert Transactions
(
ID
, Date
, Qty
, Price
, Commission
)
select ID, Date, Quantity, Price, Commission,
from
@SomeTempTable tmp
where
ID not in (select ID from Transactions t where t.Date = tmp.date)

update Transactions
set
Price = tmp.Price
, Qty = tmp.Quantity
, Commission = tmp.Commission
from
@SomeTempTable tmp
, Transactions t
where
t.ID = tmp.ID and t.Date = tmp.date

Pretty simple stuff right? A simple update for existing entries and an insert for new ones. The complication came when the triggers came into play. One for Insert, another for update of the transaction table. I assumed that when there were no rows returned for the select (that feeds the insert) that no entries would be added, and thus there would be nothing for the trigger to do, if it in fact fired at all.

Turns out I was wrong.

My trigger looked something like this.

Declare @ID as int
select @ID = (Select ID from Inserted)
Declare @Date as datetime
select @Date = (select Date from Inserted)
Declare @Qty as float
select @Qty = (select Qty from Inserted)
Declare @Price as money
select @Price = (Select Price from Inserted)

(I then inserted these variables into another table.)

The trigger still fired, and because there was nothing in the INSERTED table to feed the trigger, those values returned as null. And because none of the columns in my table allowed nulls it threw a sql exception. For the longest time I couldn't admit to myself that the trigger was indeed firing w/ nothing to fill the variables, and the problem took a while to figure out.

So the lesson learned here is that if you have a trigger on a db event, it always fired when that keyword is executed, even if the execution of the keyword doesn't actually "do" anything.