Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Different Connection Strings in SQL Task Expand / Collapse
Author
Message
Posted Wednesday, June 05, 2013 8:39 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, April 11, 2014 9:27 AM
Points: 285, Visits: 1,043
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
Post #1460258
Posted Wednesday, June 05, 2013 11:34 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, April 14, 2014 2:16 PM
Points: 2,812, Visits: 2,543
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.
Post #1460349
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse