Joseph Michael Pesch
VP Programming

Visual Studio 2005 Business Intelligence Studio Can't Open SSIS Script Task

by 4. June 2010 18:16

SSIS package that’s developed in Microsoft Visual Studio 2005 may have a script task inside that doesn’t open when you click on the "Design Script" button.  In my case this was after I installed SQL Server 2008 locally. I had to re-install SQL Server 2005 sp2 (after installing SQL Server 2008) and then the script tasks are ok again.


Oracle | SSIS | Visual Studio

Oracle Common Table Expression, Oracle Partition

by 3. March 2010 16:23

Common table expression allows "with" statement to assign a temporary table name to a select statement (similar to SQL server "using" statement).  Also, the below example shows how to use the Oracle "partition" function to group results (this sample shows how to get the first row of each group of data).

with MaxStatus as
  select * from
    , row_number() over (partition by h.ap_num order by h.change_date) as RowNumber
    from history h
  ) where RowNumber = 1
select * from MaxStatus



Oracle to_date() Function

by 12. March 2009 18:58

Date formatting in Oracle

select to_date('02/17/2009 12:22:01 PM', 'mm/dd/yyyy HH:MI:SS AM') from dual


Tags: ,


Oracle Equivelant of SQL sysobjects and syscolumns

by 22. October 2008 15:36

Tables: all_tables Columns: all_tab_columns



Oracle | SQL Server

SQL Server Identity Columns vs. Oracle Sequence Numbers

by 12. August 2008 21:28

In SQL server you create an identity column like the sample below which creates an identity column with seed value of 1 and increment value of 1.  Then when you insert records the column value is incremented and set automatically.

SQL Server Sample:
create table #MyTable(RecID int identity(1,1), MyData varchar(50))
insert into #MyTable(MyData) values('This is a test')
insert into #MyTable(MyData) values('This is another test')
select * from #MyTable
drop table #MyTable

Not so in Oracle.  There you create the integer column; however, you must manually increment and set the column value as part of your insert statement.  You can use a Sequence object (after you create one) as shown below.

create sequence MySequence minvalue 1 maxvalue 999999999999 start with 1 increment by 1 cache 20
create table imdw.MyTable(RecID int, MyData varchar2(50))
insert into imdw.MyTable(RecID, MyData) values(MySequence.nextval, 'This is a test')
insert into imdw.MyTable(RecID, MyData) values(MySequence.nextval, 'This is another test')
select * from imdw.MyTable
drop table imdw.MyTable
drop sequence MySequence

In both cases you should get the following result set:

 RecID MyData 
 1 This is a test 
 2 This is another test


Oracle | SQL Server