methods of storing the Connection String in package configuration

hi!

I want to store the value of the Connection String in an Common table exisiting for the whole project and retrieve it for the whole package to Run. I want to know if there is a way of doing this.

I do not want to have a config file which is created trhough the package configuration.

In case of Parent package variable the Value of the variable needs to exist which again needs to execute the SQL Task which requires a connection string.

In case of Environment variabIe.I have very less knowledge about the environment variable . Even if we store it? how can we access it and change it in client place.

In case of SQL server . Can we store in an existing table. what is the configuration filter? what is the vlaue for it.

I do not want to execute any command line.............................

Thanks,

jazz

Well, I have not done this but I think you could simply have a parent package variable that uses a SQL Task to load the connection string for the child packages (obviously you need an initial connection string to load the config in the first place so I am assuming you are looking for a connection string to a second database.).

Once you do that you can use a script task in each of the child packages to explicitly set the Dts.Connections("ConnectionName").ConnectionString property manually based on the parent variable. You can use parent package configurations to map a parent variable to the child package so the designer can see the variable used.

Hope this helps...

|||

Can i execute a stored preocedure to get the connection string from the database while installing the pacakge which can set the value of the parent package variables.

Where i can use the Parent Package Varaible in Package Configuration to be accessed by the Child Packages.

is this ok......

Thanks

Message --"Configuration option 'show advanced options' changed from 0 to 1. Run the RECO

i am working on storedprocedures and after the Succesful parsing of the procedure .. on executing its giving me 3 msgs ....

Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.

Configuration option 'xp_cmdshell' changed from 1 to 1. Run the RECONFIGURE statement to install.

Configuration option 'show advanced options' changed from 1 to 0. Run the RECONFIGURE statement to install.

and not displaying me the output.......

The Procedure i am executing is as follows

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

CREATE PROCEDURE [dbo].[uspRcopiaExport12] AS

DROP TABLE Skagway..RCOPIA_EXPORT

-- Get Patients' Home Addresses

/*

SELECT pa.PersonID, pa.Street1, pa.Street2, pa.City, pa.State, Skagway.dbo.StripNonDigits(pa.ZipCode) AS Zip

INTO #ADDRESS

FROM Person..PHYSICAL_ADDRESS pa

INNER JOIN Person..PHYSICAL_ADDRESS_DESC pad ON (pa.PhysicalAddrDescID = pad.PhysicalAddrDescID)

WHERE pad.PhysicalAddrDesc = 'Home'

-- Get Patients' Home Phone Numbers

SELECT tn.PersonID, tn.TelecomNumber

INTO #HOME_PHONE

FROM Person..TELECOM_NUMBER tn

INNER JOIN Person..TELECOM_NUMBER_DESC tnd ON (tn.TelecomNumDescID = tnd.TelecomNumDescID)

INNER JOIN Person..TELECOM_DEVICE_TYPE tdt ON (tn.TelecomDevTypeID = tdt.TelecomDevTypeID)

WHERE tnd.TelecomNumDesc = 'Home'

ORDER BY tdt.TelecomDevTypeID

-- Get Patients' Work Phone Numbers

SELECT tn.PersonID, tn.TelecomNumber

INTO #WORK_PHONE

FROM Person..TELECOM_NUMBER tn

INNER JOIN Person..TELECOM_NUMBER_DESC tnd ON (tn.TelecomNumDescID = tnd.TelecomNumDescID)

INNER JOIN Person..TELECOM_DEVICE_TYPE tdt ON (tn.TelecomDevTypeID = tdt.TelecomDevTypeID)

WHERE tnd.TelecomNumDesc = 'Work'

ORDER BY tdt.TelecomDevTypeID

*/

-- Get SCP patient demographic data

SELECT 'update_patient_4' AS DataType,

p.FirstName AS FIRST_NAME,

p.MiddleInit AS MIDDLE_INITIAL,

p.LastName AS LAST_NAME,

CASE p.Gender

WHEN 'm' THEN 'M'

WHEN 'f' THEN 'F'

WHEN 'M' THEN 'M'

WHEN 'F' THEN 'F'

ELSE 'U'

END AS SEX,

-- (SELECT TOP 1 Street1 FROM #ADDRESS WHERE PersonID = p.PersonID) AS ADDRESS_LINE_1,

-- (SELECT TOP 1 Street2 FROM #ADDRESS WHERE PersonID = p.PersonID) AS ADDRESS_LINE_2,

-- (SELECT TOP 1 City FROM #ADDRESS WHERE PersonID = p.PersonID) AS CITY,

-- (SELECT TOP 1 State FROM #ADDRESS WHERE PersonID = p.PersonID) AS STATE_CODE,

-- (SELECT TOP 1 Zip FROM #ADDRESS WHERE PersonID = p.PersonID) AS ZIP,

-- (SELECT TOP 1 TelecomNumber FROM #HOME_PHONE WHERE PersonID = p.PersonID) AS PHONE,

-- (SELECT TOP 1 TelecomNumber FROM #WORK_PHONE WHERE PersonID = p.PersonID) AS WORK_PHONE,

' 'AS ADDRESS_LINE_1,

' 'AS ADDRESS_LINE_2,

' 'AS CITY,

' 'AS STATE_CODE,

' 'AS ZIP,

' 'AS PHONE,

' 'AS WORK_PHONE,

' ' AS OTHER_PHONE,

-- ea.EmailAddress AS EMAIL,

' ' AS EMAIL,

p.BirthDate AS BIRTHDATE,

p.SocSecNum AS SOCIAL_SECURITY,

' ' AS INSURANCE,

' ' AS LAST_VISIT_DATE,

'PHMS-SCP-' + CAST(p.PersonID AS VARCHAR(8)) AS EXTERNAL_ID,

' ' AS PROVIDER_FIRST_NAME,

' ' AS PROVIDER_MIDDLE_NAME,

' ' AS PROVIDER_LAST_NAME

INTO Skagway..RCOPIA_EXPORT

FROM Skagway..PERSON p

INNER JOIN Skagway..EMAIL_ADDRESS ea ON (p.PersonID=ea.PersonID)

INNER JOIN Skagway..CARE_TEAM_ASSIGNMENT cta ON (p.PersonID=cta.PersonID)

INNER JOIN Skagway..SHARED_CARE_PLAN scp ON (cta.CarePlanID=scp.CarePlanID)

WHERE scp.SCPInactiveFlag <> 1

AND cta.CareTeamRoleID = 23

AND p.BirthDate IS NOT NULL

AND p.SocSecNum IS NOT NULL

AND NOT p.SocSecNum IN ('999999999', '')

-- Replace all NULLs with single spaces (required by Rcopia for import)

UPDATE Skagway..RCOPIA_EXPORT

SET MIDDLE_INITIAL = ' '

WHERE MIDDLE_INITIAL IS NULL OR MIDDLE_INITIAL = ''

--UPDATE Skagway..RCOPIA_EXPORT

-- SET PHONE = ' '

-- WHERE PHONE IS NULL OR PHONE = ''

--UPDATE Skagway..RCOPIA_EXPORT

-- SET WORK_PHONE = ' '

-- WHERE WORK_PHONE IS NULL OR WORK_PHONE = ''

--UPDATE Skagway..RCOPIA_EXPORT

-- SET ADDRESS_LINE_1 = ' '

-- WHERE ADDRESS_LINE_1 IS NULL OR ADDRESS_LINE_1 = ''

--UPDATE Skagway..RCOPIA_EXPORT

-- SET ADDRESS_LINE_2 = ' '

-- WHERE ADDRESS_LINE_2 IS NULL OR ADDRESS_LINE_2 = ''

--UPDATE Skagway..RCOPIA_EXPORT

-- SET CITY = ' '

-- WHERE CITY IS NULL OR CITY = ''

--UPDATE Skagway..RCOPIA_EXPORT

-- SET STATE_CODE = ' '

-- WHERE STATE_CODE IS NULL OR STATE_CODE = ''

--UPDATE Skagway..RCOPIA_EXPORT

-- SET ZIP = ' '

-- WHERE ZIP IS NULL OR ZIP = ''

--UPDATE Skagway..RCOPIA_EXPORT

-- SET EMAIL = ' '

-- WHERE EMAIL IS NULL OR EMAIL = ''

--UPDATE Skagway..RCOPIA_EXPORT

-- SET SOCIAL_SECURITY = ' '

-- WHERE SOCIAL_SECURITY IS NULL OR SOCIAL_SECURITY = ''

-- Create the pipe-delimited ascii file on the HiNet server

DECLARE @.cmd varchar(1000)

--DECLARE @.pwd varchar(30)

--SELECT @.pwd = (SELECT Password FROM master..Passwords WHERE UserName = 'DBO_HelpDesk')

DECLARE @.FileName varchar(100)

--SET @.FileName = '"\\phins\d$\scppatients.txt"' -- Can't get to HiNet at the moment, create it on PHINS

-- SET @.FileName = '"\\x.xx.x.x\phms\phmspatients.txt"'

SET @.FileName = '"C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\1.txt"'

--SELECT @.cmd = 'master..xp_cmdshell ' + char(39) + 'bcp Skagway..RCOPIA_EXPORT out ' + @.FileName + ' -c -t"|" -UDBO_HelpDesk -P' + @.pwd + ' -S' + @.@.SERVERNAME + char(39)

SELECT @.cmd = 'master..xp_cmdshell ' + char(39) + 'bcp Skagway..RCOPIA_EXPORT out ' + @.FileName + ' -c -t"|" -T -S' + @.@.SERVERNAME + char(39)

EXEC (@.cmd)

--DROP TABLE #ADDRESS

--DROP TABLE #HOME_PHONE

--DROP TABLE #WORK_PHONE

-

The Query i am executing after this is

USE master

GO

EXEC sp_configure 'show advanced options', 1

GO

RECONFIGURE WITH OVERRIDE

GO

EXEC sp_configure 'xp_cmdshell', 1

GO

RECONFIGURE WITH OVERRIDE

GO

EXEC sp_configure 'show advanced options', 0

GO

and the output i m looking for is like this ......

-- <Fst name><mid name><lst name> < DOB><SSN> <ID>

update_patient_4|Dawn|M|Gauthier|F| | | | | | | | | |08/30/1974|370845156| | |SCP-2| | |
update_patient_4|Keith| |Robinson|M| | | | | | | | | |09/28/1948|537523557| | |SCP-9| | |

I moved this thread from SSIS forum; I think the T-SQL one is a better place for this