ODBC Destination Issues with SSIS

Hi All,

In Migrating from DTS to SSIS we have scenario where data from OLE DB (SQL Server) is inserted into ODBC source (transoft driver). In case of DTS, ODBC component was provided. For SSIS we used the Destination Script Component and programmatically used the ODBC related calls to design similar functionality. However we are getting the following error –

Error[42000][Transoft] [TSODBC][usqld] Name expected@.

However the same program pointing to Microsoft Access ODBC driver works fine.

Any help in this direction will be greatly helpful.

Thanks,

S Suresh

Well, perhaps Transoft needs more information in their ODBC settings. That is, MS Access isn't really a database engine and hence is more "simple" to use.|||try writting the script code to insert a record into your odbc destination and run this script code from some other context, like a script task or even from VB. the goal is to separate your script/communication with driver away from something that might be SSIS specific like our ado.net connection manager or the script component. in other words, get the script to work outside of SSIS before adding more complexity :)

ODBC Access Driver error in Vista

I am migrating my development environment from XP Pro to Vista, and my website is now serving up the following error:

Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[Microsoft][ODBC Microsoft Access Driver] Disk or network error.

The code in question is as follows:

set dbConn = Server.CreateObject("ADODB.Connection")
szPath=Server.MapPath("http://../")
szProvider="Driver=Microsoft Access Driver (*.mdb); DBQ=" & szPath
szProvider=szProvider & "\database\" & szDir & "\" & szDB & ".mdb;"
if bDebug then fpDebug.WriteLine("szProvider: " & szProvider)
dbConn.Open szProvider

The error happens on the last line where I try to open the connection. I've tried also using a dbConn.Provider="Microsoft.Jet.OLEDB.4.0" line, to no avail. The item that is passed to the Open command is

Driver=Microsoft Access Driver (*.mdb); DBQ=C:\bobstuff\database\demo\HMGA.mdb;

I'm not familiar enough with DNS and IIS to figure this out. Any suggestions -- other than porting over to SQL and .NET? You don't buy a Ferrari when you only drive in a 30 MPH town.

I'm having a similar problem. I checked my installed drivers and noticed I don't have a standard MS ODBC driver. You might want to make sure you have one. Control Panel --> Administrative Tools --> Data Sources --> Drivers Tab. MDAC doesn't seem to install on my Win Vista (x64) machine so I'm not sure where to go from here.

|||Does anyone have a solution to this issue. I have given IISUser full permissions to the Access file in the directory. I have checked the read permissions for IISUser and actually have given everyone read to the directory and the file in question. I can open the file in MS Access on my computer and update and change information in it. The same code works fine on my XP SP2 machine. I am at a loss.|||I am having exactly the same problem today... Vista ultimate, was fine on xp|||This article worked for me.

http://mikeplate.wordpress.com/2006/11/24/running-legacy-asp-scripts-on-vista-and-iis-70/|||

Thanks

This worked for me. The MS article at the bottom of the blog did not.

|||You beautiful geeks!!! I have spent hours talking to MS Tech Support, and they wanted to charge me $99 for email support, or $245 for phone support as this was a "pro-level" problem. Yes, I plan to convert over to .NET, but as a one guy development team who has over 50K lines of code, I don't want to do it right now. MSFT has a KB article that says the same thing (926939), but it uses command line instead of the GUI. Thanks for giving me a solution.

ODBC Access Driver error in Vista

I am migrating my development environment from XP Pro to Vista, and my website is now serving up the following error:

Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[Microsoft][ODBC Microsoft Access Driver] Disk or network error.

The code in question is as follows:

set dbConn = Server.CreateObject("ADODB.Connection")
szPath=Server.MapPath("http://../")
szProvider="Driver=Microsoft Access Driver (*.mdb); DBQ=" & szPath
szProvider=szProvider & "\database\" & szDir & "\" & szDB & ".mdb;"
if bDebug then fpDebug.WriteLine("szProvider: " & szProvider)
dbConn.Open szProvider

The error happens on the last line where I try to open the connection. I've tried also using a dbConn.Provider="Microsoft.Jet.OLEDB.4.0" line, to no avail. The item that is passed to the Open command is

Driver=Microsoft Access Driver (*.mdb); DBQ=C:\bobstuff\database\demo\HMGA.mdb;

I'm not familiar enough with DNS and IIS to figure this out. Any suggestions -- other than porting over to SQL and .NET? You don't buy a Ferrari when you only drive in a 30 MPH town.

I'm having a similar problem. I checked my installed drivers and noticed I don't have a standard MS ODBC driver. You might want to make sure you have one. Control Panel --> Administrative Tools --> Data Sources --> Drivers Tab. MDAC doesn't seem to install on my Win Vista (x64) machine so I'm not sure where to go from here.

|||Does anyone have a solution to this issue. I have given IISUser full permissions to the Access file in the directory. I have checked the read permissions for IISUser and actually have given everyone read to the directory and the file in question. I can open the file in MS Access on my computer and update and change information in it. The same code works fine on my XP SP2 machine. I am at a loss.|||I am having exactly the same problem today... Vista ultimate, was fine on xp|||This article worked for me.

http://mikeplate.wordpress.com/2006/11/24/running-legacy-asp-scripts-on-vista-and-iis-70/|||

Thanks

This worked for me. The MS article at the bottom of the blog did not.

|||You beautiful geeks!!! I have spent hours talking to MS Tech Support, and they wanted to charge me $99 for email support, or $245 for phone support as this was a "pro-level" problem. Yes, I plan to convert over to .NET, but as a one guy development team who has over 50K lines of code, I don't want to do it right now. MSFT has a KB article that says the same thing (926939), but it uses command line instead of the GUI. Thanks for giving me a solution.

ODBC Access Driver error in Vista

I am migrating my development environment from XP Pro to Vista, and my website is now serving up the following error:

Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[Microsoft][ODBC Microsoft Access Driver] Disk or network error.

The code in question is as follows:

set dbConn = Server.CreateObject("ADODB.Connection")
szPath=Server.MapPath("http://../")
szProvider="Driver=Microsoft Access Driver (*.mdb); DBQ=" & szPath
szProvider=szProvider & "\database\" & szDir & "\" & szDB & ".mdb;"
if bDebug then fpDebug.WriteLine("szProvider: " & szProvider)
dbConn.Open szProvider

The error happens on the last line where I try to open the connection. I've tried also using a dbConn.Provider="Microsoft.Jet.OLEDB.4.0" line, to no avail. The item that is passed to the Open command is

Driver=Microsoft Access Driver (*.mdb); DBQ=C:\bobstuff\database\demo\HMGA.mdb;

I'm not familiar enough with DNS and IIS to figure this out. Any suggestions -- other than porting over to SQL and .NET? You don't buy a Ferrari when you only drive in a 30 MPH town.

I'm having a similar problem. I checked my installed drivers and noticed I don't have a standard MS ODBC driver. You might want to make sure you have one. Control Panel --> Administrative Tools --> Data Sources --> Drivers Tab. MDAC doesn't seem to install on my Win Vista (x64) machine so I'm not sure where to go from here.

|||Does anyone have a solution to this issue. I have given IISUser full permissions to the Access file in the directory. I have checked the read permissions for IISUser and actually have given everyone read to the directory and the file in question. I can open the file in MS Access on my computer and update and change information in it. The same code works fine on my XP SP2 machine. I am at a loss.|||I am having exactly the same problem today... Vista ultimate, was fine on xp|||This article worked for me.

http://mikeplate.wordpress.com/2006/11/24/running-legacy-asp-scripts-on-vista-and-iis-70/|||

Thanks

This worked for me. The MS article at the bottom of the blog did not.

|||You beautiful geeks!!! I have spent hours talking to MS Tech Support, and they wanted to charge me $99 for email support, or $245 for phone support as this was a "pro-level" problem. Yes, I plan to convert over to .NET, but as a one guy development team who has over 50K lines of code, I don't want to do it right now. MSFT has a KB article that says the same thing (926939), but it uses command line instead of the GUI. Thanks for giving me a solution.