how to control data types

i have sp that insert fileName and fileType

how can i forbidden in the sp other types then word doc and images?

Code Snippet

if not exists(select Number

from dbo.Freezrelease

where FileName1 like @.FileName1 and

FileType like @.FileType and

FileSize = @.FileSize)

/*{*/ begin

if @.FileSize<500

/*{*/ begin

insert into dbo.Freezrelease(FileName1,FileType,FileSize)

values(@.FileName1,@.FileType,@.FileSize)

select @.@.identity

/*}*/ end

else

select -1

/*}*/end

else

select 0

So you are just inserting the file names, not the bits? If so, just add a line:

if @.fileType not in ('doc','jpg','...etc')

begin

raiserror ('Filetype must be doc, jpg, or etc')

return -100

end

And the procedure will stop there after raising an error

|||

You could add a CHECK constraint to the table that limits the values in the FileName field to only values ending with '.doc', '.docx'*, or '.jpg'. (And of course, any other image file extensions required...)

Using this method, it would not be necessary to 'control' every stored procedure or application query. Programmers and users will not be able to thwart your determination that a 'proper' FileName be supplied. The CHECK constraint would 'police' the inserts/updates for you.

* .docx for Word 2007

Code Snippet

CREATE TABLE #MyTable
( RowID int IDENTITY,
FileName varchar(50)
CHECK ( FileName LIKE '%.doc%' OR FileName LIKE '%.jpg' )
)
GO

-- These succeed
INSERT INTO #MyTable VALUES ( 'MyFile.doc' )
INSERT INTO #MyTable VALUES ( 'MyPic.jpg' )
INSERT INTO #MyTable VALUES ( 'MyFile.docx' )

-- This fails
INSERT INTO #MyTable VALUES ( 'Myfile.txt' )

-- This fails
INSERT INTO #MyTable VALUES ( 'Myfile' )

SELECT *
FROM #MyTable

RowID FileName
-- --
1 MyFile.doc
2 MyPic.jpg
3 MyFile.docx

DROP TABLE #MyTable

|||

What Arnie says is true too, though I would prefer having a fileType column to hold the extension also, for all of the basic normalization reasons:

1. You can add information about types of files easily and join to it without the substring

2. Listing files by type will not require a substring

3. Searching for files by type can be indexed

Really it is all about the need to avoid dealing with parts of a column value.

how to concatenate variables for RAW output filename i.e.@path + @filename?

Doesn't appear you can do this.

Am I wrong?

Please tell me I am.you can of course use a third variable (FullFileName), set the property evaluateasexpression to true and set the expression to [User:ath] + [User::filename], then use that

[User::FullFileName] as the variable for the output file name...

|||If you right click on the Raw File Destination and select properties, in the Custom Properties section, change the AccessMode to "File name from variable."

Then, build a third variable that uses expressions to concatenate @.path & @.filename. Then use that variable in the Raw File Destination's FileNameVariable property.|||I can't get this working

@.var1 = c:\temp
@.var2 = output.raw
@.var3 = @.var1 + @.var2 [evaluate as expression set to true]

it won't let me select the raw output filenameVariable to be @.var3 in the raw editor

it says "error at.. the file name property is not valid. the filename is a device or contains invalid characters"

I have also tried @.var1 = c:\\temp

no dice

(and yes, c:\temp does exist)|||

Make sure you set the Value of @.var3 to be a valid fil path. The real value is used for validation purposes. The expression result is only really used at runtime. Any help?

|||The file will not exist until runtime, it is a raw file that gets created.

The file path "C:\temp\" is valid

Bizarrely, if I hardcode the value of c:\temp\filename.raw

It works. (not evaluating the var)

It doesn't seem to like evaluating the concatenation of the the vars.|||

adolf garlic wrote:

I can't get this working

@.var1 = c:\temp
@.var2 = output.raw
@.var3 = @.var1 + @.var2 [evaluate as expression set to true]

it won't let me select the raw output filenameVariable to be @.var3 in the raw editor

it says "error at.. the file name property is not valid. the filename is a device or contains invalid characters"

I have also tried @.var1 = c:\\temp

no dice

(and yes, c:\temp does exist)

@.var1 should be c:\temp\, not c:\temp. Either that, or in your concatenation you must contain the slash there.

You did tell the Raw File adapter to use a filename from a variable, correct?

|||It is C:\temp\

The RFA has access mode set to "File name from variable" which then allows me to select the variable from the drop down by "fileNameVariable"

I notice from another example on the web that it should be evaluating the expression and displaying the result in the 'value' property.

This is not happening for me.

a-ha!

The reason it is not working is because I was putting the @...+@... in the 'value' in the variables window.

I have discovered in the properties window for the variables window (shurely a window on a window too far, yesh?) a field called 'expression' in which I have to put the @.+@. bit.

This doesn't make sense.

Either have a flag called "evaluate as expression" OR have an "expression" property.

What is the point in having both?|||

At first, this seemed counter intuitive to me as well.. but, if you think about it a bit more it makes sense. The expression property will open up the window where you define the expression to evaluate. When you evaluate the expression it's result will go into the value field (try clicking the evaluate expression, it populates the other field). The two are properties are logically not the same and therefore should be seperate.

Are you having any problems with this now? It sounds like you have gotten it working again...

|||Yes, thankyou it is working!

My point is: if there is a value in the 'expression' property, it must be able to detect that it should evaluate the expression.

The reporting services model is much more intuitive. For starters, anything can have an expression which is based on vb code.

So if this was in RS, under the Raw.filename property, you would click on "..." to go straight to the expressions editor where you could pick from @.var1 etc.

Why have "expression","value","evaluate as expression" when you can just have value. (make everything an expression just like RS)

This is another example of something that is not intuitive and overly complex. I have once again wasted a lot of my and other people's time with something in SSIS.

how to concatenate variables for RAW output filename i.e.@path + @filename?

Doesn't appear you can do this.

Am I wrong?

Please tell me I am.you can of course use a third variable (FullFileName), set the property evaluateasexpression to true and set the expression to [User:ath] + [User::filename], then use that

[User::FullFileName] as the variable for the output file name...

|||If you right click on the Raw File Destination and select properties, in the Custom Properties section, change the AccessMode to "File name from variable."

Then, build a third variable that uses expressions to concatenate @.path & @.filename. Then use that variable in the Raw File Destination's FileNameVariable property.|||I can't get this working

@.var1 = c:\temp
@.var2 = output.raw
@.var3 = @.var1 + @.var2 [evaluate as expression set to true]

it won't let me select the raw output filenameVariable to be @.var3 in the raw editor

it says "error at.. the file name property is not valid. the filename is a device or contains invalid characters"

I have also tried @.var1 = c:\\temp

no dice

(and yes, c:\temp does exist)|||

Make sure you set the Value of @.var3 to be a valid fil path. The real value is used for validation purposes. The expression result is only really used at runtime. Any help?

|||The file will not exist until runtime, it is a raw file that gets created.

The file path "C:\temp\" is valid

Bizarrely, if I hardcode the value of c:\temp\filename.raw

It works. (not evaluating the var)

It doesn't seem to like evaluating the concatenation of the the vars.|||

adolf garlic wrote:

I can't get this working

@.var1 = c:\temp
@.var2 = output.raw
@.var3 = @.var1 + @.var2 [evaluate as expression set to true]

it won't let me select the raw output filenameVariable to be @.var3 in the raw editor

it says "error at.. the file name property is not valid. the filename is a device or contains invalid characters"

I have also tried @.var1 = c:\\temp

no dice

(and yes, c:\temp does exist)

@.var1 should be c:\temp\, not c:\temp. Either that, or in your concatenation you must contain the slash there.

You did tell the Raw File adapter to use a filename from a variable, correct?

|||It is C:\temp\

The RFA has access mode set to "File name from variable" which then allows me to select the variable from the drop down by "fileNameVariable"

I notice from another example on the web that it should be evaluating the expression and displaying the result in the 'value' property.

This is not happening for me.

a-ha!

The reason it is not working is because I was putting the @...+@... in the 'value' in the variables window.

I have discovered in the properties window for the variables window (shurely a window on a window too far, yesh?) a field called 'expression' in which I have to put the @.+@. bit.

This doesn't make sense.

Either have a flag called "evaluate as expression" OR have an "expression" property.

What is the point in having both?|||

At first, this seemed counter intuitive to me as well.. but, if you think about it a bit more it makes sense. The expression property will open up the window where you define the expression to evaluate. When you evaluate the expression it's result will go into the value field (try clicking the evaluate expression, it populates the other field). The two are properties are logically not the same and therefore should be seperate.

Are you having any problems with this now? It sounds like you have gotten it working again...

|||Yes, thankyou it is working!

My point is: if there is a value in the 'expression' property, it must be able to detect that it should evaluate the expression.

The reporting services model is much more intuitive. For starters, anything can have an expression which is based on vb code.

So if this was in RS, under the Raw.filename property, you would click on "..." to go straight to the expressions editor where you could pick from @.var1 etc.

Why have "expression","value","evaluate as expression" when you can just have value. (make everything an expression just like RS)

This is another example of something that is not intuitive and overly complex. I have once again wasted a lot of my and other people's time with something in SSIS.