advice for storing reservation/booking date ranges...

I am building a small app that will display availability data for properties. I have a calendar that displays the dates for a whole year in month rows with each days colour representing the availability status, i.e. booked, on hold etc.

My question is about how to store these dates in the db. At the moment I have the table below:

TABLE Availability
[PropertyID] [int] NOT NULL ,
[StatusID] [tinyint] NOT NULL ,
[StartDate] [smalldatetime] NOT NULL ,
[EndDate] [smalldatetime] NOT NULL

I was planning on having four status's for any given date, unknown, available, on hold or booked.

Displaying the dates has proved pretty simple but updating availability means I would need to query the db to see if any of the dates overlapped, I would then have to add the new date range/status as well as change any date ranges that overlapped, either in the sp or in the code and this is what made me wonder if there was a better way.

Does this sound a reasonable approach? Any advice or pointers would be greatly appreciated, This is the first time I have had to store date ranges and I want to make sure I am doing it right.

Instead of an Availability table, I would think you'd want a Booked table. Assume the property is available and create records in the Booked table when it's booked or held. Don't create records if you'd have an overlap by doing so. If no record for a time frame, then it's available.

It seems the way you're doing it, you would have to create a record for every date for every property. That makes it tough to be more granular if you need to go to the hour or minute level.

|||

Yes, unfortunately I have to cater for users who are not keeping the calendar uptodate hence using both the unknown and available status.

The default will be unknown, so anything not in the table will be that status and then it is up to users to use the calendar to update the properties availability, so there are three status's, available, on-hold and booked. These three will be in the table.

I think I will proceed as is and see how I go...thanks for the input.

ADO.NET Source Custom Properties - Documentation Wrong?

Ok, so I've looked near and far and have found nothing but info that says data flow properties can not be changed at runtime....then I see in this in the SSIS documentation under ADO.NET Source Custom Properties:

SQLCommand

String

The SQL statement that the ADO.NET source uses to extract data.

The value of this property can be specified by using a property expression.

Pray tell me how this property can be specified using a property expression, or any other dts variable for that matter?
Thanks,
Adrian Crawford
Adrian,

You would never find this on your own, and I realize now that we need to document this more clearly in several places in BOL.

Property expressions for components within the Data Flow task are visible and available only on the containing Data Flow task itself at the present time. Select the Data Flow task on the Control Flow tab, or an empty spot on the Data Flow designer surface, go to the Properties window (it doesn't have a Properties dialog box), select Expressions, click the ellipsis, and in there you'll be able to drop down a list, select the SqlCommand property of your DataReader source, and set the expression.

Note that nothing will show up in the DataReader editor to indicate that you've set a value (with an expression) for the SqlCommand property...it will appear blank. I trust that the "usability" of this feature will be enhanced in the future. For now, however, it's better to have awkward access to data flow properties via property expressions, than none at all.

Best regards,

-Doug
|||Doug,
Thank you so much for your post. This is the holy grail I've been looking for, and I'll take it any way I can get it. It had boggled my mind because I had read rumors of this feature but was never able to make it work.
Thanks,
Adrian
|||Doug,

Do you know if Microsoft intends (in future revs) on allowing Web Service Task web-parameters to be updateable dynamically at runtime? Right now this one is killing me. I mean, who calls a web service with the same parameter every time?!?|||

Adrian,

Glad to hear it. I apologize for overlooking your earlier post on the same subject.

I've just finished updating and adding links to multiple BOL topics so that this not entirely intuitive how-to information will be easier to locate.

As I assume you've discovered, the Property list in the Property Expressions Editor only displays available properties for data flow objects that you've already placed on the Data Flow surface of the designer. (As a result, you can't use the Property list to view all the properties of data flow objects that support property expressions.) For example, if you've placed a DataReader Source on the designer surface, the Property list contains an entry for the [DataReader Source].[SqlCommand] property. The list also displays many properties of the Data Flow task itself.

Enjoy your expressions,

-Doug

|||I don't know the answer to that, but I understand why it would be useful. You can make your request known, as always, by using sqlwish@.microsoft.com and BetaPlace http://msdn.microsoft.com/sql/bugs/default.aspx.

Best regards,

-Doug
|||Just sent the request. I will feel so special if I, personally, effect the direction of Microsoft. :)

Just one more step in my eventual goal: Total global domination.|||A mere email to sqlwish may not achieve your entire goal, but Microsoft is more responsive to customer input than the average person gets to see. Especially the documentation team (have I mentioned those Feedback links in BOL?), where improving a BOL topic runs less risk of having unforeseen side-effects than code changes.|||Doug

I have the same problem as Adrian. I need to set the SQL Command of the Data Reader from a Variable. Trying your approach, the Property Drop Down List of the Property Expression Editor does not list an SqlCommand property.

What could be the problem?

Torsten|||Not many of the stock components implement expressions. There is a list in Books Online -

Using Property Expressions to Specify Data Flow Property Values
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/dtsref9/html/cd0e171a-08be-45d6-81dc-ed94f37698b8.htm

Seems like a reasonable request though so why not log it-
http://lab.msdn.microsoft.com/productfeedback/default.aspx|||If the ADO.Net source is in the dataflow, and you open the Expressions dialog for that dataflow, you should indeed find an entry in the properties drop down. It will likely be called "[DataReader Source].[SqlCommand]"

Thanks
Mark|||

I have a similar problem

want to use the property expression on data reader source

it loks like this

select @.var1, @.var2 from @.var3 inner join @.var4

where all the variables are from the foreachloop container > variable mapping

just tell me if this is correct

|||

You should be able to use aproperty expression (set it at the data flow level though, Expressions), but your syntax is not valid. Have a look at the BOL reference for the expression syntax. It should look more like this -

"SELECT " + @.var1 + ", " + @.var2 + " FROM " + @.var3 + " inner join " + @. var4 + " ON some columns!!!"

The evaluated expression result should give you a vali SQL statement that you could copy into any query tool and run, if not make it so that it can.

ADO.NET Source Custom Properties - Documentation Wrong?

Ok, so I've looked near and far and have found nothing but info that says data flow properties can not be changed at runtime....then I see in this in the SSIS documentation under ADO.NET Source Custom Properties:

SQLCommand

String

The SQL statement that the ADO.NET source uses to extract data.

The value of this property can be specified by using a property expression.

Pray tell me how this property can be specified using a property expression, or any other dts variable for that matter?
Thanks,
Adrian Crawford
Adrian,

You would never find this on your own, and I realize now that we need to document this more clearly in several places in BOL.

Property expressions for components within the Data Flow task are visible and available only on the containing Data Flow task itself at the present time. Select the Data Flow task on the Control Flow tab, or an empty spot on the Data Flow designer surface, go to the Properties window (it doesn't have a Properties dialog box), select Expressions, click the ellipsis, and in there you'll be able to drop down a list, select the SqlCommand property of your DataReader source, and set the expression.

Note that nothing will show up in the DataReader editor to indicate that you've set a value (with an expression) for the SqlCommand property...it will appear blank. I trust that the "usability" of this feature will be enhanced in the future. For now, however, it's better to have awkward access to data flow properties via property expressions, than none at all.

Best regards,

-Doug
|||Doug,
Thank you so much for your post. This is the holy grail I've been looking for, and I'll take it any way I can get it. It had boggled my mind because I had read rumors of this feature but was never able to make it work.
Thanks,
Adrian
|||Doug,

Do you know if Microsoft intends (in future revs) on allowing Web Service Task web-parameters to be updateable dynamically at runtime? Right now this one is killing me. I mean, who calls a web service with the same parameter every time?!?|||

Adrian,

Glad to hear it. I apologize for overlooking your earlier post on the same subject.

I've just finished updating and adding links to multiple BOL topics so that this not entirely intuitive how-to information will be easier to locate.

As I assume you've discovered, the Property list in the Property Expressions Editor only displays available properties for data flow objects that you've already placed on the Data Flow surface of the designer. (As a result, you can't use the Property list to view all the properties of data flow objects that support property expressions.) For example, if you've placed a DataReader Source on the designer surface, the Property list contains an entry for the [DataReader Source].[SqlCommand] property. The list also displays many properties of the Data Flow task itself.

Enjoy your expressions,

-Doug

|||I don't know the answer to that, but I understand why it would be useful. You can make your request known, as always, by using sqlwish@.microsoft.com and BetaPlace http://msdn.microsoft.com/sql/bugs/default.aspx.

Best regards,

-Doug
|||Just sent the request. I will feel so special if I, personally, effect the direction of Microsoft. :)

Just one more step in my eventual goal: Total global domination.|||A mere email to sqlwish may not achieve your entire goal, but Microsoft is more responsive to customer input than the average person gets to see. Especially the documentation team (have I mentioned those Feedback links in BOL?), where improving a BOL topic runs less risk of having unforeseen side-effects than code changes.|||Doug

I have the same problem as Adrian. I need to set the SQL Command of the Data Reader from a Variable. Trying your approach, the Property Drop Down List of the Property Expression Editor does not list an SqlCommand property.

What could be the problem?

Torsten|||Not many of the stock components implement expressions. There is a list in Books Online -

Using Property Expressions to Specify Data Flow Property Values
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/dtsref9/html/cd0e171a-08be-45d6-81dc-ed94f37698b8.htm

Seems like a reasonable request though so why not log it-
http://lab.msdn.microsoft.com/productfeedback/default.aspx|||If the ADO.Net source is in the dataflow, and you open the Expressions dialog for that dataflow, you should indeed find an entry in the properties drop down. It will likely be called "[DataReader Source].[SqlCommand]"

Thanks
Mark|||

I have a similar problem

want to use the property expression on data reader source

it loks like this

select @.var1, @.var2 from @.var3 inner join @.var4

where all the variables are from the foreachloop container > variable mapping

just tell me if this is correct

|||

You should be able to use aproperty expression (set it at the data flow level though, Expressions), but your syntax is not valid. Have a look at the BOL reference for the expression syntax. It should look more like this -

"SELECT " + @.var1 + ", " + @.var2 + " FROM " + @.var3 + " inner join " + @. var4 + " ON some columns!!!"

The evaluated expression result should give you a vali SQL statement that you could copy into any query tool and run, if not make it so that it can.

Administrator access Lost

Hello,

I seem to have lost the ability to access the site settings as well as any security tabs and modify the properties under the directories etc...somehow i have lost administrator access , how do i get this back? I am still local administrator of the server reporting services is running on...only thing i can think off that could of done this is i installed Sharepoint onto the same machine and created a sharepoint site...are there things i can check in IIS or RS or etc to get this admin access back

thanks

A couple points come to mind. If you haven't already, run the Report Services Configuration application and try resetting (or creating new) the Report Server Virtual Directory. The configuration tool should remove any conflicts with SharePoint. In addition, in IIS make sure that anonymous access is disabled for the ReportServer virtual directory (on the Direcory Security tab of the ReportServer Properties dialog). It may be that, even though you are logged on as user, it is the IUSR_ account that is authenticating to the report server instead of the administrator.

This is assuming you're not yet attempting to integrate Reporting Services with SharePoint.

Administrator access Lost

Hello,

I seem to have lost the ability to access the site settings as well as any security tabs and modify the properties under the directories etc...somehow i have lost administrator access , how do i get this back? I am still local administrator of the server reporting services is running on...only thing i can think off that could of done this is i installed Sharepoint onto the same machine and created a sharepoint site...are there things i can check in IIS or RS or etc to get this admin access back

thanks

A couple points come to mind. If you haven't already, run the Report Services Configuration application and try resetting (or creating new) the Report Server Virtual Directory. The configuration tool should remove any conflicts with SharePoint. In addition, in IIS make sure that anonymous access is disabled for the ReportServer virtual directory (on the Direcory Security tab of the ReportServer Properties dialog). It may be that, even though you are logged on as user, it is the IUSR_ account that is authenticating to the report server instead of the administrator.

This is assuming you're not yet attempting to integrate Reporting Services with SharePoint.