When query is too long for OLE DB DFT, what are my options for bridging two servers?

Question

When query is too long for OLE DB DFT, what are my options for bridging two servers?

polkadot

SSChampion

Points: 13664
More actions
May 25, 2016 at 3:46 pm

#326608

Hi, I want to use a stored procedure inside a Execute SQL Task to pull ~100,000 rows from one server/db and to load results to another server/db. (using linked server qualified table names)
At deployment time I asked that a linked server be created, but I was told the linked server qualified table names inside my sproc would not be supported and was advised to use OLE DB task. I told them that the query inside sproc is too big to put inside OLE DB data flow task which is why I am using a sproc.
Deployment folks then advised:
1. Put your big query into a Script Component and assign that query to a package variable
OR
2. Use a file as a source for the query
Deployment folks are leaning toward using the second option.
Will someone advise on how to do one or the other?
Thank you.
--Quote me

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

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

John Rowan SSC Guru Points: 56440 More actions · Answer 1

You can use an OLE DB Source w/o having to place the stored procedure code inside the SQL command text. You put the stored procedure call into the SQL Command text.

EXEC dbo.MyStoredProcedureName

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

polkadot SSChampion Points: 13664 More actions · Answer 2

Deployment folks do not want me to use a sproc because it involves a linked server. I must supply the full query in the SSIS package and get around the character limit of the OLE DB data flow task, without resorting to a sproc in the Execute SQL Task. (because the called sproc has to reside on one server or another therefore requires a linked server name, which deployment team does not want to supply)

Will someone kindly advise on how to build an etl that will pull data from one server and dump it into another, with the constraint that I can't use a sproc and my query is too large to put into the OLE DB Data Flow Task?

--Quote me

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 3

polkadot (5/25/2016)
At deployment time I asked that a linked server be created, but I was told the linked server qualified table names inside my sproc would not be supported and was advised to use OLE DB task.

I agree with them because that would require 4 part naming and 2 of those four parts could change, which would require a code change.

Instead, use only two part naming and the name should be that of a synonym, which points to the linked server and underlying table using 4 part naming.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

polkadot SSChampion Points: 13664 More actions · Answer 4

Hi Jeff, Oh, I did mention synonyms to them.

The reason (verbatim) they gave:

"Using a linked server you may get into a double hop situation which we would not be able to resolve without assistance from the team that owns the other server."

BTW, both servers are owned by the same organization, but within the organization, one group owns one server and another group the other.

Is there anything I can do to accommodate the deployment team, by modifying SSIS?

Addendum:

I realized that since my big query contains several UNION ALLS. Am taking each query and putting into seperate OLE DB sources. In this way will be able to utilize OLE DB source and avoid linked server.

--Quote me