How to decipher SSIS Error Code

Here is a nice article. Have to use dtsmsg.h and use calculater to convert error code into Hex

http://blogs.msdn.com/helloworld/archive/2008/07/25/how-to-decipher-understand-ssis-error-code.aspx

Technorati Tags: SSIS

Refresh Intellise in SSMS 2008

Sometimes the intellise feature in SSMS 2008 needs to be updated to reflect the changed table structure. Hit Ctrl-Shift-R to refresh intellise. Otherwise, it won’t pick up the changes

Find last day of any month

I found the following tips from this blog

http://blog.sqlauthority.com/2007/08/18/sql-server-find-last-day-of-any-month-current-previous-next/

----Last Day of Previous Month
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))

----Last Day of Current Month
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0))

----Last Day of Next Month
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+2,0))

Quite useful!

SSIS OLE DB command

 

This task is in the data flow task. We are trying to update a table through a series of joining with other tables. However, we do not want to output the result into a temp table and update the original table with this temp table. OLE DB command is used in this case as it can perform sql command for each row. The good side is that the original table only contains around 300 rows. So the performance is not a bit deal. If the original table contains a large volume of data, then it’s better to use output into a staging table and use the set operation to update the table.

how to handle the Numeric datatype = datetime in the where clause

In a where clause, I need to put billing_period = effective_date. The billing_period is numeric(6,0) while the effective_date is datetime. I’ve come up with the following code.

Step 1 is to convert billing_period into varchcar

Step 2 is to convert effective_date into varchar. This step requires peeling each part from the date type and padding the month and day.

cast(rut.BILLING_PERIOD as varchar(6))+'01' =
cast(datepart(yy,b.effective_date) as varchar(4))+
REPLICATE('0',2-len(cast(DATepart(mm,b.effective_date) as varchar(2))))+cast(DATepart(mm,b.effective_date) as varchar(2))+
REPLICATE('0',2-len(cast(DATepart(dd,b.effective_date) as varchar(2))))+cast(DATEPART(dd,b.effective_date) as varchar(2))

Nice online SQL formatter

http://www.sqlusa.com/sqlformat/

It’s a really nice portal which converts ugly sql codes into pretty format

How to create a time dimension in a table?

  with mycte as

(
select cast('2009-01-01' as datetime) DateValue
union all
select DateValue + 1
from    mycte  
where   DateValue + 1 < = GetDate() + 10

  )

select DateValue

from    mycte

OPTION (MAXRECURSION 0)

This script will generate date starting from 2009-01-01 to 10 days after current date.

Datetime VS Datetime2 in Sql Server

I ran into this issue when the source data in the Oracle has the date of 1/1/0001. The datatype in the column of Sql server is datetime and SSIS kept telling me the conversion failed. After careful investigation, we found that datetime in sql server is used to store date and time from 1/1/1753 to 12/31/9999. Hence, we need to use datetime2 type which stores date from 1/1/0001 to 12/31/9999. A lesson to learn.

Break PK constraints among tables and drop tables within a script

From time to time, I need to drop tables that have PK constraints applied already. Deleting manually is really cumbersome as you have to take care of dependencies first. Is there a script to break all constraints among tables so that I could apply the following script to drop tables?The example shows how to drop tables with the name of HUB%

DECLARE @id varchar(255) DECLARE @dropCommand varchar(255)

if exists (select '['+TABLE_SCHEMA+']'+'.'+table_name from INFORMATION_SCHEMA.TABLES a where a.TABLE_NAME like 'HUB%' or a.table_name like 'HUB%') begin

DECLARE tableCursor CURSOR FOR

select '['+TABLE_SCHEMA+']'+'.'+table_name from INFORMATION_SCHEMA.TABLES a where a.TABLE_NAME like 'STG_STARS%' or a.table_name like 'HUB%' and a.TABLE_SCHEMA = 'dbo'

OPEN tableCursor FETCH next FROM tableCursor INTO @id

WHILE @@fetch_status=0 BEGIN SET @dropcommand = 'drop table ' + @id EXECUTE(@dropcommand) FETCH next FROM tableCursor INTO @id end

end CLOSE tableCursor DEALLOCATE tableCursor

Below is the script to drop all constraints

DECLARE @database nvarchar(50) DECLARE @table nvarchar(50) set @database = 'ERS_DS' --set @table = 'tabs' declare @schema nvarchar(128), @tbl nvarchar(128), @constraint nvarchar(128) DECLARE @sql nvarchar(255) declare cur cursor fast_forward for select distinct cu.constraint_schema, cu.table_name, cu.constraint_name from information_schema.table_constraints tc join information_schema.referential_constraints rc on rc.unique_constraint_name = tc.constraint_name join information_schema.constraint_column_usage cu on cu.constraint_name = rc.constraint_name where tc.constraint_catalog = @database and tc.table_name like 'HUB%' open cur fetch next from cur into @schema, @tbl, @constraint while @@fetch_status <> -1 begin select @sql = 'ALTER TABLE ' + @schema + '.' + @tbl + ' DROP CONSTRAINT ' + @constraint exec sp_executesql @sql fetch next from cur into @schema, @tbl, @constraint end close cur deallocate cur

When upload packages to the IS server, can’t execute packages anymore

why?

It seems like a 64-bit issue. Some connection managers won’t work on this version. The following blog describes the same symptom I am facing now:

http://tsutha.blogspot.com/2006/05/getting-ssis-packages-to-run-on-64-bit.html

Seems it’s related to the ProtectionLevel under the security section of package property

Here is the detailed thread

http://social.msdn.microsoft.com/Forums/en/sqlintegrationservices/thread/1a7c23c8-5f4f-4891-981b-5e1f4e7d33fe

A case where a hint has to be applied

I usually do not use hint but today I have to use one

Below is the script

SELECT /*+ index(cdr_customer_mv CDR_CUST_MV_CSTCD_ACCT_YR) */ 
    *
  FROM ds_prod.cdr_customer_mv
WHERE  acct_period >=
TO_NUMBER (TO_CHAR (ADD_MONTHS (SYSDATE - 9, -3), 'yyyymm'))

 

The weird thing is that if I do not use > and only use =, then there is no need to use the hint as the explain plan indicates the usage of index. However, when I apply the >, it starts to scan the full table.

  Anyway, it’s good to know this…

Oracle Numeric type to SQL SERVER Numeric

In DDL, one column is defined as CDR_ID                NUMERIC,

when I run the script against Sql server, the output is CDR_ID (numeric(18,0), null).

Seems SQL SERVER automatically sets the default as (18,0)

What’s is database collation?

Here is a good article

http://help.godaddy.com/article/4203

Collation controls the way string values are sorted

This is another good article on the topic of collation

http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/07/12/883.aspx

Drop multiple tables with schema having \

This one is based on the following thread:

http://dbaspot.com/forums/sqlserver-faq/228429-problem-alter-schema-dbo-transfer-owrd-julainih-authorlist.html

I need to modify a bit to accommodate my scenario.

 

DECLARE @id varchar(255)
DECLARE @dropCommand varchar(255)

DECLARE tableCursor CURSOR FOR
    select '['+TABLE_SCHEMA+']'+'.'+table_name from INFORMATION_SCHEMA.TABLES
where TABLE_SCHEMA = 'STRATOS\donso'

OPEN tableCursor
FETCH next FROM tableCursor INTO @id

WHILE @@fetch_status=0
BEGIN
    SET @dropcommand = 'drop table ' + @id
    EXECUTE(@dropcommand)
    FETCH next FROM tableCursor INTO @id
    print 'drop table'+@id
END

CLOSE tableCursor
DEALLOCATE tableCursor

Tricky part:

1. Use information_schema to get the list of tables that belong to user STRATOS\donso

2. Since the schema contains ‘ \ ‘, we must use [] to escape. Otherwise, SSMS will keep popping error msgs. The solution is below

    select '['+TABLE_SCHEMA+']'+'.'+table_name from INFORMATION_SCHEMA.TABLES
where TABLE_SCHEMA = 'STRATOS\donso'

Change the ownership in a table

ALTER SCHEMA ers TRANSFER dbo.ers_time

It means change the table ers_time from dbo to ers

  The result is that dbo.ers_time is replaced by  ers.ers_time

Script used to perform this job

with mycte as
(
select cast('2009-02-01' as datetime) DateValue
union all
select DateValue + 1
from    mycte  
where   DateValue + 1 < = GetDate()
  )
  insert into dbo.ers_time(call_dt)
  select datevalue from mycte
OPTION (MAXRECURSION 0)

 

Useful link:  http://www.kodyaz.com/articles/sql-server-dates-table-using-tsql-cte-calendar-table.aspx

Lessons learned from pilot BI project

In order to build this pilot BI project, we utilized SSIS 2008 to create the datawarehouse and used SSAS to create the OLAP cube on top of that.  In the next few blogs, I’ll post the tips and lessons learnt from the process in order to share with those who are interested and to remind myself from time to time…

Excel 2007 can not connect to SSAS cube

Weird problem! My excel keeps saying the server is either busy or not started…why?

 

Finally, the helpdesk solved this mystery by……reinstalling MS Office….