I hate this

I'm migrating a fu... dts to SSIS. That's fine. I've got a derived column task for stuff as cdbl(DTSSource("Col014") /100).

But how the source plain file have a lot of columns suddenly I find this:

Function Main()

Fecha=Right(DTSSource("Col014"),2) & "-" & Mid(DTSSource("Col014"),5,2) & "-" & left(DTSSource("Col014"),4)
If IsDate(Fecha) then
DTSDestination("FechaOp") =Fecha
Else
DTSDestination("FechaOp")=null
End IF
Main = DTSTransformStat_OK
End Function

So that 'Derived Column' is useless at all for to encompass this rule unless to anyone be able to tell me how to implement that conditional snippet inside...

I'll be to use a Script Component Task only for that dammed column when the rest ones already defined.

!!!!

Do y'know hare and tortoise tale? So here is the same but in reverse order. jezz|||

There is a conditional operator in the SSIS expression language. It will do what you need to do.

Here's the BOL topic on it: http://msdn2.microsoft.com/en-us/library/ms141680(SQL.90).aspx

-Jamie

|||Thanks Jamie. hurries doesn't provide us good advices..|||

OK.

I've taken as example this and then I've converted into this line:

'Fecha = Right(DTSSource("Col014"), 2) & "-" & Mid(DTSSource("Col014"), 5, 2) & "-" & Left (DTSSource("Col014"), 4)
'If IsDate(Fecha) Then
' = Fecha
'Else
'DTSDestination("FechaOp") = null
'End If

Isdate(Right([Column 13],2) & "/" & Mid([Column13],5,2) & "/" & Left([Column13],4)) ? [Column13] : Nothing

But fails:

Error at CargaModelo187 [Derived Column [10345]]: Attempt to find the input column named "Column13" failed with error code 0xC0010009. The input column specified was not found in the input column collection.

Error at CargaModelo187 [Derived Column [10345]]: Attempt to parse the expression "Isdate(Right([Column 13],2) & "/" & Mid([Column13],5,2) & "/" & Left([Column13],4)) ? [Column13] : Nothing" failed and returned error code 0xC00470A2. The expression cannot be parsed. It might contain invalid elements or it might not be well-formed. There may also be an out-of-memory error.

Error at CargaModelo187 [Derived Column [10345]]: Cannot parse the expression "Isdate(Right([Column 13],2) & "/" & Mid([Column13],5,2) & "/" & Left([Column13],4)) ? [Column13] : Nothing". The expression was not valid, or there is an out-of-memory error.

Error at CargaModelo187 [Derived Column [10345]]: The expression "Isdate(Right([Column 13],2) & "/" & M

id([Column13],5,2) & "/" & Left([Column13],4)) ? [Column13] : Nothing" on "input column "Column 13" (11693)" is not valid.

Error at CargaModelo187 [Derived Column [10345]]: Failed to set property "Expression" on "input column "Column 13" (11693)".

Let me know which is the next step or what guidelines must I follow on this issue.

|||

In your expression you refer to "Column13" but the SSIS error message shows that the column in the data flow is in fact named "Column 13"

Donald

|||

A couple other things:

The expression language does not have an IsDate() function. What exactly are you testing for with that? Itstill might be possible, depending what you are looking for...

There is also Left() function -- you should use Substring() for that.

Instead of "Nothing", you need to use the NULL() functions, which are strictly typed.

|||

Mark Durley wrote:

There is also Left() function -- you should use Substring() for that.

Another alternative to the lack of a LEFT() function is RIGHT(REVERSE())

-Jamie

|||Thanks a lot to all of you|||

Well, this follow failing:

Right([Column 13],2) & "/" & SubString([Column 13],5,2) & "/" & SubString([Column 13],1,4) ? [Column 13] : Nothing

Column 13 on the first row own this value:

20050214

TITLE: Microsoft Visual Studio

Error at CargaModelo187 [Derived Column [10345]]: Attempt to find the input column named "Nothing" failed with error code 0xC0010009. The input column specified was not found in the input column collection.

Error at CargaModelo187 [Derived Column [10345]]: Attempt to parse the expression "Right([Column 13],2) & "/" & SubString([Column 13],5,2) & "/" & SubString([Column 13],1,4) ? [Column 13] : Nothing" failed and returned error code 0xC00470A2. The expression cannot be parsed. It might contain invalid elements or it might not be well-formed. There may also be an out-of-memory error.

Error at CargaModelo187 [Derived Column [10345]]: Cannot parse the expression "Right([Column 13],2) & "/" & SubString([Column 13],5,2) & "/" & SubString([Column 13],1,4) ? [Column 13] : Nothing". The expression was not valid, or there is an out-of-memory error.

Error at CargaModelo187 [Derived Column [10345]]: The expression "Right([Column 13],2) & "/" & SubString([Column 13],5,2) & "/" & SubString([Column 13],1,4) ? [Column 13] : Nothing" on "input column "Column 13" (12091)" is not valid.

Error at CargaModelo187 [Derived Column [10345]]: Failed to set property "Expression" on "input column "Column 13" (12091)".

ADDITIONAL INFORMATION:

Exception from HRESULT: 0xC0204006 (Microsoft.SqlServer.DTSPipelineWrap)

BUTTONS:

OK

|||

I was wrong although this new line-version fails too...

[Derived Column 1] == Right([Column 13],2) & "/" & SubString([Column 13],5,2) & "/" & SubString([Column 13],1,4)

Thanks in advance,

|||

It's already solved.

Instead of & you must use + and drop '[Derived Column 1] =='

RIGHT([Column 13],2) + "/" + SUBSTRING([Column 13],5,2) + "/" + SUBSTRING([Column 13],1,4)

IsDate function is not necessary because of I saw concretly in that file that all of them have the same structure yyyymmdd

|||In any case what function is intented do the same that ISDATE?|||

There is no function that can do IsDate. As Mark suggested there may be possibilities using other functions - checking that a string has certain characters and that substrings, can be cast to valid integers - but that expression would likely be rather complex.

Script component does all this really nicely, of course.

Donald

I hate the maintenance CleanUp task :=)

I have a backup database task that runs at 4 am.

I added a maintenance CleanUp task pointing to the right folder
I checked the include first-level subfolder box, and delete files based on the age of the file at task run time

I specified "delete files older than " 1 day

But the cleanup task is not working, I have to manually go a delete the files myself.

Am I missing something? should I run the maintenance task first?
and then the backup task?

make sure that your system is upgraded to SP2 and also you have installed SSIS.

Refer these links for more info

http://support.microsoft.com/default.aspx/kb/933508

http://blogs.msdn.com/sqlrem/

Madhu

|||Also you make sure to apply SP2 for the client tools as they also willbe updated with bug fixes.|||

I'm having this problem, too. I have a Maintenance Cleanup Task to remove old maintenance plan text reports that seems to "refuse" to delete the old files. The system I'm trying this on has had the GDR 3054 update applied to all components, including the clients. I've even recreated the entire maintenance plan new, and it still doesn't work. The interesting thing is that my Cleanup Task that removes old backup files is working correctly!

|||

This has been confirmed as a bug

See http://support.microsoft.com/kb/936305

Maybe by SQL Server 2008 SP2 the Maintenace Plans may have got back to being usable and reliable

Regards

Nadreck

|||

This is not a solution or an answer to what Armando is asking.

What he says (and what Nadreck and I am experiencing) is the complete refusal of the maintenance cleanup task to delete the files...at *any* time.

Your links, as well as the other replies to this topic refer to a bug where existing maintenance tasks, created before SP2 being applied, are mis-timed after SP2 is applied. In my situation, the maintenance plan was created after SP2 was applied.

By not being able to automate the cleanup of the older backup files, it makes the entire backup task (as a scheduled process) nearly worthless.

Jeff

|||

Ok, after doing a lot of searching around the net I found out a workaround (sort of).

To get the maintenance plan to actually delete files in the first level subfolders, change the extension for it to delete (in the maintenance cleanup task dialoge) to *.*

I know this isn't ideal if you store files other than your backups in some of these folders (or rename old backups a different extension to always keep them around), but it did work for me. You just need to be careful what is put in those folders.

Jeff

I hate the maintenance CleanUp task :=)

I have a backup database task that runs at 4 am.

I added a maintenance CleanUp task pointing to the right folder
I checked the include first-level subfolder box, and delete files based on the age of the file at task run time

I specified "delete files older than " 1 day

But the cleanup task is not working, I have to manually go a delete the files myself.

Am I missing something? should I run the maintenance task first?
and then the backup task?

make sure that your system is upgraded to SP2 and also you have installed SSIS.

Refer these links for more info

http://support.microsoft.com/default.aspx/kb/933508

http://blogs.msdn.com/sqlrem/

Madhu

|||Also you make sure to apply SP2 for the client tools as they also willbe updated with bug fixes.|||

I'm having this problem, too. I have a Maintenance Cleanup Task to remove old maintenance plan text reports that seems to "refuse" to delete the old files. The system I'm trying this on has had the GDR 3054 update applied to all components, including the clients. I've even recreated the entire maintenance plan new, and it still doesn't work. The interesting thing is that my Cleanup Task that removes old backup files is working correctly!

|||

This has been confirmed as a bug

See http://support.microsoft.com/kb/936305

Maybe by SQL Server 2008 SP2 the Maintenace Plans may have got back to being usable and reliable

Regards

Nadreck

|||

This is not a solution or an answer to what Armando is asking.

What he says (and what Nadreck and I am experiencing) is the complete refusal of the maintenance cleanup task to delete the files...at *any* time.

Your links, as well as the other replies to this topic refer to a bug where existing maintenance tasks, created before SP2 being applied, are mis-timed after SP2 is applied. In my situation, the maintenance plan was created after SP2 was applied.

By not being able to automate the cleanup of the older backup files, it makes the entire backup task (as a scheduled process) nearly worthless.

Jeff

|||

Ok, after doing a lot of searching around the net I found out a workaround (sort of).

To get the maintenance plan to actually delete files in the first level subfolders, change the extension for it to delete (in the maintenance cleanup task dialoge) to *.*

I know this isn't ideal if you store files other than your backups in some of these folders (or rename old backups a different extension to always keep them around), but it did work for me. You just need to be careful what is put in those folders.

Jeff

sql

I get this error using Sql Server Destination

Does anyone have any idea?

I was using OLE DB DESTINATION and after going to Sql Server Destination task:

[SQL Server Destination [11259]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Could not bulk load because SSIS file mapping object 'Global\DTSQLIMPORT ' could not be opened. Operating system error code 2(The system cannot find the file specified.). Make sure you are accessing a local server via Windows security.".

?

TIA

What happens when you try with an OLE DB Destination?

I never use SQL Server destinations so I'm not sure if the file not found error is your fault or something with your installation.

Can you explain a bit more of your data flow?

|||The SQL Server Destination requires that the SQL Server is local (on the same computer) as the executing package. Is that the case in your scenario?|||

Did you try doing a search? : http://search.live.com/results.aspx?FORM=QBJK&q1=macro%3Ajamiet.ssis&q=Global%5CDTSQLIMPORT

You cannot use SQL Server Destination if you are inserting to a server that is not the same as the one on which you are running the package.

-Jamie

I don't understand it

Dear all,
I'm executing a DTS which own a sql server task that launch a stored
procedure.
This stored procedure run a WHILE sentence and then launch a bcp command
dinamically.
Well, everything works debugging that stored procedure or simply launch it
throught Query Analyzer with its parameters.
Problem come up when I launch the DTS. That sql server task works but only
launch one time the BCP command.
I know that is very difficult reach to see the exit but...
There goes an excerpt:
set @.cont = 1
Set @.iCont = (select count(*) from tblDownloadKF_Bridge_Info)
WHILE @.cont < @.iCont + 1
begin
set @.table = (select name from tblDownloadKF_Bridge_Info where ID = @.cont)
SET @.bcpCommand= 'bcp ' + @.db + '' + @.table + ' out ' + @.strPath +
Ltrim(RTrim(@.table)) + '.dat -c -S ' + @.host + ' -U ' + @.strUser +' -P '
+
@.strPassword + ''
EXEC @.result = master.dbo.xp_cmdshell @.bcpCommand
if (@.result = 0)
set @.status = 'done'
else
set @.status = 'error'
update tblDownloadKF_Bridge_Info
set Remarks = Ltrim(Rtrim(@.status)),
dateoperation = getdate()
where ID = @.cont
set @.table = ''
set @.cont = @.cont + 1
end
GO
Is there any restriction on that command via DTS'
Thanks so much,Hi Enric,
Your problem is not the BCP command but xp_cmdshell stored procedure.
"INF: How to Run a DTS Package as a Scheduled Job"
http://support.microsoft.com/defaul...kb;en-us;269074
Cristian Lefter, SQL Server MVP
"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:0C0CB603-97B8-40B0-9255-D6E3E3F245B3@.microsoft.com...
> Dear all,
> I'm executing a DTS which own a sql server task that launch a stored
> procedure.
> This stored procedure run a WHILE sentence and then launch a bcp command
> dinamically.
> Well, everything works debugging that stored procedure or simply launch it
> throught Query Analyzer with its parameters.
> Problem come up when I launch the DTS. That sql server task works but only
> launch one time the BCP command.
> I know that is very difficult reach to see the exit but...
> There goes an excerpt:
> set @.cont = 1
> Set @.iCont = (select count(*) from tblDownloadKF_Bridge_Info)
> WHILE @.cont < @.iCont + 1
> begin
> set @.table = (select name from tblDownloadKF_Bridge_Info where ID = @.cont)
>
> SET @.bcpCommand= 'bcp ' + @.db + '' + @.table + ' out ' + @.strPath +
> Ltrim(RTrim(@.table)) + '.dat -c -S ' + @.host + ' -U ' + @.strUser +' -P
> ' +
> @.strPassword + ''
> EXEC @.result = master.dbo.xp_cmdshell @.bcpCommand
> if (@.result = 0)
> set @.status = 'done'
> else
> set @.status = 'error'
> update tblDownloadKF_Bridge_Info
> set Remarks = Ltrim(Rtrim(@.status)),
> dateoperation = getdate()
> where ID = @.cont
> set @.table = ''
> set @.cont = @.cont + 1
> end
> GO
>
> Is there any restriction on that command via DTS'
> Thanks so much,

I am looking for a task which..

Dear all,
A couple of days ago I posted a request but nobody answered it.
I need a task which reports me all the NT users and groups in every
server/database and of course, their permissions.
The following excerpt is a part of the work:
declare @.sql varchar(300)
declare @.USER as char(25)
declare cursor1 cursor fast_forward for
select name from sysusers nolock where isntuser = 1 or isntgroup = 1
open cursor1
fetch next from cursor1 into @.USER
while @.@.fetch_status = 0
BEGIN
set @.sql = ('sp_helprotect NULL, ''' + lTRIM(rTRIM(@.USER)) + '''')
exec (@.sql)
fetch next from cursor1 into @.USER
end
close cursor1
deallocate cursor1
Thanks a lot and regards,Finding a list of server instances is something I haven't had to do before,
so I can't help you there.
sysdatabases lists the databases in an instance.
sysobjects lists the database objects, including tables (U), views (V),
procedures (P), and functions (FN, TF, IF)
sysprotects contains information about permissions, take a look a the
definition of the INFORMATION_SCHEMA.TABLE_PRIVILEGES.
I think syspermissions also contains permissions information.
Good luck! I would purchase a security audit tool instead. That way you
limit your liability and shift the focus of the leeches--oops, I meant
lawyers, to someone with deeper pockets.
It might be easier to generate a database creation script (script all
objects) for each database and parse the output.
"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:7CDE748E-8426-4250-A52D-57DA7798F73E@.microsoft.com...
> Dear all,
> A couple of days ago I posted a request but nobody answered it.
> I need a task which reports me all the NT users and groups in every
> server/database and of course, their permissions.
> The following excerpt is a part of the work:
> declare @.sql varchar(300)
> declare @.USER as char(25)
> declare cursor1 cursor fast_forward for
> select name from sysusers nolock where isntuser = 1 or isntgroup = 1
> open cursor1
> fetch next from cursor1 into @.USER
> while @.@.fetch_status = 0
> BEGIN
> set @.sql = ('sp_helprotect NULL, ''' + lTRIM(rTRIM(@.USER)) + '''')
> exec (@.sql)
> fetch next from cursor1 into @.USER
> end
> close cursor1
> deallocate cursor1
>
> Thanks a lot and regards,

Hyper Threadng with SQL 2000 ?

Is it better to enable Hyper Threading on a DB machine. I am running only SQL 2000 on my machine no other service is running. I have noticed in Task Manager that in peak time when there is some load on DB one Proccesor proccesing goes to 100 % while other reamin idle or maximum go to 20 to 30 %. I want whole peoccesing for database only.

If some one has any tips for improving database performance then please share it.mmmmmmmmm

Heres a good link to HyperThreading if anyones interested

http://www.2cpu.com/articles/42_2.html

But Beware as YOUR SERVER HARDWARE MUST SUPPORT THIS NEW Technology

and as Intel say

Performance will vary depending on the specific hardware and software you use. but could provide a performance increase in the neighborhood of 10-30%, depending on the application and the situation

If your really interested you could set up 2 test machines and try it but IMHO... I think your barking up the wrong tree as far as Twin/Quad CPU Usage balancing is concerned.

As far as tips for improving database performance try

Higher Spec Hardware & more RAM,
Transaction file physically stored on a seperate Hard Disk away from the RAID Array of the Data File,
Tune Indexes & tweak sloppy Querys etc...etc...etc...etc...etc...etc...etc...etc. ..

GW|||The most important thing I've read is to limit the max degreee of parallelism to the number of physical processors when using HT.