Problem in Converting Task to DataPumpTask2

Hai All,

I tried to convert a Task to DataPumpTask2.

But i got an Error

An unhandled exception of type 'System.InvalidCastException' occurred

QueryInterface for interface DTS.CustomTask failed

I installed SP3 for SqlServer in my Desktop and in the Server itself.

But i faced the same problem again.

If anyone have idea please enligten me .

Thanx,

Karthik.A

Hai All,

I found the Solution.

What we have to do is,Just install the SP3 for desktop SQL2KDeskSP3.exe in ur Machine.

While installation, it asks for "sa" Password.

Just open the setup.ini in the Extracted folder sql2ksp3/MSDE/setup.ini

and add PWD sa = <Password> below the [options].

then it starts installation.Once it's completed .

Compile ur code.it runs successfully.

Thanx,

Karthik.A

Problem getting security for configuration database

Below is what I have put into an execute sql task container. When I try to run it I have output open and have posted the output. The code runs fine when I have the configuration database set up to only check one server. However, when I add anymore to it, the package will get the security from the first db and then error out. Below is the error output and script. Any help with my problem is greatly appreciated.

**********Begin script*******************

declare @.dbname varchar(200)
declare @.mSql1 varchar(8000)

DECLARE DBName_Cursor CURSOR FOR
select name
from master.dbo.sysdatabases
where name not in ('mssecurity','tempdb')
Order by name

OPEN DBName_Cursor

FETCH NEXT FROM DBName_Cursor INTO @.dbname

WHILE @.@.FETCH_STATUS = 0
BEGIN
Set @.mSQL1 = ' Insert into [tempdb].[dbo].[DBROLES] ( DBName, UserName, db_owner, db_accessadmin,
db_securityadmin, db_ddladmin, db_datareader, db_datawriter,
db_denydatareader, db_denydatawriter )
SELECT '+''''+@.dbName +''''+ ' as DBName ,UserName, '+char(13)+ '
Max(CASE RoleName WHEN ''db_owner'' THEN ''Yes'' ELSE ''No'' END) AS db_owner,
Max(CASE RoleName WHEN ''db_accessadmin '' THEN ''Yes'' ELSE ''No'' END) AS db_accessadmin ,
Max(CASE RoleName WHEN ''db_securityadmin'' THEN ''Yes'' ELSE ''No'' END) AS db_securityadmin,
Max(CASE RoleName WHEN ''db_ddladmin'' THEN ''Yes'' ELSE ''No'' END) AS db_ddladmin,
Max(CASE RoleName WHEN ''db_datareader'' THEN ''Yes'' ELSE ''No'' END) AS db_datareader,
Max(CASE RoleName WHEN ''db_datawriter'' THEN ''Yes'' ELSE ''No'' END) AS db_datawriter,
Max(CASE RoleName WHEN ''db_denydatareader'' THEN ''Yes'' ELSE ''No'' END) AS db_denydatareader,
Max(CASE RoleName WHEN ''db_denydatawriter'' THEN ''Yes'' ELSE ''No'' END) AS db_denydatawriter
from (
select b.name as USERName, c.name as RoleName
from ' + @.dbName+'.dbo.sysmembers a '+char(13)+
' join '+ @.dbName+'.dbo.sysusers b '+char(13)+
' on a.memberuid = b.uid join '+@.dbName +'.dbo.sysusers c
on a.groupuid = c.uid )s
Group by USERName
order by UserName'

--Print @.mSql1
Execute (@.mSql1)

FETCH NEXT FROM DBName_Cursor INTO @.dbname
END

CLOSE DBName_Cursor
DEALLOCATE DBName_Cursor
Go

****************End Script***********************

****************Begin Error Output********************

[Execute SQL Task] Error: Executing the query "declare @.dbname varchar(200) declare @.mSql1 varchar(8000) DECLARE DBName_Cursor CURSOR FOR select name from master.dbo.sysdatabases where name not in ('mssecurity','tempdb') Order by name OPEN DBName_Cursor FETCH NEXT FROM DBName_Cursor INTO @.dbname WHILE @.@.FETCH_STATUS = 0 BEGIN Set @.mSQL1 = ' Insert into [tempdb].[dbo].[DBROLES] ( DBName, UserName, db_owner, db_accessadmin, db_securityadmin, db_ddladmin, db_datareader, db_datawriter, db_denydatareader, db_denydatawriter ) SELECT '+''''+@.dbName +''''+ ' as DBName ,UserName, '+char(13)+ ' Max(CASE RoleName WHEN ''db_owner'' THEN ''Yes'' ELSE ''No'' END) AS db_owner, Max(CASE RoleName WHEN ''db_accessadmin '' THEN ''Yes'' ELSE ''No'' END) AS db_accessadmin , Max(CASE RoleName WHEN ''db_securityadmin'' THEN ''Yes'' ELSE ''No'' END) AS db_securityadmin, Max(CASE RoleName WHEN ''db_ddladmin'' THEN ''Yes'' ELSE ''No'' END) AS db_ddladmin, Max(CASE RoleName WHEN ''db_datareader'' THEN ''Yes'' ELSE ''No'' END) AS db_datareader, Max(CASE RoleName WHEN ''db_datawriter'' THEN ''Yes'' ELSE ''No'' END) AS db_datawriter, Max(CASE RoleName WHEN ''db_denydatareader'' THEN ''Yes'' ELSE ''No'' END) AS db_denydatareader, Max(CASE RoleName WHEN ''db_denydatawriter'' THEN ''Yes'' ELSE ''No'' END) AS db_denydatawriter from ( select b.name as USERName, c.name as RoleName from ' + @.dbName+'.dbo.sysmembers a '+char(13)+ ' join '+ @.dbName+'.dbo.sysusers b '+char(13)+ ' on a.memberuid = b.uid join '+@.dbName +'.dbo.sysusers c on a.groupuid = c.uid )s Group by USERName order by UserName' --Print @.mSql1 Execute (@.mSql1) FETCH NEXT FROM DBName_Cursor INTO @.dbname END CLOSE DBName_Cursor DEALLOCATE DBName_Cursor " failed with the following error: "Line 15: Incorrect syntax near '-'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

**************End Error Output**************

Not knowing your database names, I'd suspect that one or more of them have names that need to be bracketed in a SQL statement. The error indicates that one might have a hyphen in the name? Try putting brackets around the @.dbname variable. Ex: "from [' + @.dbName+'].dbo.sysmembers"|||I don't get why the SSIS package would work for one database then. I will try what you suggest though. I am attempting to spearhead the SSIS learning in my department and learning has been pretty slow.|||

kschlap wrote:

I don't get why the SSIS package would work for one database then. I will try what you suggest though. I am attempting to spearhead the SSIS learning in my department and learning has been pretty slow.

What you have would work fine for a database named FooBar, but it would fail with the error you posted if the database were named Foo-Bar.
|||

I"m sorry, I made a mistake in my last post. I meant to say it worked for one server, but won't work for any other server.

-Kyle

|||

I think I have figured out one of the problems. I need to change the OLE DB Connection Manager. However, it won't let me do this and hit ok because it says that the table in my FROM clause is not in the database. Well of course its not on the database because the table gets built dynamically and the servers are gathered dynamically. How can I change this?

-Kyle

|||SSIS tries to validate the tasks when you are editing them; so I guess the table have to exists while you are editing the package. At run time though you can set DelayValidation to true; so the package does not fail validation.|||

The problem with this is that the connection I am trying to use is generated dynamically too. Thus, there are no servers until run time that I could add the table to to get around this error.

-Kyle

|||I don't get that. Does this mean you don't have a development environment where you can create oll required stuff? remebember this is just for validation purposes.|||

No, I have the proper development environment. I just get an error that says that the table doesn't exist whenever I try to make a change to the OLE DB connection. Here is the message I sent to the template creator yesterday.

I am having trouble with changing an OLE DB source. The package is set to not validate until run time, but currently it won’t let me change one of the settings. I keep getting an error that the table it is referring to is not valid. However, I know that it isn’t created because it gets created at run-time. Is there a way to change OLE DB sources? As always any help is appreciated.

|||

I fixed the problem. The package needed me to make an advanced edit instead of a normal one? Weird, but I don't care why it worked just because it worked. If anybody else is building a sql dba configuration database, mine is from a template by sqlmag, please PM me and we can work together on them.

-Kyle

Problem executing a Oracle Procedure using Execute Sql Task

Hi All,

I am trying to execute a Oracle Procedure through Execute Sql Task but it keeps throwing back an error ORA:00900. Is it possible to execute the procedure from this object? Do I need to use the script task to execute this. If yes then how?

Any help would be appreciated.

Thanks in advance.

Amol

hi, I did some stuff related that from a Sql Task but from Sql Server 2000 (dts), an Oracle function:

DECLARE

RetVal NUMBER;

begin

ACTUALIZAMARCAEMPLEADO;

end;

|||

Hi Amol,

Could you provide a bit more info:

1. What is the SQL statement you are using to execute the stored procedure?

2. What type of connection are you using? (ADO, OLE DB, ODBC)

Thanks,

~Matt

problem creating DTS package

Hi,
I am trying to create a DTS package wherein the data from the sql
server is transfered to a text file. I am using the Transform Data Task
to transfer the data.In Transform Data task properties, I build the
query and able to preview the data but when i click OK in the Transform
Data task properties screen I get the following error.
"To continue , define transformations. You can make changes to the
default transformations"
I am not able to define the transformations. Has anyone had this
problem and know a way to resolve it. I would appreciate if anyone
could help me.
Thanks
MBare you working on the server or throught the EMC remotely?|||are you working on the server or through the EMC remotely?|||Mohan wrote:
> Hi,
> I am trying to create a DTS package wherein the data from the sql
> server is transfered to a text file. I am using the Transform Data Task
> to transfer the data.In Transform Data task properties, I build the
> query and able to preview the data but when i click OK in the Transform
> Data task properties screen I get the following error.
> "To continue , define transformations. You can make changes to the
> default transformations"
> I am not able to define the transformations. Has anyone had this
> problem and know a way to resolve it. I would appreciate if anyone
> could help me.
> Thanks
> MB
>
You need to define the transformations first before you can click OK.
The text file must be defined with column names and then the
transformations can be assigned.|||I am actually using the remote connection to work directly on the
server.
i was able to get it to work if i selected some of the columns, but not
all. Is there a limit on the number of columns?
Let me explain in more detail.
When i am setting up the transform data task, I build the query and
then go to the destination tab. I click the Execute button to define
the columns for the destination.
The problem is that when i select all the columns from the table then
nothing happens when i click Execute. There are no columns defined and
when i click on the Define Columns button it gives an error and
terminates Enterprise Manager.
I am not sure whats going on. Really appreciate your help
Thanks
MB|||Mohan wrote:
> I am actually using the remote connection to work directly on the
> server.
> i was able to get it to work if i selected some of the columns, but not
> all. Is there a limit on the number of columns?
> Let me explain in more detail.
> When i am setting up the transform data task, I build the query and
> then go to the destination tab. I click the Execute button to define
> the columns for the destination.
> The problem is that when i select all the columns from the table then
> nothing happens when i click Execute. There are no columns defined and
> when i click on the Define Columns button it gives an error and
> terminates Enterprise Manager.
> I am not sure whats going on. Really appreciate your help
> Thanks
> MB
>
What service pack is SQL Server running on? That may be part of the
issue if you have a large amount of columns to export.|||Its running on SP3.|||Mohan wrote:
> *Its running on SP3. *
I figured out a fix without having to upgrade to SP4. If you don't
upgrade your client tools to SP3 on your computer you don't have the
errors. I had to uninstall my client tools and re-install the client
tools on my machine (not on the server). There were no issues. Its
not a pretty fix, but I can always use any new functionality on the
server itself. Good luck.
dmasuda
---
Posted via http://www.mcse.ms
---
View this thread: http://www.mcse.ms/message2000028.html