How to refresh Access Table from SSIS package?

Hi all,

I have created an SSIS package to export rows of data from SQL to Access using SSIS package. The package is executed from asp.net web application. Below is what i want to achieve:

-User enters a date range

-SSIS package will export data between the date range from SQL to Access database.

-When user enter another date range, I want to clear the contents of the Access database. (Im using Execute Sql Task-- Delete tablename)

The problem is that when I look at the table after the second user request, the fields will show #deleted. Only after i click refresh will the new data appear. How can I make the data appear without manually refreshing the Access table.

Thks alot.

Can't you just drop off the access table and re-create it upon every user request ?

|||

Hi,

dropping the table means that I will need to create a the table again at the OLE DB destination. However I am unable to do that. I tried to use SQL command data access mode to create table. However, I received an error "CREATE TABLE SQL is not supported".

|||

Hi,

if it is not possible to refresh the Access table, can any expert here advise me on how to create a table when executing the package? The only way to go now is to drop the table and create a new table to solve my problem, but i am facing this problem of creating table.

Thks

How to read a single row in a strongly types dataset?

I'm using strongly typed datasets in my first ASP.NET 2.0 web application. I come from ASP Classic, not an earlier version of .NET, and feel like I'm in another world. I'm slowly getting my head around datasets, but one thing I can't find any information on is how to read the data in a single record?

I'm not talking about for next loops, or accessing row information as a repeater or other control is filled. I'm talking about reading the data returned by a method that returns a table containing a single record. This is what I have:

Dim BookAdapter As New BooksTableAdapters.BooksBBTableAdapter
Dim organizations As Books.BooksBBDataTable
Dim organization As Books.BooksBBRow

organizations = BookAdapter.GetDataByOneBook(sBookID)

Dim sDropOff As String
Dim sOrganization As String
Dim sContact
For Each organization In organizations
If organization.DropOff = 1 Then
sDropOff = "True"
Else
sDropOff = "False"
End If
sOrganization = organization.Organization
sContact = organization.ContactName
Next

sBody = "Books Listing" & Chr(10) & Chr(10)
sBody = sBody & "Organization: " & sOrganization & Chr(10)
sBody = sBody & "Contact Name: " & sContact & Chr(10)
sBody = sBody & "Email: " & organization.Email & Chr(10)
sBody = sBody & "Phone Number: " & organization.Phone & Chr(10)

I'm using the FOR NEXT, but this is silly since I only have one record. GetDataByOneBook(sBookID) does exactly what is says, it returns a single book with a specific bookID.

Not only is this silly, it doesn't work. Using sContact as an example, it's Dim'd as a string. In the FOR NEXT loop, organization.ContactName has the right value, and it appears to be assigned to sContact correctly, but when I try to use sContact in sBody, I get an error saying that sContact has been used before it is assigned a value. Maybe the variables lose their scope outside the loop? Maybe I could get around this by building sBody inside the loop, but there has to be a better way!

Diane

I believe you want to do something like the following with your code:

1Dim BookAdapterAs New BooksTableAdapters.BooksBBTableAdapter2Dim organizationsAs Books.BooksBBDataTable3Dim organizationAs Books.BooksBBRow45 organizations = BookAdapter.GetDataByOneBook(sBookID)67If (organizations.Count > 0)Then89 sBody ="Books Listing" & Chr(10) & Chr(10)10 sBody = sBody &"Organization: " & organizations[0].Organization & Chr(10)11 sBody = sBody &"Contact Name: " & organizations[0].ContactName & Chr(10)12 sBody = sBody &"Email: " & organizations[0].Email & Chr(10)13 sBody = sBody &"Phone Number: " & organizations[0].Phone & Chr(10)1415End If

I do not have your XSD, so I can not test this.

|||

Thank you!

Diane

How to read a set of rows into session variables? C#

Hello ASP.NET C# and SQL gurus

I want to read the results of a set of rows into session variables -- how is it possible?

Let me try explain. I have a query which returns multiple rows, e.g. the following query

SELECT PROFILE_ID, PROFILE_NAME FROM USER_PROFILES returns 5 rows i.e 5 sets of profile_ids and profile_names.

Now, I want to capture these and store them in session variables thus.

Session["PROFILEID_1"] =

Session["PROFILEID_2"] =

Session["PROFILEID_3"] =

Session["PROFILEID_4"] =

Session["PROFILEID_5"] =

Session["PROFILENAME_1"] =

Session["PROFILENAME_2"] =

Session["PROFILENAME_3"] =

Session["PROFILENAME_4"] =

Session["PROFILENAME_5"] =

Thanks in advance!

Fouwaaz

Hi,

Why do you store them in seperate rows. You can store it as datatable and retrieve it when ever you need.

for ex

your select query returns a DataTable dt

then assign it session Session["myTable"] = dt

then when ever you need cast that object to datatable like Session["myTable] as DataTable

Hope it helps

|||

just do a loop of the returned rows.. for example

using (IDataReader rdr = [your returned data read])
{
Session[rdr.GetOrdinal("Profile_ID")] = blah;
Session[rdr.GetOrdinal("Profile_Name")] = blah;

}

Personally, I won't suggest you put so many things in your session object. I'd simply store the entire returned Rows in whatever format when I need to use it.

How to query for master-detail output on same page

I'd like to create a master-detail output from SQL2005 to display in a page using classic ASP. Ideally, I want the output to contain a <div> to show/hide the order details beneathe the order header, as shown below.

OrderNo OrderDate

1001 7/27/2007

+ <div to show/hide>

Item Description

AAA desc_for_itemAAA

BBB desc_for_itemBBB

+ </div>

1002 7/26/2007

+ <div to show/hide>

Item Description

CCC desc_for_itemCCC

+ </div>

I currently just have a stored procedure returning the results and it is very slow as I'm simply querying the details section for each order header. Does anyone have any ideas on how I can create an efficient and fast method for returning these results in the format above?

It’s always better to do these rendering on the ASP/UI page,

For your courtesy,

Code Snippet

Create Table #order (

[OrderNo] int ,

[OrderDate] datetime

);

Insert Into #order Values('1001','7/27/2007');

Insert Into #order Values('1002','7/26/2007');

Create Table #orderdetails (

[OrderNo] int ,

[Item] Varchar(100) ,

[Description] Varchar(100)

);

Insert Into #orderdetails Values('1001','AAA','desc_for_itemAAA');

Insert Into #orderdetails Values('1001','BBB','desc_for_itemBBB');

Insert Into #orderdetails Values('1002','CCC','desc_for_itemCCC');

Select

OrderNo,

OrderDate,

Replace(Replace((Select '<td>' + Item + '</td><td>' + Description + '</td>' as [text()] From #orderdetails OD Where OD.[OrderNo] = O.[OrderNo] For XML Path('tr')),'&gt;','>'),'&lt;','<')

From

#order O

<!--

On ASP/UI page,

While NOT rs.eof

Response.Write(“<tr><td>”)

Response.Write(rs(0))

Response.Write(“</td><td>”)

Response.Write(rs(1))

Response.Write(“</td></tr><tr>”)

Response.Write(“<td colspan=2><span style='cursor:hand' click=ExpandOrCollopse(‘div_” + rs(0) + “’)>+</span>”)

Response.Write (“<div style='display:none' id=’div_”)

Response.Write(rs(0))

Response.Write(“’><table>”)

Response.Write(rs(2))

Response.Write(“</table></div>”)

Wend

-->

|||

What you are proposing is clearly a presentation side issue and should not be 'forced' on SQL Server.

I suggest that you may find utility in the new XML output functionality.

|||

Manivannan, thanks for the sample code. I was able to create an output as you explained.

Arnie, can you point me to an easy to understand resource for this xml output functionality? I haven't really had any exposure to xml.

|||

Here are a couple of resources to get you started:

http://www.topxml.com/sql/

http://technet.microsoft.com/en-us/library/ms175024.aspx

Using XML data is perfect for web applications.