Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2012 Forums
 SSIS and Import/Export (2012)
 Different Connection Strings in one SQL Task

Author  Topic 

sauce1979
Starting Member

47 Posts

Posted - 2013-06-05 : 11:50:58
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_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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-05 : 14:15:42
if the tables are in two different servers then it would make sense to split this single sql task to a sql task to do the drop table part and then move the creation step to data flow task. following that you can add another sql task to do index creation.

If you want to accomodate everything within same sql task then you may need to create linked server connections to serverA from other servers containing ReferenceData_Dax database which will defeat purpose of using SSIS


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

sauce1979
Starting Member

47 Posts

Posted - 2013-06-06 : 03:13:32
Thanks for that. it worked and didn't affect my ssissdb catalog configuration which is what I wanted.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-06 : 03:16:04
quote:
Originally posted by sauce1979

Thanks for that. it worked and didn't affect my ssissdb catalog configuration which is what I wanted.


You're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -