dynamic port assignment

We have a customer who uses the dynamic port assignment option when a connec
tion is established, instead of the default 1433. However, our application
requires a fixed port number, either the default 1433 or an explicitly set p
ort number.
For now we are accessing this small database via the ODBC connector using th
e Microsoft-supplied ODBC for SQLServer driver. However, this is not a solu
tion for larger databases because of the additional overhead involved.
Any assistance will be very appreciated.
Alex IvascuYou can have SQL Server listen on multiple ports. So if the application
chooses between a list of ports you could simply have SQL listen on all of
them. Of course that is not the optimum solution for security reasons and
your firewall folks will probably not like the idea at all...
To configure SQL to listen on multiple ports; open up the Server Network
Utility, open up the TCP/IP properties and in the ports textbox enter in
your ports separated by commas.
"alexdivascu@.hotmail.com" <anonymous@.discussions.microsoft.com> wrote in
message news:8847E4B5-8BC9-4725-86F8-976AF93F945E@.microsoft.com...
> We have a customer who uses the dynamic port assignment option when a
connection is established, instead of the default 1433. However, our
application requires a fixed port number, either the default 1433 or an
explicitly set port number.
> For now we are accessing this small database via the ODBC connector using
the Microsoft-supplied ODBC for SQLServer driver. However, this is not a
solution for larger databases because of the additional overhead involved.
> Any assistance will be very appreciated.
> --
> Alex Ivascu
>|||So... how do people handle connecting to a particular instance, since the po
rt numbers are dynamic? Do they put a "list" of ports to try?
Thanks, Don.
Alex Ivascu|||Now I think I know what you are talking about. In the Client Network
Utility in the Alias setup, if the "Dynamically Determine Port" box is
checked there is no additional setup needed on your servers.
That setting just means that the client will query the instance to determine
which port should be used and then use it.
HTH
"Alex Ivascu" <anonymous@.discussions.microsoft.com> wrote in message
news:D3956B77-CC1F-47D6-95A5-0F7595BA76D2@.microsoft.com...
> So... how do people handle connecting to a particular instance, since the
port numbers are dynamic? Do they put a "list" of ports to try?
> Thanks, Don.
> Alex Ivascu
>sql

dynamic port assignment

We have a customer who uses the dynamic port assignment option when a connection is established, instead of the default 1433. However, our application requires a fixed port number, either the default 1433 or an explicitly set port number.
For now we are accessing this small database via the ODBC connector using the Microsoft-supplied ODBC for SQLServer driver. However, this is not a solution for larger databases because of the additional overhead involved.
Any assistance will be very appreciated.
Alex Ivascu
You can have SQL Server listen on multiple ports. So if the application
chooses between a list of ports you could simply have SQL listen on all of
them. Of course that is not the optimum solution for security reasons and
your firewall folks will probably not like the idea at all...
To configure SQL to listen on multiple ports; open up the Server Network
Utility, open up the TCP/IP properties and in the ports textbox enter in
your ports separated by commas.
"alexdivascu@.hotmail.com" <anonymous@.discussions.microsoft.com> wrote in
message news:8847E4B5-8BC9-4725-86F8-976AF93F945E@.microsoft.com...
> We have a customer who uses the dynamic port assignment option when a
connection is established, instead of the default 1433. However, our
application requires a fixed port number, either the default 1433 or an
explicitly set port number.
> For now we are accessing this small database via the ODBC connector using
the Microsoft-supplied ODBC for SQLServer driver. However, this is not a
solution for larger databases because of the additional overhead involved.
> Any assistance will be very appreciated.
> --
> Alex Ivascu
>
|||So... how do people handle connecting to a particular instance, since the port numbers are dynamic? Do they put a "list" of ports to try?
Thanks, Don.
Alex Ivascu
|||Now I think I know what you are talking about. In the Client Network
Utility in the Alias setup, if the "Dynamically Determine Port" box is
checked there is no additional setup needed on your servers.
That setting just means that the client will query the instance to determine
which port should be used and then use it.
HTH
"Alex Ivascu" <anonymous@.discussions.microsoft.com> wrote in message
news:D3956B77-CC1F-47D6-95A5-0F7595BA76D2@.microsoft.com...
> So... how do people handle connecting to a particular instance, since the
port numbers are dynamic? Do they put a "list" of ports to try?
> Thanks, Don.
> Alex Ivascu
>

dynamic port assignment

We have a customer who uses the dynamic port assignment option when a connection is established, instead of the default 1433. However, our application requires a fixed port number, either the default 1433 or an explicitly set port number
For now we are accessing this small database via the ODBC connector using the Microsoft-supplied ODBC for SQLServer driver. However, this is not a solution for larger databases because of the additional overhead involved
Any assistance will be very appreciated
-
Alex IvascYou can have SQL Server listen on multiple ports. So if the application
chooses between a list of ports you could simply have SQL listen on all of
them. Of course that is not the optimum solution for security reasons and
your firewall folks will probably not like the idea at all...
To configure SQL to listen on multiple ports; open up the Server Network
Utility, open up the TCP/IP properties and in the ports textbox enter in
your ports separated by commas.
"alexdivascu@.hotmail.com" <anonymous@.discussions.microsoft.com> wrote in
message news:8847E4B5-8BC9-4725-86F8-976AF93F945E@.microsoft.com...
> We have a customer who uses the dynamic port assignment option when a
connection is established, instead of the default 1433. However, our
application requires a fixed port number, either the default 1433 or an
explicitly set port number.
> For now we are accessing this small database via the ODBC connector using
the Microsoft-supplied ODBC for SQLServer driver. However, this is not a
solution for larger databases because of the additional overhead involved.
> Any assistance will be very appreciated.
> --
> Alex Ivascu
>|||So... how do people handle connecting to a particular instance, since the port numbers are dynamic? Do they put a "list" of ports to try
Thanks, Don.
Alex Ivasc|||Now I think I know what you are talking about. In the Client Network
Utility in the Alias setup, if the "Dynamically Determine Port" box is
checked there is no additional setup needed on your servers.
That setting just means that the client will query the instance to determine
which port should be used and then use it.
HTH
"Alex Ivascu" <anonymous@.discussions.microsoft.com> wrote in message
news:D3956B77-CC1F-47D6-95A5-0F7595BA76D2@.microsoft.com...
> So... how do people handle connecting to a particular instance, since the
port numbers are dynamic? Do they put a "list" of ports to try?
> Thanks, Don.
> Alex Ivascu
>

Dynamic Database Source Changing

Hi,

I am building a data warehouse for a customer who has systems located in two different countries.

I need to import that data from four seperate databases, which all share the same structure.

To do this i have created 20 packages to import that data from the source database. What i would like to do, is at run time set which database the SSIS package should get its data from.

In sql 2k this was easy with a global variable that was set, then use a dynamic properties task to set the data source.

How can i achieve the same result in SSIS? the data source is an ODBC connection, with the four ODBC connections having similar names, eg ABC_NZ, ABC_AU

Thanks in Advance!

Truby

Use a ForEach Loop Container to loop over your collection of ODBC connection names.

Upon each iteration, set the connection string of the connection manager. This technique is described here: http://blogs.conchango.com/jamiethomson/archive/2005/05/30/1489.aspx although in this exampe it talks about using a flat file conneciton manager which is not what you want. The principle is the same though.

-Jamie

|||

Hi Jamie,

Thanks for that, it would be perfect if i could run the database extracts from each system at the same time, but i need to be able to schedule the extracts at different times due to different time zones.

what i really want to be able to do is specify the connection at run time.

eg set variable "datasource" to be "ABC_NZ", and that will point at the ABC_NZ ODBC.

Truby

|||

AHA. You can supply this information on the command-line. use the /SET option of dtexec.exe

And use dtexecui.exe to build the command-line for you.

-Jamie

|||

Hi,

I had a similar problem like you. I recognized that it is helpful to use one or more global variables which hold the infomation about the data source. The variable(s) could be set during runtime (i.e. from a db table) and finally you can dynamically change a connection in the connection manager when you click on the connection, properties, expressions. Under expressions you might use your variables to set up a new connection string dynamically.

Example for OLE DB:

"Data Source="+@.[User::Address]+";User ID="+@.[User::UserID]+";Initial Catalog="+@.[User::CatalogName]+";Provider=SQLOLEDB.1;Password="+@.[User::PWD]+";"

If you are not sure about the structure of your connection strings then have a look under:

http://www.connectionstrings.com/

Hope that helps.

Regards,

Stefan

|||

Hi,

You can create a package configuration file and specify Connections being set dynamically from SQL AGent or a schedule job.

Follow

In the Integration serivice screen select package configuration Create a file and select the ODBC connection items as configurible. Remember to copy the .dtsconfig file in the place where ur deloying the package.

Once you have done this.

Create a Schedule job under the steps u select the pakage. After setting the package you can go to the connection tab and then change the datasource and the connection strings to what ever you want and leave it.

Like that you can create multiple scheuler for the same dts package and make it run in different time zones according to your requirment.

Hope this helps a bit

Mani

Dynamic age of customer based on transactions being viewed

Hi

I am after some ideas on how to deal with customer ages and dynamically linking them to transactions. For example if I purchased something 2 years ago I would be obviously 2 years younger then now but if I have age as a standard dimension all of my purchases would be tied to my current age, assuming age is updated and overwritten. The ideas I have are a slow changing dimension which means that I would have a new entry per customer's birthdate or somehow tie the age calculation to the transaction date. The reason I want this is that the purchasing decisions of say a 20 year old are different than a 25 year old for us. Anyone dealt with this type of issue before.

Thanks in advance.

Derek

One approach, which would avoid a new entry per birthday, is to compute the age by joining the transaction fact table to the customer table in a SQL view (or named query in AS 2005). There could be an "Age" dimension table, starting at the day level, which is joined to the computed age in days (eg: DateDiff("D", TransDate, BirthDate)).

Obviously, if the BirthDate of the customer changes (assuming that's even permitted), then there will have to be a new entry in the Customer dimension table.

|||thanks for the idea Deepak, I'll have a look into that option. I suppose I'm basically adding the slow changing dimension to the fact table.|||It'll be interesting to see which solution works best in your scenario, because there must be others working on similar problems.