Dynamic export to Access (DTS best?)

[jpa]

Our SQL server has some tables that we need to pivot (with transact SQL statements) then export to Access. We have some ASP that creats the dynamic SQL to view the results on the web page, so that's not our issue.

Question is: what is the best way to get the resulting data to an Access database via code on the server side? We've hardcoded a DTS package that does it nicely, but the SQL query is currently hardcoded and the nature of the pivot is such that the SQL will change as the data changes. We can't find a Dynamic Property that will allow us to set the SQL from ASP (anyone know what it is?).

Is there a better way to go about this? Users are remote and won't be able to link to the SQL database.

Thanks.

 

[LogicalmanUS]

jpa,

I'm not certain about your question (We can't find a Dynamic Property that will allow us to set the SQL from ASP (anyone know what it is?).), do you mean you wish to update the DTS package with different parameters?

If so, simply place the parameter data into a table on the server and reference the same table from the DTS package and use Global Variables in the DTS Package.

Logicalman

 

[jpa]

In the DTS package we set up, the connection has a parameter for the SQL Query. We'd like to be able to change the SQL Query text on the fly, from ASP, before DTS exports to Access. I made an assumption (possibly incorrect) that this could be changed using a Dynamic Property.

The situation is that we have an SQL string in ASP (greater than 8000 char long) and we'd like to get the resulting record set out to MS Access.

 

[LogicalmanUS]

jpa,

I understand a bit better now.

You say that the SQL string is created in the ASP, and it is greater than 8000 characters (Pity, that's a page length). One question does arise then. What changes in the different SQL statements? Is there any commonality at all? If so, then you may get away with simply passing the changes, rather than to whole statement, and then reconstructing it within the DTS package.

There is another question on this. Why use a View from the DTS package, when you can use a script to execute the same?

I tend to include as much dynamic script as possible in the DTS package, and call SProcs, with parameters, when the need arises.

Logicalman

 

[jpa]

The changes are the large part of the SQL. The ASP page generates the changing part, then passes it to the SQL Server for execution. A sample of the SQL is as follows:

SELECT [a couple static comma separated colums],

-- Dynamic part, generated by ASP, prints the following
-- about 200 times with slight variations (the number and
-- the Field Label), once for every 'definitionID' which
-- comes from a separate query

MIN(CASE definitionID WHEN 55 THEN (CASE WHEN dataField IS NOT NULL THEN (CASE WHEN dataField <> 'na' THEN dataField ELSE 'N/A' END) END) END) AS 'Field Label 55',

-- End Dynamic part

FROM [a static view]
GROUP BY [static]


I'm not sure what you meant in your reply regarding dynamic script in DTS. I'm quite new to DTS and have just tested it as an option to export to Access. Where would I place the script? I could possibly place the dynamic middle loop there (rather than ASP).

Thanks

 

[LogicalmanUS]

jpa,

To see further DTS scripting, refer to one of my previous threads &quot;DTS and Global Variables&quot;. I have placed a number of lines of script in there that may assist you.

When using DTS packages, I try to make everything run from the package. At the last resort I will call a SProc, but this is mainly if I have the SProc already written and being used by another application/process and don't want to duplicate code, especially if it may be altered in the future.

You can write sript directly into an 'SQL Task' object in DTS, but this is not truly dynamic in respect of Global Variables, for those you will need to use an 'ActiveX Script', with similar syntax to the following:

dim ConnTXT
dim strSQL
dim myGUID
dim strTableName

myGUID = DTSGlobalVariables(&quot;TempTable1&quot.Value
strTableName = &quot;##tCFTEMP&quot; & myGUID
Set ConnTXT = DTSGlobalVariables(&quot;CONNSQLOBJECT&quot.Value

strSQL = &quot;&quot;
strSQL = strSQL & &quot; delete &quot; & strTableName
strSQL = strSQL & &quot; WHERE [ORDER] = 'ORDER'&quot;
ConnTXT.execute strSQL


Where I have set the GVs of 'CONNSQLOBJECT' and 'TempTable1' in a previous ActiveX Script.

Hope this assists

Logicalman