SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Different Connection Strings in SQL Task


Different Connection Strings in SQL Task

Author
Message
eseosaoregie
eseosaoregie
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1450 Visits: 1229
I have created a SSIS package that selects records from a table in one database The data retrieved is used to populate a temp table in another database. Both databases are on the same server, ServerA. The query is executed within a SQL Task. The connection string for this SQL Task pertains to ServerA. The SQL used is as follows:


[ReferenceData_Dax].Sys.objects WHERE object_id = OBJECT_ID(N'[TMP_CRALTNUMBERS]') AND type in (N'U'))
DROP TABLE [ReferenceData_Dax].TMP_CRALTNUMBERS
GO

SELECT N1.[CREATIONID],

STUFF
((SELECT ' ; ' + N2.CRNUMBERTYPEID + '|#|' + N2.CRNUMBER + CASE N2.CRSOCIETYCODE WHEN '0' THEN '' ELSE '|#|' + N2.CRSOCIETYCODE END
FROM CRALTNUMBERS N2
WHERE N1.CREATIONID = N2.CREATIONID FOR XML PATH('')), 1, 3, '') AS CRNumbers
INTO [ReferenceData_Dax].dbo.TMP_CRALTNUMBERS
FROM CRALTNUMBERS N1 WHERE N1.[CREATIONID] IN (SELECT ItemID FROM dbo.InventTable)
GROUP BY N1.[CREATIONID]
ALTER TABLE [ReferenceData_Dax].TMP_CRALTNUMBERS ADD PRIMARY KEY CLUSTERED (CREATIONID)




The package has been deployed to our SSISDB Catalog. However it has now come to light that for testing the [ReferenceData_Dax] database will be found on different servers. There is a different server for UAT, ACCEPTANCE and Production. The other tables for will come from ServerA regardless of whether we are in UAT, ACCEPTANCE and Production.

I would like to make this package flexible such that dynamically change the connection strings without having to hard code server values into the query. We have different SSIS Catalog environments for UAT, ACCEPETANCE and I was thinking maybe to use environment variables but I wasn't sure. Any ideas on how I can do this
Daniel Bowlin
Daniel Bowlin
SSCertifiable
SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)

Group: General Forum Members
Points: 7774 Visits: 2629
The best way to do this is with package configurations for your connections. Each server can have its own configuration table/file indicating the appropriate connection string for each connection. Google SSIS Package configurations and do some reading. It should make sense without too much time.

Package configurations are also very useful for changing variables in different environments such as date ranges or distribution lists, etc.. You simply maintain the configurations and you don't have to change the package itself.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search