Anurag581784


Hi,

I want to make a SSIS package with Oracle and deploy it in no of oracle databases, for it every time I have to open package and change connection information.

How can I make oracle connection information as variable value so that when I deploy my package on Oracle database it will pick all oracle connection information(User Id, Pwd, Server Name) automatically.

Please let me know about this.

Thanks




Re: Oracle connection information in SSIS package.

Subhash


Hi Anurag,

Experts / MVPs have already addressed this issues about dynamically assigning the Connection details. Follow the steps below

1) Create a table in one of your Oracle database with following fields:

ConnectionDetails(UserID, PWD, ServerName)

2) Insert details of all servers you want to deploy

3) Open the New SSIS Package, Drag and drop an execute sql task, write the query (Select * from ConnectionDetails) to retrieve Conectiondetails into a ResultSet Variable say User::ResultSet of an Object type.

4) Drag and drop the For Each Loop container after the Execute SQL Task and configure details to retrieve each row

5) Store output column values in Package Variables say "v_UserID", "v_passwd" , "v_serverName"

6) Drag and Drop a DataFlow Task into the ForEach Loop Container, determine the source, destination and transformation mappings required.

7) Now assign these variables to connection manager whose detail should change dynamically via expression builder Say

ServerName = @[User::v_serverName]

UserName=@[User::v_UserID]

Thanks

Subhash Subramanyam







Re: Oracle connection information in SSIS package.

Anurag

Hi Subash,

Slew of thanks for speedy reply.

I need a little bit change in first 3 steps, instead of making a table in oracle database, i want to keep oracle server and service name information in Text file and reading from this file.

Thanks






Re: Oracle connection information in SSIS package.

Subhash

If you want to read from a file, Instead of first two steps you can place a script task that can read the data from the text file to populate a resultset variable which you can use in for each loop.