Tuesday, August 17, 2010
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
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
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
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.
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.
System.ArgumentException: Value does not Fall within The expected Range.
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
zip -r ToBeNameOfZipFile.zip FolderName
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.
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.
Subscribe to:
Posts (Atom)