August 22, 2008 at 10:57 am
I have a DTS package that I have the ability to change the password for the connection to an Oracle db dynamically. I cannot seem to find how to do this in SSIS. I have stored procedure to pull the current id and password from a table. I have tried creating the entire connection string and passing that that via expressions to the connection, but have not been successful. I do not seem to be able to get my variable to update. The proc and result are listed below. I have a package variable strConnectionString defined as string. In the SQL task I have the variable defined as output, VARCHAR 255 and a name of 0. If someone can give me the details to pass this to the connection and value the variable, I would be most appreciative. Thanks
String to execute proc in SSIS SQL task
EXEC dbo.SP_SetLocationLoadConnection ? OUTPUT
Here is my proc
USE [aid1026]
GO
/****** Object: StoredProcedure [dbo].[SP_SetLocationLoadConnection] Script Date: 08/22/2008 09:45:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
ALTER PROCEDURE [dbo].[SP_SetLocationLoadConnection]
@ConnectionString AS VARCHAR(255) OUTPUT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SET @ConnectionString = (SELECT 'Data Source=HR001P.WORLD;User ID=' +
USERID + ';Password=' + PASSWRD +
';Provider=MSDAORA.1;Persist Security Info=True;'
FROM dbo.T_EDI_PC
WHERE (APP = 'Location Load'))
PRINT RTRIM(@ConnectionString)
END
Oracle connection string from proc
Data Source=HR001P.WORLD;User ID=REELC05;Password=CR41185P;Provider=MSDAORA.1;Persist Security Info=True;
August 22, 2008 at 12:05 pm
I finally discovered a solution to this after much trial and error and an article here that gave me some ideas. Changes I made were
1. Changed the connection method to ADO.NET and set the IsQueryStoredProcedure value to TRUE
2. Corrected parameter mapping. Set variable name to @name with the 'name' being the same name as my output parameter in the stored procedure.
3. Set the variable strConnectionString to a proper connection string format.
4. Used an expression to set the connection string in the connection manager to the variable that contained the new connection string.
Now here is the kicker
4. Set the connection task to delay validation. Before when I would run the package I would get a logon/id connection failure since it was trying to validate with a non-valid password. Now it does not try to validate the task prior to execution and runs fine once the variable is passed.
The big issue I was having was thinking the variable was not being updated. I had to add a script task to display the variable to see that it was updated. I could not find a way to ad a watch to the variable and the Package Explorer would only show the default fvalues in the variables. Is there another way to look at the variables and see what they are at the current time?
Scott
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply