ssis package with linked server connection.

Question

Post reply

ssis package with linked server connection.

Nomvula

Hall of Fame

Points: 3770
More actions
December 17, 2015 at 6:50 am

#323088

Hi
I have a package that I've developed in the local environment and used linked server to simulate what I need to do in the production server which the client (company) is hosting.
the package is running perfectly in the local server cause I have access to the linked server '[PRODS-TOOLBOX\PRAGMA]' i'm connecting to, but now my problem is I need to build the package and deploy it in another server.
I need a way to be able to change the sql query on my dataflow from :
SELECT * FROM [PRODS-TOOLBOX\PRAGMA].LangerHeinrich.dbo.MeterReadings AS mr
REMOTE SERVER
SELECT * FROM [19-AIS-004\VIJEOHISTORIAN].VijeoHistorianCPSData.dbo.RunHours AS mr
so that I can deploy the package.

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

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

Phil Parkin SSC Guru Points: 245813 More actions · Answer 1

Create a string parameter in your package and set it to the query string.

Change your dataflow source to use what's in the parameter, not the hard-coded query.

Use different configurations to control the value of the parameter, depending on where the package is running.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Phil Parkin SSC Guru Points: 245813 More actions · Answer 2

By the way, SSIS is quite happy to handle direct connections to servers (ie, there may be no need to use a linked server within SSIS itself).

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Nomvula Hall of Fame Points: 3770 More actions · Answer 3

thanks for the response.

How do I create a string parameter? another thing I do have to create linked servers cause this server are hosted by my client. I use vpn connection to connect to them.

the scenario here is like this:

server 1 where the interface will be deployed

server 2 - where I have to extract data from and import to server 1

I connect to server 1 using vpn connection where I created the linked server for server 2 to get data from the table :[19-AIS-004\VIJEOHISTORIAN].VijeoHistorianCPSData.dbo.RunHours .