Derived column in SQL Server 2005 SSIS

Hi,

I am trying to upload a csv file into a destination SQL Server table using Data FLow objects in MS SQL Server 2005 SSIS. My destination table X has a date column while my source data file (for which i have a flat file connection) does not have a date column. I created a Derived column for date using the system date function in my Source data object and ran the package but it returned errors. I changed the data type of the derived column but I still get errors.

is there any other way i can get the date on the fly i.e generate a date for the incoming source file and map that (insert into) to the destination table ?

Thanks.

Post your errors here and we can then better help you. Also ensure that you have the correct date types flowing into the given date column.|||

Devived column name - Name for your date field

Devived column - should be <add as new column>

The expression columns should be GETDATE()

On the advanced Editor - Make sure to select the date name to be used in the output columns.

this should work for you.

Hammer.

Derived Column from a Condition statement

I've found that there is no such thing as a Case Statement in the Derived Column task in the data flow objects. I've written a ternary statement instead. I can't seem to get it to work exactly how I want it to. For example

AccountCategory == "E" ? 1 : 2

Works fine but the following doesn't

AccountCategory == "E" ? CreditAmount : DebitAmount

The CreditAmount and DebitAmount Fields are spelled correctly, the field type of AccountCategory is String and the Field type of CreditAmount and DebitAmount is numeric, but that seems to be the same situation that I have in the first example that works. Am I missing something? I'd appreciate any advice anyone has to offer. Thanks,

Bill

Only thing you did not mention is what is the data type set for the derived column itself, it should be numeric to match the Credit and Debit columns. The scale and precison must match for all three too.

That is all just guess work, and the syntax looks fine, but what would really help is the actual error message you get.

|||It looks like I stumbled on to how to fix this, I don't know if it is a bug or it is designed to work this way. If I copied the second statement and then deleted the whole row for that particular derived column then copied the statement back into the expression column before naming the column it works. Go figure.|||

Glad to hear you have it working.

Most likely, as Darren suggests, it was a datatype mismatch. However, if you could repro and report the error message, we could give you better advice.

Donald

deploying CLR objects best practice

Hi,
When I want to deploy CLR assemblies on a SL Server Production Server. What
is the best pratice to define where to store theses assemblies?
In the case of deploying with VS 2005, where the tool put the assemblies on
the server?
Thanks in adavance,
LaurentAssemblies are stored in databases, no matter if you deploy them using
CREATE ASSEMBLY command of VS.NET IDE deployment. Therefore, store the
assembly in the database where you need your CLR objects.
Dejan Sarka, SQL Server MVP
Mentor
www.SolidQualityLearning.com
"laurent banon" <laurent.banon@.soorce.com> wrote in message
news:uNPGDOl%23FHA.2812@.TK2MSFTNGP09.phx.gbl...
> Hi,
> When I want to deploy CLR assemblies on a SL Server Production Server.
> What is the best pratice to define where to store theses assemblies?
> In the case of deploying with VS 2005, where the tool put the assemblies
> on the server?
> Thanks in adavance,
> Laurent
>

Dependent Objects

I am trying to find all the objects - SP, triggers, Views that depend on
some Table.
I was trying to use sysdepends table but it seems to me that it doesn't
return all the dependent objects. It misses obvious Update and Insert SP for
this table.
Is there a reliable way to find dependent objects.
thank you
ShimonThere is no reliable way to find dependent objects because of delayed name
resolution. However, you can try to parse syscomments :)
"Shimon Sim" wrote:

> I am trying to find all the objects - SP, triggers, Views that depend on
> some Table.
> I was trying to use sysdepends table but it seems to me that it doesn't
> return all the dependent objects. It misses obvious Update and Insert SP f
or
> this table.
> Is there a reliable way to find dependent objects.
> thank you
> Shimon
>
>

deny permissions issues

Hi,
i'd like to deny permissions on system objects to the public role in a
database.
I have tried using the following commands without any success:
DENY SELECT ON SCHEMA::sys TO public
or
DENY SELECT ON sys.sysobjects TO public
Can someone tell me what is wrong in my commands?
Thx,
ChrisHello Chris
I am not sure what is the reason to revoke SELECT on objects in sys schema
because resultsets will be filtered already by permissions. Anyway here is
statement you can use:
REVOKE SELECT ON sys.sysobjects FROM public
Martin Rakhmanov
<christophe.leroquais@.gmail.com> wrote in message
news:1149148183.181843.75560@.h76g2000cwa.googlegroups.com...
> Hi,
> i'd like to deny permissions on system objects to the public role in a
> database.
> I have tried using the following commands without any success:
> DENY SELECT ON SCHEMA::sys TO public
> or
> DENY SELECT ON sys.sysobjects TO public
>
> Can someone tell me what is wrong in my commands?
> Thx,
> Chris
>