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:IF EXISTS (SELECT * FROM [ReferenceData_Dax].Sys.objects WHERE object_id = OBJECT_ID(N'[TMP_CRALTNUMBERS]') AND type in (N'U'))DROP TABLE [ReferenceData_Dax].TMP_CRALTNUMBERSGOSELECT 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
Different Connection Strings in one SQL Task
Posted - 2013-06-05 : 11:50:58
------------------------------------------------------------------------------------------------------SQL Server MVP