June 5, 2013 at 8:39 am
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
June 5, 2013 at 11:34 am
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.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply