SSIS Execute SQL Task

Question

SSIS Execute SQL Task

andylovegrove

Old Hand

Points: 394
More actions
July 29, 2010 at 10:43 pm

#225622

Hi, first post so please be gentle..
I wasn't sure where to put this post, it's 2008 but SSIS, please move or advise if incorrect.
I am writing an ETL and am making use of the SQL merge command to do an UPSERT. I have created a staging table in the same db using another schema and this gets populated and I then want merge to do its magic on the target.
My issue is when I add the code below to the task it fails saying incorrect syntax near '.' however when I paste it into SQL directly and run it, its fine. I can make it work by pasting it into a stored procedure and calling that from the sql task but I don't really want to have to do this for each and every table.
As a curiosity I ran the merge against SQL 2005 and got the same error, I realise this is 2008 functionality but as I have both on my machine I wonder if things have got confused?
Code which executes in SQL directly ok:
MERGE dbo.CustomerGroup AS Target
USING etl.CustomerGroup AS Source
ON Target.[GroupID]=Source.[GroupID]
WHEN matched THEN
UPDATE SET Target.[Code]=Source.[Code],
Target.[Name] = Source.[Name],
Target.[IsActive] = Source.[IsActive],
Target.[DateModified] = Getdate()
WHEN NOT matched THEN
INSERT ([GroupID],[ParentID],[Code],[Name],[IsActive],[DateModified]) values (Source.[GroupID],Source.[ParentID],Source.[Code],Source.[Name],Source.[IsActive],Getdate());

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

ksun SSC Veteran Points: 275 More actions · Answer 1

Just a suggestion, if you have both SS 2005 and 2008, check the connection manager you are using for the SQL task to see if you have right instance for the 2008 server. Most likely your 2005 is on the default instance(servername) and your 2008 will be on a named instance (servername\instancename)

HowardW One Orange Chip Points: 29109 More actions · Answer 2

Assuming you're connecting to the correct server, are you using 2005 or 2008 BIDS? The version of the SQL Client provider that ships with 2005 isn't compatible with the merge statement I don't think. You can get around it by using an ADO.Net connection, or encapsulating the code in a stored procedure and executing that from SSIS...

andylovegrove Old Hand Points: 394 More actions · Answer 3

Thank you both for taking the time to reply.

I can confirm the following:

1. Connection manager pointing to 2008 instance of SQL

2. I am using 2008 version of BIDS

I didn't really want to add stored procedures for each table so maybe I will group the merges and perform several in one stored procedure.

I have read a few things on .Net connection having slow performance so I may stick with what I have.

Possibly the multiple instances on my machine have caused some internal confusion as if this was a general issue I would have thought others would have experienced it.

Many thanks again

andylovegrove Old Hand Points: 394 More actions · Answer 4

Hi

Just as a follow up to this I found out what the issue was, this may have been the initial reply but was lost in translation from nerd to idiot, clearly me being the latter.

OK so the issue was that in the connections although the datasource was correctly pointing at the 2008 instance the provider was declared as SQLNCI.1 I changed this to SQLNCI10.1 and hey presto.

I have another issue now so I will post this as another question as I believe adding to this may be considered bad form in some circles.

Thanks all for your help, I hope this may be useful to someone else.