Extract data from MS Access

Hi,
What are the various methods by which I can extract data
from MS Access databases using scripts (TSQL/ActiveX). I
know I can use MS DTS. But I'm interested to know about
other options.
TIA,
HariIf you want to bring the Access data into SQLS Server you can use OPENROWSET
or create a Linked Server and query the Access database directly. See
OPENROWSET or sp_addlinkedserver in Books Online for details and examples.
--
David Portas
SQL Server MVP
--|||Hari
You can use OPENDATASOURCE command
SELECT *
FROM OPENDATASOURCE(
'Microsoft.Jet.OLEDB.4.0',
'Data Source="d:\northwind.mdb";
User ID=Admin;Password='
)...Customers
"Hari" <anonymous@.discussions.microsoft.com> wrote in message
news:2de1701c46a4d$a3b57220$a301280a@.phx.gbl...
> Hi,
> What are the various methods by which I can extract data
> from MS Access databases using scripts (TSQL/ActiveX). I
> know I can use MS DTS. But I'm interested to know about
> other options.
> TIA,
> Hari

Extract data from MS Access

Hi,
What are the various methods by which I can extract data
from MS Access databases using scripts (TSQL/ActiveX). I
know I can use MS DTS. But I'm interested to know about
other options.
TIA,
HariIf you want to bring the Access data into SQLS Server you can use OPENROWSET
or create a Linked Server and query the Access database directly. See
OPENROWSET or sp_addlinkedserver in Books Online for details and examples.
David Portas
SQL Server MVP
--|||Hari
You can use OPENDATASOURCE command
SELECT *
FROM OPENDATASOURCE(
'Microsoft.Jet.OLEDB.4.0',
'Data Source="d:\northwind.mdb";
User ID=Admin;Password='
)...Customers
"Hari" <anonymous@.discussions.microsoft.com> wrote in message
news:2de1701c46a4d$a3b57220$a301280a@.phx
.gbl...
> Hi,
> What are the various methods by which I can extract data
> from MS Access databases using scripts (TSQL/ActiveX). I
> know I can use MS DTS. But I'm interested to know about
> other options.
> TIA,
> Hari

Extract data from MS Access

Hi,
What are the various methods by which I can extract data
from MS Access databases using scripts (TSQL/ActiveX). I
know I can use MS DTS. But I'm interested to know about
other options.
TIA,
Hari
If you want to bring the Access data into SQLS Server you can use OPENROWSET
or create a Linked Server and query the Access database directly. See
OPENROWSET or sp_addlinkedserver in Books Online for details and examples.
David Portas
SQL Server MVP
|||Hari
You can use OPENDATASOURCE command
SELECT *
FROM OPENDATASOURCE(
'Microsoft.Jet.OLEDB.4.0',
'Data Source="d:\northwind.mdb";
User ID=Admin;Password='
)...Customers
"Hari" <anonymous@.discussions.microsoft.com> wrote in message
news:2de1701c46a4d$a3b57220$a301280a@.phx.gbl...
> Hi,
> What are the various methods by which I can extract data
> from MS Access databases using scripts (TSQL/ActiveX). I
> know I can use MS DTS. But I'm interested to know about
> other options.
> TIA,
> Hari

Extract data from ACCESS database with pwd via ODBC

Hi,

My task is simple, I want to use the execute sql task editor to grab a value from a database in Access and put it in a variable. The connection is via ODBC and the access database is protected by a password.

I've done all the preliminary stuff such as running profiler to make sure that the package is getting the call to the database, setting up the ResultSet to be "single row" in the general tab, mapped the Result Set correctly, but nothing works. I get the same error every time.

This is my sql command:

selectcount(FingerPrintID) as FingerPrint

from Employee

Result Set is set up like this:

Result Name: FingerPrint ; Variable Name: User:: varDataset

Here is the error I get:

Error: 0xC002F309 at Execute SQL Task, Execute SQL Task: An error occurred while assigning a value to variable "varDataset": "Value does not fall within the expected range.".

My variable is set up as a Int16.

Please help!!!!!

If you could provide step by step example's that would really make my day.

Thanks,

Lawrence

What connection type are you using? ADO.NET?|||

Have you tried a 32-bit integer? That would explain the error message.

-Doug

|||

Hi,

I create a connection via odbc, using Microsoft Access Driver.

And i'm using this connection to access the data in Access DataBase.

|||

Hi,

I already try all kind of variable types that exist in SSIS, but all them retrieve the same error.

Any more ideia? I don't knew what's to do.

Thanx very much

|||

Have you properly mapped the column value that you want out of the resultset by naming the resultset FingerPrint or 0 (zero), as described in the "Populating a Variable with a Result Set" section of the Execute SQL Task topic? http://msdn2.microsoft.com/en-us/library/ms141003.aspx

-Doug

|||

Hi,

First i created an odbc, in ODBC Data Source.

Then i have been trying to connect via odbc, using Execute SqlTask. But it doesn't work

Can you give me some help on this?

Thanx

|||

Hi,

Yes, i already did this. But also didn' work.

Any more ideias?

Thanx

Extract data from ACCESS database with pwd via ODBC

Hi,

My task is simple, I want to use the execute sql task editor to grab a value from a database in Access and put it in a variable. The connection is via ODBC and the access database is protected by a password.

I've done all the preliminary stuff such as running profiler to make sure that the package is getting the call to the database, setting up the ResultSet to be "single row" in the general tab, mapped the Result Set correctly, but nothing works. I get the same error every time.

This is my sql command:

selectcount(FingerPrintID) as FingerPrint

from Employee

Result Set is set up like this:

Result Name: FingerPrint ; Variable Name: User:: varDataset

Here is the error I get:

Error: 0xC002F309 at Execute SQL Task, Execute SQL Task: An error occurred while assigning a value to variable "varDataset": "Value does not fall within the expected range.".

My variable is set up as a Int16.

Please help!!!!!

If you could provide step by step example's that would really make my day.

Thanks,

Lawrence

What connection type are you using? ADO.NET?|||

Have you tried a 32-bit integer? That would explain the error message.

-Doug

|||

Hi,

I create a connection via odbc, using Microsoft Access Driver.

And i'm using this connection to access the data in Access DataBase.

|||

Hi,

I already try all kind of variable types that exist in SSIS, but all them retrieve the same error.

Any more ideia? I don't knew what's to do.

Thanx very much

|||

Have you properly mapped the column value that you want out of the resultset by naming the resultset FingerPrint or 0 (zero), as described in the "Populating a Variable with a Result Set" section of the Execute SQL Task topic? http://msdn2.microsoft.com/en-us/library/ms141003.aspx

-Doug

|||

Hi,

First i created an odbc, in ODBC Data Source.

Then i have been trying to connect via odbc, using Execute SqlTask. But it doesn't work

Can you give me some help on this?

Thanx

|||

Hi,

Yes, i already did this. But also didn' work.

Any more ideias?

Thanx

Extra Browser window when exporting?

Hello,
I'm using the HTML Viewer and URL access to render my reports. When I change the format (for example to excel) on the HTML Viewer, and click the "export" link beside the drop down. A new browser window pops up and a save box prompting me to specify the location of where to save. After I choose the save destination, download the new formated report, and close the save box, the newly opened browser window remains open.

Is there any way to automatically close that browser window? or maybe even not show that browser window at all?

Thanks in AdvanceUnfortunatly there is no way to get rid of this extra box.|||Oh Well... Thanks for the response

Extra Browser window when exporting?

Hello,
I'm using the HTML Viewer and URL access to render my reports. When I change the format (for example to excel) on the HTML Viewer, and click the "export" link beside the drop down. A new browser window pops up and a save box prompting me to specify the location of where to save. After I choose the save destination, download the new formated report, and close the save box, the newly opened browser window remains open.

Is there any way to automatically close that browser window? or maybe even not show that browser window at all?

Thanks in AdvanceUnfortunatly there is no way to get rid of this extra box.|||Oh Well... Thanks for the response

Exterpise Manager Select Export (ASCII,Excel,Access)?

Hi All. A client needs to send me some sample data. He has insisted he can query the table in Enterprise Manager via a simple select... "Select * from Table1"...

Now I need to get the data in some simple form (ASCII, Excel, Access, etc.) sent to me.

Can someone please provide me the info so I can pass it on for him to query a table from Enterprise Manager and "export it" to a simple file so I can receive it.

ANY THOUGHTS would be helpfull and GREATLY Appreciated!

Thanks.

BillIf this is a one timer I would go for "Tools -> Data Transformation Services -> Export Data".

// Pati|||Hey Pati... It may just be a one timer... but if the data looks good, it may be more frequent. it turns out some other process is taking records from this table, and may be removing them... Part of the reason we are trying to get some snapshots of the data.

I'm trying not to write an app until I know if we need this data.

Is it really simple to use to do the menu picks? Doesn't seem like the end user is very experienced, nor am I on Sql Server.

Thanks for the thoughts.

Bill|||If you think that you will need the same procedure again then you can save the DTS package for further use and then schedule it to run as desired.
...or then for another approach you could automate everything with scripts/scheduled jobs.

But as you're saying that you don't have much experience in SQL Server and that this might just be a one off solution then I would stick to DTS.

// Patisql

External Images on Network Share Driving Me Mad

Hi,
Using RS 2000 SP2, I'm trying to access external images that a stored
on another computer via a network share. However, I get nothing but
red x's in my report. I understand that in order for this to work, I
need to setup the UnattendedExecutionAccount. Here's the rsconfig call
I've used:
rsconfig -e -u DOMAIN\user -p password -a windows
However, the pictures still don't show. I've logged on as the user in
question and confirmed the user can see the folder and open the
images. I've also double checked that I have the correct case for the
user and the password. The image source is in the format:
file://\\Computer\Data\Images\xxxx.jpg
I've also tried:
\\Computer\Data\Images\xxxx.jpg
If I copy the images to a local drive everything works.
Is there a problem using UNC paths? Maybe the developer install has
trouble accessing shared network paths? This is driving me nuts, can
anybody shed any light?
Thanks,
CamelOn Mar 3, 11:20 am, Daniel wrote:
> Mine works with the following:
> file:\\server\share folder\xxxxxx.gif
> EggHeadCafe.com - .NET Developer Portal of Choicehttp://www.eggheadcafe.com
Unfortunately I was not able to resolve this. I suspect that it is a
permissions issue - and that it has something to do with the
UnattendedExecutionAccount not working the way I expect it to.
Regardless, I've given up and used an IIS virtual folder instead. At
least there I can control what credentials are used to access the
share.
Thanks,
Camel

External access to SQL Server

Hello,

I'm not sure whether this is right section to post this. It seems the most relevant one to me. (Please point me in the right direction if it is not).

I am a programmer/web developer for a medium sized organisation in the financial industry (in Australia). Obviously being in the finance industry, we have very strict guidelines and access when it comes to security. We are currently in the process of converting our website to use a SQL Server database (instead of MS Access which we have been using for years). In the past, we have accessed our web database via SFTP as it was only an Access file. Now that we are converting to SQL Server, we will still need access to our database (which is on a server hosted externally). It seems a bit silly (and even bizarre) to me to consider trying to access a SQL Server database via SFTP (as that, to me, defeats the purpose of some of its security features), but is it even possible?

My security guys here will only allow me to access the external server that hosts our website/database via SFTP (on a machine that is outside our network, so any files that I wish to update on our website I need to copy over via USB or whatever). If I am not able to get into the SQL Server administrator program to change our database via SFTP, is there someway that I can "drop" a changed copy of our database into a directory somewhere that SQL Server can "pick up"?

Am I making sense?

Many thanks,

Bronwyn

You could drop that, but you would anyway have to do some administrative stuff, like detaching the old database, are even just stopping the service. Perhaps it is better to install a web admin client for the SQL Server which is downloadable at Microsoft. It does not provide the whole functionality of the Managment Studio or Enterprise Manager, but this would be a proper way to execute your changing scripts to narrow the maintaince window.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||

host the client application inside your site

or design a webservice inside your site

and have the remote application

access Sql server through webservice

|||Thanks guys. That's helpful.sql

external access denied to update a config file

Hello,

I'm having an issue with a CLR Stored procedure. Everything works great in a 32 bit environment, I have a CLR SP that updates an xml file stored on a local drive. When I execute the Stored proc it does go and update what I want it to in a 32 bit system. When I run the CLR SP on a 64 bit cluster, I seem to have give the "everyone" group write permissions to my G: drive (which is where the file is located that I'm updating). What security context is this SP running under? I thought it would be either under the SQL Service account (which is a domain user in the local administrators group) or what I'm logged in as when I run it from Management Studio (which is a domain admin, also in the local administrators group). If I have given the local administrators group "Full Control" access to the G: drive, why isn't this enough? Why do I have to give the Everyone group write access?

The security context seems odd to me, it seems like it's not running as either one of those 2 users I mentioned, because if it was, then it should be able to update the xml file.

Any help appreciated. Here's the error I'm getting:

Msg 6522, Level 16, State 1, Procedure usp_XMLWriter, Line 0

A .NET Framework error occurred during execution of user defined routine or aggregate 'usp_XMLWriter':

System.UnauthorizedAccessException: Access to the path 'g:\ssisPackages\BuildCalendar\andy.dtsConfig' is denied.

System.UnauthorizedAccessException:

at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)

at System.IO.FileStream.Init(String path, FileMode mode, FileAccess access, Int32 rights, Boolean useRights, FileShare share, Int32 bufferSize, FileOptions options, SECURITY_ATTRIBUTES secAttrs, String msgPath, Boolean bFromProxy)

at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access, FileShare share, Int32 bufferSize, FileOptions options, String msgPath, Boolean bFromProxy)

at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access, FileShare share)

at System.Xml.XmlTextWriter..ctor(String filename, Encoding encoding)

at System.Xml.XmlDocument.Save(String filename)

at XMLWriter.StoredProcedures.usp_XMLWriter(String xmlDocPath, String xmlNodePath, String xmlInnerText)

.

I found out that I'm able to get this to work if I put the SQL Service User account directly into the security of the G: Drive. For some reason, even though the user is in the Local Administrators group, it doesn't seem to pick up the fact that my user in is in the group, and therefore won't let him update a file in the folder.

Does anyone know of any issues in a 64 bit cluster where a CLR Stored proc isn't able to decipher the users in a local Group?

|||This turned out to be a cluster issue. We weren't doing permissions through the cluster, we were doing permissions to the file shares through each node, which is a mistake in a cluster.

external access - the definitive solution?

ok, I know this topic has been covered COUNTLESS times in this group,
yet the solution is still very, very hard to come by. Has anybody
successfully published their reports (through a custom .net web
application using URL access to the report server) to an external
network / internet?
If so, how... Custom security extension? Handing out network logins
(with very restricted rights) to the report server?
A final, solid solution would be greatly appreciated...Yes, we did do it and it and there are compromises. A custom security
extension is painful but is probably most secure. The simplest way for an
Internet solution is to -
1. Permit "annonymous" accesss to a low privelege account on the
ReportServer virtual directory.
2. Everything must be https and you will need a registered FQDN server side
cert.
3. However, one still needs to authenticate the user which needs to be done
by your application. Otherwise you have to go custom+Forms authentication or
handing out network logins .. ugh!
4. In order to secure reports, we used a sessionkey generated by our app and
passed it as a hidden parameter from an embedded IE browser using url access.
5. The RDLs execute SPs and the session check is done at this point. With a
custom solution one would do the check prior to allowing access to the
ReportServer.
6. You then need to follow MBSA guidelines to lock down your IIS server
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/secmod/html/secmod104.asp
P.S ... the last part is nuts & you'll wonder why not have all clients
connect over IPSec and be done with it.
"Gearoid" wrote:
> ok, I know this topic has been covered COUNTLESS times in this group,
> yet the solution is still very, very hard to come by. Has anybody
> successfully published their reports (through a custom .net web
> application using URL access to the report server) to an external
> network / internet?
> If so, how... Custom security extension? Handing out network logins
> (with very restricted rights) to the report server?
> A final, solid solution would be greatly appreciated...
>

External Access

Hey all,
I am trying to access SQL Server from external site on internet. I have
set the server to listen on a random port to prevent discovery of the
server at the default 1433. I am forwarding the port through the firewall
to the SQL box. I can connect internally to the chosen port but am unable
to establish a connection externally. The server receives the connection
attempt but the reply doesn't seem to make it back. The firewall does not
perform any filtering of outgoing packets. I would have thought that NAT
would ensure responses were returned to the external network.
Are there any additional parameters or network considerations that must be
made to allow the external access?
Cheers,
Aaron Wolski.INF: TCP Ports Needed for Communication to SQL Server Through a
Firewall
http://support.microsoft.com/?id=287932 - This article documents the
minimum TCP/IP ports that are required to communicate to SQL Server over a
firewall.
HOWTO: Use ADO to Connect to a SQL Server That Is Behind a Firewall
http://support.microsoft.com/?id=269882 - When you use ActiveX Data
Objects (ADO) to connect to a SQL Server 7.0 or SQL Server 2000 server that
is behind a firewall, consider the following: The firewall must be
configured to permit port 1433 incoming (or the port numbers that SQL
Server...
-oj
RAC v2.2 & QALite!
http://www.rac4sql.net
"Aaron Wolski" <ajwNO@.SPAMuqconnectPLEASE.net> wrote in message
news:Pine.OSF.4.58.0310071002140.5903@.fox.uq.net.au...
> Hey all,
> I am trying to access SQL Server from external site on internet. I have
> set the server to listen on a random port to prevent discovery of the
> server at the default 1433. I am forwarding the port through the firewall
> to the SQL box. I can connect internally to the chosen port but am unable
> to establish a connection externally. The server receives the connection
> attempt but the reply doesn't seem to make it back. The firewall does not
> perform any filtering of outgoing packets. I would have thought that NAT
> would ensure responses were returned to the external network.
> Are there any additional parameters or network considerations that must be
> made to allow the external access?
> Cheers,
>
> Aaron Wolski.
begin 666 1ptrans.gif
L1TE&.#EA`0`!`( ``/\SS ```"'Y! $`````+ `````!``$`0 ("A%$`.P``
`
end

External Access

Hello,

I want to access my database server from the internet. I've created a rule
on my router to redirect port 1433 to the desired machine, but still get a
Server does not exist, or access denied message. When I change the
(external) ip to the local IP address, connection succeeds. Is there
anything i need to change within SQL Server to allow external (internet)
access?http://support.microsoft.com/defaul...kb;en-us;287932

Simon|||thnx!

"Simon Hayes" <sql@.hayes.ch> wrote in message
news:1110803406.369995.165930@.z14g2000cwz.googlegr oups.com...
> http://support.microsoft.com/defaul...kb;en-us;287932
> Simon

external access

Hi evebody,

I′m making some reports on VS 2003, using RS 2000 and deploying it to a RS 2005 server

my problem is: is there any way to make a default user for accessing these reports, cause my reports can be seen by those who I grant permission (user from my domain)

I found the "execution account", but I couldn′t understand how I would use it, if this helps on my problem at all..and if I can use it with RS 2000 reports. (I know that some features of RS 2005 cannot be used on RS 2000 reports...)

Best Regards.

Pedro

Hi everybody!!

I′ve been reading some stuff, and I believe I can explain much better my problem.

I have a system that will be accessed on extranet. Users from extranet will be authenticated by an AD.

These users all have authorization to access my systems reports.

I′ve been reading the reporting services extension, and I discovered that I can use the IAuthorization Interface.

In this case I would implement the checkAccess method, checking if the users ID is in my AD and grant permission to requested report.

is my idea right?

Best regards,

Pedro

sql

Extended SP ?

Hi All,
I want to call an extended proc from a trigger. Is there a way to access the
"Inserted" and "Deleted" tables from the extended proc'
Thanks,
AshkanCould the programming possibly be written using T-SQL? If so, then don't
even pursue the extended proc avenue any further.
"Ashkan Daie" <adaie@.nospam.org> wrote in message
news:%23ehdN4VDFHA.1836@.tk2msftngp13.phx.gbl...
> Hi All,
> I want to call an extended proc from a trigger. Is there a way to access
the
> "Inserted" and "Deleted" tables from the extended proc'
>
> Thanks,
> Ashkan
>|||I wish it could :). That is why I am pursuing the extended proc method.
"JohnnyAppleseed" <someone@.microsoft.com> wrote in message
news:u%23JCU6VDFHA.960@.TK2MSFTNGP09.phx.gbl...
> Could the programming possibly be written using T-SQL? If so, then don't
> even pursue the extended proc avenue any further.
> "Ashkan Daie" <adaie@.nospam.org> wrote in message
> news:%23ehdN4VDFHA.1836@.tk2msftngp13.phx.gbl...
> the
>|||AFAIK, You cannot do this in SQL Server 2000.
Having said that, SQL Server 2005 has flexibility in writing extended stored
procedures in amanged code and even triggers can be written in .NET
languages. These triggers have access to the pseudo tables INSERTED and
DELETED.
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
"Ashkan Daie" <adaie@.nospam.org> wrote in message
news:eRhTl9VDFHA.4072@.TK2MSFTNGP10.phx.gbl...
> I wish it could :). That is why I am pursuing the extended proc method.
> "JohnnyAppleseed" <someone@.microsoft.com> wrote in message
> news:u%23JCU6VDFHA.960@.TK2MSFTNGP09.phx.gbl...
access
>|||Hi Ashkan, I hope all is well. Please email me some info, too on extended pr
ocs, db 39482, and Bozbecon, Moose thread, and Rpnzyl threads 9-20. I'm in y
our area, would like to meet for lunch...if you want to. It will be nice.
Sincerely,
C.H.
> Hi All,
> I want to call an extended proc from a trigger. Is there a way to access t
he
> "Inserted" and "Deleted" tables from the extended proc'
>
> Thanks,
> Ashkan
>
User submitted from AEWNET (http://www.aewnet.com/)

Expressions in queries

I'm new to SQL Server and converting a VB app from Access back end to
SQL Server back end. In this app I use a lot of expressions in
queries
e.g. SELECT iif(isnull([MyText],"",[MyText]) as sRemoveNull
or SELECT [SaleDate]>=#01-Jan-2004# as bUseSale
I can't get anything of the sort to work in SQL Server. I've written a
few UDFs to cope with some more common expressions, but for some cases
it would be much easier to be able to use a complex expression within
the query.
Stored Procedures are not really appropriate as queries are built in
VB and the SQL string passed to SQL Server.
Am I missing something obvious?
Thanks,
MattThese two examples may help. You can check out the full syntax of these
expressions in SQL Server Books Online.
SELECT COALESCE(mytext,'') AS sRemoveNull
SELECT CASE WHEN saledate>='20040101' THEN 1 ELSE 0 END AS bUseSale

> queries are built in
> VB and the SQL string passed to SQL Server.
In the world of SQL Server that's generally a really bad idea. Standard
practice is to use SPs for all data access unless you have an exceptional
reason not to. SPs make the most sense from the point of view of
performance, security and maintainability. If you just want to continue
using SQL the way you've always used Access then you can't expect to realize
all the benefits of the platform change. You may well be better off sticking
with your Access backend.
David Portas
SQL Server MVP
--|||Thanks David, that'll get me started.
Re your further comments, I was also going to post the
"How do I do SELECT * FROM @.tablename" question last night,
but did some reading here first and found numerous responses along the
same lines as yours. I've read two articles from www.sommarskog.se
about dynamic SQL and it seems I have to accept that I'll have to do
things quite differently in the future than I have been (sticking with
Access is not an option). The upside is that I'm learning heaps and
enjoying doing it.
Thanks for your comments.
Matt
On Sun, 30 Jan 2005 18:03:56 -0000, "David Portas"
<REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote:

>These two examples may help. You can check out the full syntax of these
>expressions in SQL Server Books Online.
>SELECT COALESCE(mytext,'') AS sRemoveNull
>SELECT CASE WHEN saledate>='20040101' THEN 1 ELSE 0 END AS bUseSale
>
>In the world of SQL Server that's generally a really bad idea. Standard
>practice is to use SPs for all data access unless you have an exceptional
>reason not to. SPs make the most sense from the point of view of
>performance, security and maintainability. If you just want to continue
>using SQL the way you've always used Access then you can't expect to realiz
e
>all the benefits of the platform change. You may well be better off stickin
g
>with your Access backend.
>--
>David Portas
>SQL Server MVP

Expressions

When creating expressions we have access to a list of functions. It is my understanding that while these functions seem to have the same names and parameters as SQL functions, they are not so. They are implemented in the package libraries themselves. It is also my understanding that this function library cannot be extended to add new ones.

Am I correct? If so... why not?

Leaving alone the fact that they follow the same screwy names as SQL (instead of .NET on which SSIS is built on) and what seems to be a limited library (i.e. You have YEAR(), MONTH(), DAY() functions but no HOUR(), MINUTE(), or SEC() functions -- instead you have to use DATEPART())

I mean honestly... a common expression for most people is using date and times for folder and filenames... So instead of a simple .NET type expression of DateTime.ToString("yyyyMMdd") or Format(DateTime.Now, "yyyyMMdd_hhmmss") I end up with the very complex:

(DT_STR, 4, 1252) YEAR( GETDATE() ) + RIGHT("0" + (DT_STR, 2, 1252) MONTH( GETDATE() ), 2) + RIGHT("0" + (DT_STR, 2, 1252) DAY( GETDATE() ), 2) + "_" + RIGHT("0" + (DT_STR, 2, 1252) DATEPART("hour", GetDate()), 2) + RIGHT("0" + (DT_STR, 2, 1252) DATEPART("minute", GetDate()), 2) + RIGHT("0" + (DT_STR, 2, 1252) DATEPART("second", GetDate()), 2)

Personally I find myself using Script Tasks and Variables to "build" my expressions and just use Expressions to set the property to the variable. (Which I think may defeat the full purpose of expressions.)

Any thoughts?

Hi Jason,

Yes, you are correct that the SSIS expression language in SQL Server 2005 is not extensible, and yes the set of available functions id fairly small. The primary reason for that is that the development team simply ran out of time. Several of the functions that you see today were in fact added in response to beta feedback from customers such as yourself. I think it would be great if you have the time to submit your suggestions for things that you would like to see added. You can do that by going here:http://msdn.microsoft.com/sql/bi/integration/ and choosing the MSDN Product Feedback link under Support.

As for why the expression language is not .net -- there is a fairly long history there. Note that the existing language is very C-like, and is optimized to work efficiently with DataFlow data types. The dataflow engine itself is native code, not .NET, and the expression language is tightly integrated with the type system.

I hope that helps to answer your questions. Do not hesitate to ask if you have further questions.

Thanks
Mark

|||

Thanks Mark,

Will take up your suggestion and submit feedback.

I am surprised (and a bit disheartened) to learn that the dataflow engine is not dotNET. Microsoft has spent a lot of time and publicity pushing .NET. (Hell, it was even integrated into the SQL engine...)

Authoring packages is done in Visual Studio 2005 which is PRIMARLY a dotnet development environment. (Lets not go down the road of asking why VS itself is not written in dotnet...)

Most of the tasks SEEM to be written in dotNET... (unless they are just PIA wrappers...)

Yet...

You guys (and Microsoft as a whole) does not seem fully utilize your own products... Microsoft Office is still plain old C++ (or C-like), VS is plain old C++ (or C-like), and the SSIS core engine...

(I apologize if this puts you on the spot Mark.)

|||

Not using our own products? Remember we own the best-selling c++ tools too! ;-)

One key reason why our engine is native code is that, through native code, we can write our own code to manage memory. One of the benefits of .Net is precisely that memory is managed for you - which is a real boon until you need to get down and dirty at the system level. .Net has indeed been widely marketed - but not as a replacement for native code in all places.

BTW, the excellent Steve Texeira has an interesting blog on his role as a C++ program manager which includes many insightful posts on such issues, such as this one: http://blogs.msdn.com/texblog/archive/2006/01/04/509428.aspx

I would never regard C++ as "plain old" - and again, Steve Tex has an good outlook on that too: http://blogs.msdn.com/texblog/archive/2006/01/04/509396.aspx

Donald

BTW - our designer, the UI bits, is indeed written in managed code. But the underlying components and tasks of SSIS are mostly native code.

|||

Thanks for responding Donald.

Of course, C++ is not "plain old"... I labelled it that way to emphasize what I perceived as a push for .NET technologies from Microsoft yet a reluctance to fully board the bandwagon...

We still use C++ here in our shop, but for server side process where speed is an issue.

|||

Mark Durley wrote:

Yes, you are correct that the SSIS expression language in SQL Server 2005 is not extensible, and yes the set of available functions id fairly small. The primary reason for that is that the development team simply ran out of time. Several of the functions that you see today were in fact added in response to beta feedback from customers such as yourself. I think it would be great if you have the time to submit your suggestions for things that you would like to see added. You can do that by going here:http://msdn.microsoft.com/sql/bi/integration/ and choosing the MSDN Product Feedback link under Support.

While we're on the subject of the usability of expressions - a slight digression.

I dislike the fact that sometimes you have to write horribly long, clunky expressions to achieve some things - particularly around concatenation. Jason gives a great example regarding datetimes in this thread.

A really nice feature would be to allow us to build up these long concatenations bit by bit in the same Derived Column component. The way I envisage this happening is by referencing other derived columns in the same component.

I've requested this feature at the feedback center but the site is down at the moment so I can't link to it!!

-Jamie

Express reporting services

I'd like to graduate from using access as my back-end db to using sql express however i'm concerned that I won't be able to create reports with express. Is this true? And if it is, is there some third party tool (preferable free) that will allow me to create a reports....Or can I link access to the sql server tables just for reports?

Thanks,.

Hi,

SQL Reporting Services will be added to the Express product later this year when we release Service Pack 1. You can use Reporting Services in Express to create reports against your SQL Express databases.

You can also create linked tables to your SQL database in Access and use the reporting functionality built into Access to create your reports.

Regards,

Mike Wachal
SQL Express Team

|||Hi,

Can we create reports using SQL SERVER EXPRESS, if we install crystal reports 10 (or later editions) separately ?

Express reporting services

I'd like to graduate from using access as my back-end db to using sql express however i'm concerned that I won't be able to create reports with express. Is this true? And if it is, is there some third party tool (preferable free) that will allow me to create a reports....Or can I link access to the sql server tables just for reports?

Thanks,.

Hi,

SQL Reporting Services will be added to the Express product later this year when we release Service Pack 1. You can use Reporting Services in Express to create reports against your SQL Express databases.

You can also create linked tables to your SQL database in Access and use the reporting functionality built into Access to create your reports.

Regards,

Mike Wachal
SQL Express Team

|||Hi,

Can we create reports using SQL SERVER EXPRESS, if we install crystal reports 10 (or later editions) separately ?