SSIS workflow is really amazing when you have to add some automatism in your data flow processes. But sometimes is better to make same checks before start with a really dangerous BULK operation.
One of the most noise stuff I have to do is to launch insert of new records when I receive updates from web (web services, XML data). But when I do this I'm always afraid about duplicated rows.
Ho to check if a row exist, in SSIS?
SSIS Package contains a component called lookup. Very intuitive name. So if you need to insert only some new rows in a table you can build a workflow like this:
So we have three components:
- A OLEDB Datasource that reads data from the source table
- A LookUp component that check every row, if exists in the destination table. If doesn't it redirects the rows in the error event.
- A Destination SQL that receive only the new rows, the rows that are in error in the lookup component.
Easy and automatic.
Premise: if you come from Europe you will find this post very strange. The reason is that in Europe you can take an International driving license and use it where you want.
But not in Bermuda!! If you want to take a Bike (Motorbike) or a Car you have to get the local Driving license. In addition you have to take 4 steps to drive everything. The first one is for the 50cc bike, then 100cc, then Automatic car and finally manual car.
How to do it?
First you have to go to the TCD (Transport Control Department) and get the book (for only 3 USD) and a Form for your doctor (the local one ...).
After this you have to pay around 60 USD to take the write exam on a computer, but the good thing is that if you mistake the exam you can retake it without paying anymore.
Now, at this point, you have to take the driving exam, but beware, because here is not like Europe, there aren't driving school, so you need some local that give you his bike for a while to take practice and also, the driving test.
I will let you know when I will take my.
Usually when we build a query we start the .sql file, or command in the query editor with a simple command ... CREATE PROCEDURE ... bla bla bla.
Ok but if I'm building a database from scratch is very probably that my procedure will be modified a lot of time. So every time we have to launch the command DROP PROCEDURE, or ALTER PROCEDURE.
This sounds good if you are building your object. But in case of you want to store as a BACKUP PROCEDURE some scripts in the network, is better to produce this scripts as ATOMIC and AUTOMATIC.
To accomplish this task, we can use a function really useful in SQL SERVER, IF [OBJECT] EXIST, DROP IT!
IF EXISTS (SELECT * FROM [dbo].[sysobjects]
WHERE ID = object_id(N'[schema].[procedure]') AND
OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [schema].[procedure]
GO
CREATE PROCEDURE [schema].[procedure]
AS
BEGIN
So now you can run the script as much time as you want.
If you have a SQL 2005 machine with a (x64) installation, some of the system queries have a different name, like [some_command]sys_64. So when you try to connect your powerfull SQL2005 to an old remote SQL2000, probably in x32 version, you can receive a strange error like Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO".
In many forums you can find a link to a microsoft KB that explains to you that you have to install the SP4 in the old SQL and maybe everything will done.
Usually I like to know why something doesn't run ...
So, when you execute from SQL2005 a query like
select * from sql2000.mybase.dbo.mytable
SQL Server 2005 x64 runs the following query on remote SQL2000 server:
exec [mybase]..sp_tables_info_rowset_64 N'mytable', N'dbo', NULL
So you can also try to add this stored in the Remote SQL2000 server, in the master database:
create procedure sp_tables_info_rowset_64
@table_name sysname,
@table_schema sysname = null,
@table_type nvarchar(255) = null
as
declare @Result int set @Result = 0
exec @Result = sp_tables_info_rowset @table_name, @table_schema, @table_type
It works and you don't need to run strange Package on your critical machine.
This year is a new year for me for everything, why?
New location (Bermuda), new job (IT Manager) and new technologies (no more NET dev but SQL Dev). So why don't put in my life another new interest in different certifications? I have decided to became a MCITP (Microsoft Certified IT Professional). Here you can find a detailed description of all the seminaries.
For MS SQL 2005 there are 3 ways. All the ways have the same startup. Exam 70-431 TS: Microsoft SQL Server 2005 – Implementation and Maintenance. With this exam you gain the MCTS SQL 2005. Now you can choose one of the three available profiles.
MCTIP Database Developer.
Microsoft Certified IT Professional: Database Developer certification demonstrates that you can design a secure, stable, enterprise database solution by using Microsoft SQL Server 2005.
Exams
70-441 - PRO: Designing Database Solutions by Using Microsoft SQL Server 2005
70-442 - PRO: Designing and Optimizing Data Access by Using Microsoft SQL Server 2005
MCTIP Database Administrator.
Microsoft Certified IT Professional: Database Administrator (MCITP: Database Administrator) is the premier certification for database server administrators. This certification demonstrates that you can keep up with your enterprise business solutions 24 hours a day, 7 days a week.
Exams
70-443 - PRO: Designing a Database Server Infrastructure by Using Microsoft SQL Server 2005
70-444 - PRO: Optimizing and Maintaining a Database Administration Solution by Using Microsoft SQL Server 2005
MCTIP Business Intelligence.
Microsoft Certified IT Professional: Business Intelligence Developer certification demonstrates that you can design analysis solutions, data transformations, and reports. Business intelligence developers design and implement multi-dimensional database models (logical and physical), data marts, data warehousing, data transforms, data analytics, and reporting solutions. This includes programming and customizing servers that use Multidimensional Expressions (MDX), customer transforms, and custom reporting solutions. Business intelligence developers are typically employed by medium-sized to large organizations.
Exams
70-445 - TS: Microsoft SQL Server 2005 Business Intelligence – Implementation and Maintenance
70-446 - PRO: Designing a Business Intelligence Infrastructure by Using Microsoft SQL Server 2005
I would like to get in first step the Database Administrator, that is the most hard to get. And in a second time I will start with the Database Developer. But if I have to honest what I love is the ETL process, so for me the dream is to get also the complete knowledge of how to build a strong Data warehouse.
Starting from now stay tuned because I will post a lot of info about SQL.
Happy Birthday to me ... 
Yep today I'm 30 so please connect to my blog, make a comment and say me HAPPY BIRTHDAY!!
This week-end in Bermuda was a long one, because yesterday there was the Bermuda Day, so one more day to stay on the Beach.
Another interesting thing is that I have started my Kite-Surf course!! So in first I want to share with you some interesting photos, not anything amazing but something to start with.
First thing I can say to you, Man finally I have found some interesting guys where I can talk and stay on the week-end. Also most of them, incredible, are IT technician like me.
There are guys from all the part of the world, for example, one from Sweden, one from England, one from South Africa and more and more ...
Let's start with some information about kite-surf in Bermuda.
Where to play
As you know, Kite-surfer are not well viewed from other tourist or Beach consumer. One of the reason I think is because Kite-surfer stole a lot of space in the beach with their instruments and kites ...
Another thing, in my opinion, because kite-surfer run very fast near the beach. But hey guys, I'm here to enjoy!!
So in Bermuda there are a lot of amazing Beaches where you can take the sun, make snorkeling, but only two for Kite-surfing. The reasons are more than one. First you need good wind direction, Second you need a safe place to land, with no obstacles, no risk to your health, Third you need the only two beaches in Bermuda where Coral are less!
Somerset Long Bay Beach - is the most famous for the Kite-Surfer, at the end of the West cost of the Island in the Sandy's Parish. Not easy to find, and this is another reason because I like this beach. For more details check the Map.
This beach is really quite, with a big place with green grass and trees, so if you want to relax after kiting, you can also take a sleep in this quite place. But not in Sunday where some People come to see the kite-surfers ...
Elbow Beach - This beach is not so far from Long bay, but is in the warwick Parish. I have never been there so I cannot give you more info about. I'm really sorry.
Last one, Official School
Hey man, Kite-surfing is not for quite people I think. Also it's a really dangerous sport if you don't know everything in the right way. So, in first, you need an official instructor and an official instrumentation to be sure that you will not injury anytime. First - keep safe!
The Official school is Kio and you can go on their web-site and search for a close Teacher to you country.
Here in Bermuda you have to call Simon, is the only official one here, and He's really cool.
Take care.
I guys finally I have some time to spent for you.
During this days I was and also I'm too busy so as you can imagine I have not time to blog.
Today I was to the Electricity Society for open my account. So if you are an emigrate you need:
- Passport Number and Passport copy
- Your landlord near you to sign the contract
- Your house contract
In 15 minute the www.belco.bm company will sign you for a new contract.
Obviously because you are a non-bermudians customer you have to deposit 150.00 BDM that they will give you back when you will stop your contract.
But don't worry about, because in Italy we have this also for Italian Citizens!!
For pay the bill you have more than one solutions, you can pay directly in the Company office, when you receive the bill (I suppose it's monthly), or you can pay in a ATM or also you can achromatized the process with your bank account or finally you can pay every time via E-banking.
Very nice.
Before starting to this fantastic Island, I have read a lot of forum, blogs and also personal experiences.
Anything was really true. I have prepared a long detailed list in excel format, with all my things inside, also Clothes, Electrical tools, Documents and more ...
I have also prepared my suitcases separating every one thing in a Plastic bag, I have divided the suitcases by things typologies (Clothes, Health ...) but when I was in the Customs ...
First - don`t be aware about Customs, they are friendly, sunniest, and in special case they are very very kind and compressively.
Second - actually in Bermuda the law about import has changed a little bit. I haven`t paid anything duties. Yes now when you come in the Island as a worker, the first time you don`t pay duties so please don`t be aware.
Finally I want to please all stupid people that write fantastic evil stories about Customs to STOP. Bermuda is a fantastic friendly place, people are very kind and also Police and Immigration employees are very compressively with stranger.
I finally can say that I haven`t had problem in the Airport and I am very happy to finally be in a place where stranger are good accepted more than in other European Countries.
I remember to you for any kind of question go directly to Bermuda Customs web site (search in my old posts), and ask everything you are in doubt.
Have a nice experience to like mine!!