I’ve decided to post more on the BizTalk and HL7 integration projects as I found it extremely interesting. It is truly a fantastic area for my career and lots of opportunities out there. All I need to do is dive in and practice more….
Some useful links:
Recently, I am working on a WinForm application using C# 2008. Since I am pretty new to this area, I am going to log every simple or complex questions here for future reference.
Source data size should be equal or less than destination data size, otherwise, it will have truncation warning.
To be continued.
I am assigned to a task and this task sounds quite easy: load data from a csv file into an access 2003 table. I used SSIS2008 to complete this job and it appears the traditional unicode and nonunicode conversion errors was blocking the development. After investigation, I realized that all text columns in access are unicode-enabled default. Since all incoming columns are converted to DT_STR, that caused the conversion error. I am still trying to find a way to solve this issue, will keep updated once it’s found.
I am working on a small ETL task trying to extracting data from a workbook. Some cells do not seem to contain any values but ISBLANK() shows false.
After some research, I found out if you put =”” into the cell, it will not display anything but it’s not blank. This could be very misleading when in extracting data from workbooks. The best way is to save the excel file as a csv file and launch the notepad apps to replace “ ,” with “,” to remove any spaces in between commas because the space in the cell was shown as a space in csv.
When integrating SSRS reports into web apps, report view redistributables have to be installed against the web server. Otherwise, users can not view any SSRS reports.
I was trying to connect to a SSIS 2005 installation via SSMS 2008.
The first error I had is the classical access is denied. I googled around and realized that I need to get right access privileges.
Luckily, MS posted this article ( http://msdn.microsoft.com/en-us/library/aa337083.aspx) which is pretty helpful. However, our DBA followed the every single steps but I still can’t connect to. Finally, it turns out that we have to do the save steps on the higher level which is the My Computer. Basically,have to right click My Computer under computer service/computers. This step was missing on that article.
Secondly, when I try to connect to SSIS 2005, it pops up another error which is class is not registered. This is actually a known issue by MS but there is no solution at this moment (https://connect.microsoft.com/SQLServer/feedback/details/363922/connect-to-ssis-service-on-machine-servername-failed-class-not-registered?wa=wsignin1.0) . So for now, for SSIS 2005, we have to use SSMS 2005 to connect. However, SSMS2008 can be used to connect to DB 2005/2000, analysis service 2005. Odd…
There are two articles which did a nice job explaining this task
http://www.sqlis.com/post/Looping-over-files-with-the-Foreach-Loop.aspx
will write some experience through this project
Several items that I’d like to touch are listed below:
1. SSIS tips
Development
We have 3 types of packages: daily, weekly and monthly.
Daily pkgs will pick up new records everyday and aggregate into the summary table.
For daily changed reference data, we do not pick up untill the monthly pkgs kick in.
That’s our version to Slowly Changing Dimension (ideally, SCD should be used)
weekly pkgs will truncate the fact table and bring 3 month new records
monthly pkgs will truncate dimension tables and bring all records
Deployment
So far, the deployment is using file system and import pkgs via SSMS. That’s not the best way though but DBA feels more comfortable in this way
Debugging
We have created a sysssislog table which contains all log information. If data error or pkg error occurs , dba or am will get an email immediately.
When we direct error output to another table in OLE DB destination, make sure it’s using table/view instead of fast load. If fast load is used, if the stream hits any bad data, ssis will direct all data after the bad row into the error table no matter if it’s bad or not. Be careful!
2. SSAS tips
Development
Deployment
Debugging
3. Version Control (SVN)
@[System::MachineName] is the right way to put in the expression of connection manager. The property is ServerName. In this way, when pkgs are executed , it will automatically use the servername which will save tons of time on the dba’s side.
I have a few columns in three tables and want to convert them to nullable column. Below is the script
DECLARE @tbl_name varchar(255)
DECLARE @col_name varchar(255)
DECLARE @dt_type varchar(255)
DECLARE @alterCommand varchar(255)
if exists (
select
table_name,
column_name ,
DATA_TYPE
from INFORMATION_SCHEMA.COLUMNS
where IS_NULLABLE = 'NO'
and TABLE_NAME like '%ERR'
)
begin
DECLARE TblCursor CURSOR FOR
select
table_name,
column_name ,
DATA_TYPE
from INFORMATION_SCHEMA.COLUMNS
where IS_NULLABLE = 'NO'
and TABLE_NAME like '%ERR'
OPEN TblCursor
FETCH next FROM TblCursor
INTO @tbl_name,@col_name,@dt_type
WHILE @@fetch_status=0
BEGIN
SET @altercommand = 'alter table ' + @tbl_name +
' alter column '+@col_name+' '+@dt_type+' null '
EXECUTE(@dropcommand)
FETCH next FROM TblCursor INTO @tbl_name,@col_name,@dt_type
end
end
CLOSE TblCursor
DEALLOCATE TblCursor
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
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
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!
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.
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))
http://www.sqlusa.com/sqlformat/
It’s a really nice portal which converts ugly sql codes into pretty format
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.
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.
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
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
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…
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)
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
This one is based on the following thread:
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
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
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…
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….