Connection

iam using an execute sql task

ineed the connection to be daynamic ,changed at run time ..

how can i do that

Depending on your scenario you may have more than one option. Serach in package configurations or "http://msdn2.microsoft.com/en-us/library/ms141682.aspx">http://msdn2.microsoft.com/en-us/library/ms141682.aspx

"http://msdn2.microsoft.com/en-us/library/ms141232.aspx">http://msdn2.microsoft.com/en-us/library/ms141232.aspx

|||

Batool wrote:

iam using an execute sql task

ineed the connection to be daynamic ,changed at run time ..

how can i do that

Use an "http://blogs.conchango.com/jamiethomson/archive/2006/10/05/SSIS-Nugget_3A00_-Dynamically-set-a-logfile-name.aspx">http://blogs.conchango.com/jamiethomson/archive/2006/10/05/SSIS-Nugget_3A00_-Dynamically-set-a-logfile-name.aspx

talks about setting an expressoin on the ConnectionString property of a file conenction manager rather than an OLE DB Conenction Manager, but the concept is the same.

-Jamie

|||

Hi,

The easiest way to set dynamic connection at run time is by declaring two SSIS variables

1. DBName
2. DBServer

In connection properties open the "InitialCatalog" = "@.[User::DBName]" and "ServerName" = "@.[User::DBServer]".While running the package if you just set the value of variables dynamically the connection to the DB will be created accordingly. Using this connection in SQL task will server your purpose. This works with both OLEDB and ADO.NET connections.

Thanks

Mohit

|||thx for ur replay and i think its my solution but,can we read theses variable from the registry|||

Hi,

Using follwoing in Script tasks will retrieve value from registry

Imports Microsoft.Win32

VarValue = Registry.LocalMachine.GetValue(<Registry Key>)

After getting the value you can set this in SSIS variables using Dts.Variables("DBServer").Value = VarValue will set the value from registry

Thanks

Mohit

|||Sorry that seems a bit mad. Why write code when there is a built in mechanism, see Configurations. You can use the registry as a source for a configuration. It also happens before the package starts executing, so things start working from the very begining. Often changing a connection part way can be an issue, as tasks have started to validate or do other preparation.|||

I PUT THE NEEDED VALUES IN THE REGISTRY

(DB NAME AND SERVER NAME )

BUT I DID NOT KNOW HOW TO RETRIVE THESE VALUES AND MADE THE CONNECTION STRING

|||

Independently where you want to store your configuration values; in the case of connection managers you can better store the whole connection string, instead of Server name and DB name; and then use that value against the connection string property.

|||

There is a section in Books Online that describes the registry format. Seems that you must use one value per key, and that value must be called Value. It also seems to be limited to values within HKEY_CURRENT_USER. In retrospect the Script task method may be better after all with these rather restrictive limitations.

Package Configurations
(http://msdn2.microsoft.com/en-us/library/d20e0311-1fc9-4ddc-a381-6d127cf11b69.aspx)

Added Connect Feedback on these restrictions - https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=250871