DTS Designer Error: The specified module could not be found

Hi folks
I am connecting to a SQL Server 2000 (SP3) from a client Enterprise
Manager. It connects to it, well.
But if I try to open an existing DTS package (located under Local
packages) it throws an error "DTS Designer Error The specified module
could not be found" and it closes.
Same error occurs if I try to open a .dts file also.
Your help is appreciated.
Thanks in advance
Venkat.
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!Hi
Searching google for you error messages turned up these two possible
solutions:
http://tinyurl.com/66tsu
http://tinyurl.com/6e6jm
John
"Venkatesan" wrote:

> Hi folks
> I am connecting to a SQL Server 2000 (SP3) from a client Enterprise
> Manager. It connects to it, well.
> But if I try to open an existing DTS package (located under Local
> packages) it throws an error "DTS Designer Error The specified module
> could not be found" and it closes.
> Same error occurs if I try to open a .dts file also.
> Your help is appreciated.
> Thanks in advance
> Venkat.
>
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!
>

DTS - How to...

I am new to DTS and I need some help. I have created a package which connects to an Oracle db, runs a query and then either inserts or updates a sql server table based on a DTSLookup. The package contains 3 connections and one DataDrivenQueryTask. It eventually will be scheduled to execute every 10 minutes.

Its working at the moment but there is one part I am missing. The table in Oracle has a field which contains the date the record was last modified. I have currently hardcoded into the where clause this statement LastModDate > trunc(sysdate).

This returns all records modified for the current day but of course, everytime it executes it will return records I have already processed.

Sorry, for babbling on but finally here's the question

How can I use a variable in my Source query? I was hoping I could store the last execution date/time in a sql table, retrieve it and use it in in the Where clause. Something like LastModDate > @.LastExecuteDate. I am not sure how to set this up. Any ideas or pointers you have would be great?One approach:

Write an ActiveX Script to take the place of the Connection to Oracle. In your ActiveX Script, establish the connection to Oracle, pull the data into SQL in a temp table and then use the Data Driven Query Task to process the data from the temp table. The ActiveX script can contain either a lookup to a date/time field which us updated each time the script processes, or you can take the max date/time field from the last job processed as your new starting point.

There are lots of things wrong with this approach, but it is doable.

Another Approach:
In the ActiveX script for the Data Driven Query task, you can do a similar thing with pulling in the last date/time stamp and then use the DTSTransformStat_SkipRow to skip over a record that you don't want to process. This still involves pulling the data across the nework from Oracle to SQL, but you will save some processing time.

Again, there are porbably lots of things wrong with this approach, but it is doable.

Finally, I have not yet discovered a way to manipulate the SQL text in a Data Pump or Data Driven Query object. I believe that it is theoretically possible to do it (by manipulating the DTS package in SQL DMO), but I have never really had the time to fully explore this.

Best of luck to you...

Hugh Scott

Originally posted by brucevde
I am new to DTS and I need some help. I have created a package which connects to an Oracle db, runs a query and then either inserts or updates a sql server table based on a DTSLookup. The package contains 3 connections and one DataDrivenQueryTask. It eventually will be scheduled to execute every 10 minutes.

Its working at the moment but there is one part I am missing. The table in Oracle has a field which contains the date the record was last modified. I have currently hardcoded into the where clause this statement LastModDate > trunc(sysdate).

This returns all records modified for the current day but of course, everytime it executes it will return records I have already processed.

Sorry, for babbling on but finally here's the question

How can I use a variable in my Source query? I was hoping I could store the last execution date/time in a sql table, retrieve it and use it in in the Where clause. Something like LastModDate > @.LastExecuteDate. I am not sure how to set this up. Any ideas or pointers you have would be great?

DSN-less connection to SQL Server with sql 7 and sql 2k

Hi,
I have commercial asp webspace hosting an application that connects to
an SQL machine on my local network. THe machine has both SQL 7 and SQL
2k running on it. Webspace and SQL are both running on Win2k.
The connection string I am using is as follows:
Application("Application_ConnectionString") = "Provider=sqloledb;" & _
"Network Library=DBMSSOCN;" & _
"Data Source=aaa.bbb.ccc.ddd,1433;" & _
"Initial Catalog=DBName;" & _
"User ID=xxx;" & _
"Password=yyy;"
It works fine when pointing to a database in the sql 7 instance. But
what I want to achieve is to connect to a database in the sql 2k
instance. Running locally, I can achieve this by substituting
aaa.bbb.ccc.ddd\servername_sql2k. But I cannot get it to connect to
the sql 2k instance from the commercial webspace. I get the following
message in this case:
Microsoft OLE DB Provider for SQL Server error '80004005'
[DBMSSOCN]General network error. Check your network documentation.
SO, my question is, how do you address a database in the sql 2k
instance via a dsn-less connection? Do you have to set SQL 7 and SQL
2k to listen on different ports? I have tried this but have not met
with success.
Any help gratefully received...
Thanks in advanceCan you make a connection from a DSN from the commercial webspace using
aaa.bbb.ccc.ddd\servername_sql2k as the server name?
Also can you connect via Query Analyzer form the commercial webspace using
that servername?
Rand
This posting is provided "as is" with no warranties and confers no rights.|||Hi Rand
Thanks for your reply. Restricted access to the commercial web space
means that I DSN cannot be established, hence the DSN less connection.
For that reason I cannot try your suggestion of connecting Query
Analyser from that server. However, I have ascertained that both
instances of SQL Server can be reached from an external server by
connecting from a server where where I do have SQL and OS level
access.
rboyd@.onlinemicrosoft.com (Rand Boyd [MSFT]) wrote in message news:<A1F7CV6gEHA.480@.cpms
ftngxa06.phx.gbl>...
> Can you make a connection from a DSN from the commercial webspace using
> aaa.bbb.ccc.ddd\servername_sql2k as the server name?
> Also can you connect via Query Analyzer form the commercial webspace using
> that servername?
> Rand
> This posting is provided "as is" with no warranties and confers no rights.

DSN-less connection to SQL Server with sql 7 and sql 2k

Hi,
I have commercial asp webspace hosting an application that connects to
an SQL machine on my local network. THe machine has both SQL 7 and SQL
2k running on it. Webspace and SQL are both running on Win2k.
The connection string I am using is as follows:
Application("Application_ConnectionString") = "Provider=sqloledb;" & _
"Network Library=DBMSSOCN;" & _
"Data Source=aaa.bbb.ccc.ddd,1433;" & _
"Initial Catalog=DBName;" & _
"User ID=xxx;" & _
"Password=yyy;"
It works fine when pointing to a database in the sql 7 instance. But
what I want to achieve is to connect to a database in the sql 2k
instance. Running locally, I can achieve this by substituting
aaa.bbb.ccc.ddd\servername_sql2k. But I cannot get it to connect to
the sql 2k instance from the commercial webspace. I get the following
message in this case:
Microsoft OLE DB Provider for SQL Server error '80004005'
[DBMSSOCN]General network error. Check your network documentation.
SO, my question is, how do you address a database in the sql 2k
instance via a dsn-less connection? Do you have to set SQL 7 and SQL
2k to listen on different ports? I have tried this but have not met
with success.
Any help gratefully received...
Thanks in advance
Can you make a connection from a DSN from the commercial webspace using
aaa.bbb.ccc.ddd\servername_sql2k as the server name?
Also can you connect via Query Analyzer form the commercial webspace using
that servername?
Rand
This posting is provided "as is" with no warranties and confers no rights.
|||Hi Rand
Thanks for your reply. Restricted access to the commercial web space
means that I DSN cannot be established, hence the DSN less connection.
For that reason I cannot try your suggestion of connecting Query
Analyser from that server. However, I have ascertained that both
instances of SQL Server can be reached from an external server by
connecting from a server where where I do have SQL and OS level
access.
rboyd@.onlinemicrosoft.com (Rand Boyd [MSFT]) wrote in message news:<A1F7CV6gEHA.480@.cpmsftngxa06.phx.gbl>...
> Can you make a connection from a DSN from the commercial webspace using
> aaa.bbb.ccc.ddd\servername_sql2k as the server name?
> Also can you connect via Query Analyzer form the commercial webspace using
> that servername?
> Rand
> This posting is provided "as is" with no warranties and confers no rights.