Raw Partitions in SQL Server 2000

Hi,

I have a 3gb database (SQL 2k on Windows 2k) with some performance issues and it has been suggested to me to use Raw Partitions to increase performance. After researching this on BOL, I am hesitant to use raw partitions. I know this was a common practice in SQL 6.5. Has anyone tried this? What is the performance increase? Are there any other negatives besides what is mentioned in BOL? Any other insight would be greatly appreciated?

Thanks

MichaelMichael, I have used raw partitiones back with Ver 6.0 however controler technology has greatly improved since then. You need to read the little blurb in Books Online before you go down this path. I seriously doubt you will get enough of a performace boost to warrent the limitations raw partitions impose.

I would look instead to the drive configuration, channel allocation, transfer speed of the SCSI card, distribution of files across the drives etc.

Raw files - create once

Hi,

I try to add multiple files to a raw files. I use a loop for it and set the write option to "create once", so that the file should be created when the package is started and files are appended as they flow to the destination... However I always get an error when I try to add the second file that the raw file already exists... Well, I expect that it exists but I don't expect this error because that's not the intended behaviour!

Is there anything I also have to do to use the raw file as it's described in BOL?

Thanks,

All answered here Thomas: http://blogs.conchango.com/jamiethomson/archive/2005/12/01/2443.aspx

-Jamie

|||

Jamie,

Google is always as good as the questions you ask it... ;-)

Meanwhile I came around with another solution... I just created a "template" file which I copy over the existing file before the data flow is started. Not perfect, but it works... So I always work with "append" to get around this problem...

Thanks... (BTW: Do you come to PASS Europe next week?)

|||

Hi Thomas,

Yeah, I didn't like that approach because it meant you had to deploy a raw file.

In the end we had to do it anyway though cos we have a datareader destination in the same data flow and there is a bug that means the datareader destination will not work if the data flow had DelayValidation=TRUE.

Unfortunately I can't make it to PASS. I am working on the same project as Darren at the moment and its required that there's always one of us around. So he is going to PASS and I'm not :-(

-Jamie

Raw File Source issue

I have a single file that contains records destined for multiple tables. The "first" record is considered primary and the other records are considered "secondary" (meaning that they have foreign keys to the primary table).

In order to properly insert this I needed to use two data flows. The first data flow directed the primary rows to the primary table and the secondary rows get directed to a raw file destination. The second data flow read in from the raw file and wrote out the rows to the appropriate tables.

But here is my problem.

This darn validation! While I think validation is a great idea, the extensive use of it in what seems like EVERY aspect of SSIS seems to cause more headaches than not...

When I deploy my package and try to run it I get an error because the raw file source DOES NOT EXIST. Of course it does not exist, it gets created when the package runs... I cannot deploy something that does not exist yet.

I even have a problem while I am trying to work with the package in VS. The only way to get the package to run is to disable the second data flow so it does not try to validate it. Run the package so the raw file is created. And then re-enable the second data flow again. (Which then I guess I could take the raw file and deploy it with my package but that just seems silly.... deploying temporary files... that would be like deploying Internet Explorer with the Temporary Internet Files folders....)

And of course with that type of solution my package could never "clean up" after itself...

Try setting DelayValidation to TRUE for all source / destination components

Thanks,
Sankaranarayanan MG

|||I have used DelayValidation on other objects but I do not see any property of that sort when looking at the Raw File Source. The only thing I see with the word valid is ValidateExternalMetadata. Should I be looking elsewhere?|||

Yes, DelayValidation is a task property not a component property so you would need to set it on the DataFlow task that contains the component you need to have validation delayed on. Note that this delays the validation for all the components in the task not just the one component you need it for.

HTH,

Matt

raw file source avoid bad data?

I want to have a data flow like this:

1. raw file source
2. validate data? or conditional split?
3. sql server destination

there is some bad data in my raw file. by bad data I mean datetimes that are out of the range that sql server can handle.

this should be simple right?What created the raw file and where did the data within it originally come from?|||a different package that does a raw extract from an oracle rear end.|||Well then the fact that its in a raw file is irrelevant. Your issue is the conversion between Oracle data types and SQL data types.

What columns does it complain about?
What is the datatype of those columns in your SSIS package?
What is the the datatype of the columns in the Oracle source?
What is the the datatype of the columns that you are trying to populate in the SQL Server destination?
What error messages do you get?

You need to provide more information than simply "by bad data I mean datetimes that are out of the range that sql server can handle."

-Jamie

|||[SQL Server Destination [31]] Error: An OLE DB error has occurred. Error code: 0x80040E07. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E07 Description: "Error converting data type DBTYPE_DBTIMESTAMP to datetime.".

they are datatime on both sides.

I was hoping there was something in the conditional split thingie but there is no ISDATE() function.

that is really all I need. I just want to skip the rows with bad dates. I can't fix the data it is just bad and there is nothing I can do about it.

it's like a 1,000 rows in millions is bad so it's not a big deal. seriously it's not a big deal.

I just want to avoid importing those rows with bad data. or better yet scrubbing the bad values to nulls.|||Seems like if you know what your bad dates look like (or you know what a good date looks like, which you should since BOL states what is valid for different types in SQL Server) then you could use a conditional split or a script component to filter the dates out. Obviously for the conditional split, it wouldn't be as simple as if there was an ISDATE method but it could be done with DATEPART comparisons against the know bad (or good) values.

Matt|||

datepart would ***-u-me a valid date be passed?

I will have to investigate the script option.

The implementation of the conditional split transformation seems pretty weak. Why not provide access to the entire managed runtime in the condition part?

Expressions seem like an afterthought. I would expect a lot more.

I'm just missing something here this should be easy and I'm making it way to hard.

|||Hi,
I'm puzzled by your sceptism of the expression language. Does it not do what you need?

Access to the entire managed runtime (I presume you mean the dotnet framework) is provided through the script component (which gives you regular expressions et al). As such, is there really a need to do that through the Derived Column Component as well?

Interested to hear your thoughts.

-Jamie|||

I'm reading the help on expressions. It does not seem like it will do what I need; and forcing me to use vb gives me facial ticks. I just don't like vb. I know a lot of people live and die by it but it's not for me. I’ll use it if I have to but I avoid it if I can.

why not make expressions work like they do in reporting services? you can do almost anything. In-line without writing a custom script thingies (even though it's vb based)

why invent yet another limited set of functions/conditions?

I think it's just a learning curve thing for me and eventually I figure out what I want to do.

all I want to do is write code that does

if ( isdate(mydate))
select mydate
else
select null

that's it; seems simple but now I have to learn the in's and out's of yet another vb based subsystem to make it happen with a script component.

I’m really sure I’m missing something really basic here and the light bulb will go on and I’ll say “cool I can just do it this way”.

|||There is no IS_DATE() function. I agree that would be handy.

IF...ELSE... can be provided using the conditional operator

-Jamie|||

so debugging script components does not work?

nice.

all the time I have wasted with integration services already.

I could have hand written a console c# app to to the same thing I was trying to do with integrations services.

I think I will wait for integration services 2.0 by that time it might be actually usefull.

Raw File reader utility?

Does anyone happen to know if there is a raw file reader utility available? One of my processes drops a couple of interim raw files and I'd like to be able to look at the data in them in a columnar format without having to run a downstream data flow with a reader inserted.If you need to do this, then temporarily redirect to a flat file, look at your data, and then put back the raw file destination. Or, temporarily throw a multicast in right before the raw file destination and add a flat file destination. That'd be your best option.|||

It's about time someone created a utility (Microsoft ?).

Not having one does provide a challenge for support, especially if you are heavily using raw files for staging data.

|||

Have a look at this one I've just finished.

http://sqlblogcasts.com/files/folders/ssis_tools/entry1528.aspx

Feedback please as this is hot off the presses

|||Simon,
The features look very good and pretty comprehensive.
I am going to try this this week.

Keep up the good work.|||

Simon,

Whats the best place to provide feedback?

|||

You should be able to add comments to the download page. You may have to register.

or you can contact me through my blog, see below

|||Just awesome, man. I don't have a lot of cash, but I'm kicking a few quid your way. Enjoy a pint on me!

Raw File Destination Create Always error

Hello,
I'm having issues with the Raw File Destination and using Create Once/Create Always.

First problem we have is that if we have WriteOption="Create Once" when the file is already present then we get a design-time validation error: "Error occurred because the output file already exists and the WriteOption is set to Create Once". To me that doesn't seem right - why should the fact that the file already exists be a problem - it doesn't matter if WriteOption="Create"

So OK, we can deal with that by deleting the file prior to using it. That's rubbish but there you go.
Now the REAL problem. It seems that Create Once does not do what BOL (ms-help://MS.VSCC.v80/MS.VSIPCC.v80/MS.SQLSVR.v9.en/extran9/html/d311b458-aefc-4b4d-b1a1-4c0ebbb34214.htm) suggests it will.

BOL says:

Create once

Creates a new file. If the data flow that uses the Raw File destination is in a loop, the file is created once and data is appended to the file when the loop repeats. The data appended to the file must match the file format.

If we set WriteOption="Create Once" and run it, it fails on the second iteration of the loop. The error is: "Error occurred because the output file already exists and the WriteOption is set to Create Once"

That doesn't make sense. That is COMPLETELY at odds with what BOL says (above).

What is going on here? WriteOption="Create once" doesn't do what BOL says it does.

We have ValidateExternalMetadata=FALSE on our Raw File Destination and DelayValidation=TRUE on our data-flow.
Any help very much appreciated!!!

-Jamie

Hi, me again!

This is really quite annoying me because I'm convinced that does not work even though it should. I would appreciate someone, ANYONE, downloading a demo package I have built and having a go at the same. The package is here: http://blogs.conchango.com/Admin/ImageGallery/blogs.conchango.com/jamie.thomson/20051116AggregatorDemo.zip

The only prerequisite is that you have access to AdventureWorksDW. All you need to do is change the server name in the connection manager.

If it exhibits the same behaviour that it is for me then it will fail on the second iteration of the loop. And that completely negates what BOL says (see above).

I am running this on RTM.

PLEASE can someone try this and prove to me I'm not losing my marbles. I would really appreciate it.

-Jamie|||Please can someone try this. Its not hard and it won't take long!

-Jamie|||Jamie,
I got the same error as you when I tried it. I didn't use your package but created a new one. I'm not sure if it is a BOL error or a SSIS error.
Larry|||

Larry_Pope wrote:

Jamie,

I got the same error as you when I tried it. I didn't use your package but created a new one. I'm not sure if it is a BOL error or a SSIS error.

Larry

Thank you Larry.
I think its an SSIS bug because the functionality that BOL describes sounds legitamate.

Anyone from Microsoft reading this? Should I raise a bug?

-Jamie|||Hi,

The behavior is correct. This is a documentation bug. The WriteOption = CreateOnce means it will create a file if it doesn't exist. If a file already exists, then the task will fail. If you want to delete the existing file, and create a new file everytime, you should use option WriteOption = CreateAlways. If you want to append rows to the existing file, you should use WriteOption = Append.

To be able to create the file in the first iteration of the loop, and then keep appending rows in the subsequent iterations of the loop, here is what you need to do. To be able to use Append option in design time, the file should already exist, so that it can make sure that the metadata matches. So just run one iteration of the loop with WriteOption = CreateOnce or CreateAlways and run it, so that the file is created. Then go back to the Raw file Destination, and change WriteOption to Append and set ValidateExternalMetadata to False. Now you can go ahead and delete the raw file. Now if you execute the loop, it will create the file in the first iteration and will append rows in the subsequent ones. If you use TruncateAppend option instead of Append, it will truncate the rows from previous iteration, and will Append new rows, but the metadata should still match.

Please go ahead and file a documentation bug for the incorrect documentation of CreateOnce option.

Hope that helps.

Ranjeeta|||Ranjeeta,
Thank you very very much. This worked.

I haven't filed a bug for the documentation, I have used the "Send Feedback" link in BOL instead.

If I get time I I'll try and blog this because the steps required to make it work are definately not clear.

-Jamie

raw file destination and environment variables

when using a raw file destination it would be nice to be able to use an environment variable for the filename property.

like

%my_extract%\data.txt

instead of

c:\my_extract\data.txt

Could you turn your screen round a bit... ...no, sorry, still cannot see it from here. Could you describe which OK button it is, any errors you may have when clicking, and perhaps briefly what lead up to this point.

|||

You could use the script task to define a SSIS variable, storing the file name, built using the environment variable. Then you can use this variable in Raw adapters by using the File Name from Variable access mode.

HTH.

|||

have you actually tried that?

|||

Now, I have. It worked fine for me.

Have you had problems with it?

|||How did you do it, there isn't an expressions setting for raw sources/destinations. What do you do to get the filename to be based on a variable?|||Raw file adapters have an AccessMode property, which allows you to enter a filename or a variable name. Variables can obviously use expressions.|||Doh, looked everywhere for that and ti was right under my nose.|||

not yet. I will give it a try today.

you should post some code and a sample usage.

|||

There is the AccessMode property on the raw adapters; change it to "File name from variable" and set your variable to the FileNameVariable property.

Thanks.

|||

Here is how I did it:

- Define RawFileName variable of type string on the package level

- Add the Script Task and set its ReadWriteVariables property to "RawFileName" and the script like this:

Public Sub Main()

Dts.Variables("RawFileName").Value = System.Environment.GetEnvironmentVariable("<your_env_var>") + "\<your_file_name>"

Dts.TaskResult = Dts.Results.Success

End Sub

- Add the Data Flow Task after the Script Task, define your data flow with the Raw File Destination and set its file name to come from the RawFileName variable, as explained in the previous messages.

HTH.

|||

not working for me.

so you actually ran it. it worked? seriously you actually ran it and it created the file you specified?

I create the variable at the package level.

I create the script task and stick the code in there.

I go to my advanced editor for raw file destination.

select accessmode: file name from variable

filenamevariable: user::rawfilename

it keeps telling me error at dataflow task (raw file destination[23]: the file name is not properly specified. supply the path and name to the raw file either directly in the file name property or by specifying a variable in the filenamevariable property.

what gives?

|||

Have you investigated why it is not working? If the file name is invalid, is the variable getting set correctly? Some ideas-

Set a breakpoint on the PreExecute event of the Data Flow and when broken drag the Rawfilename variable into the Watch window. Examine the value.

Add some breakpoints into the Script Task and examine the values as you step through the code.

|||the gui will not let me click the ok button|||

I can delete my raw file destination out of the data flow and put a break point on the data flow task and look at my variable.

it looks like a file name. correctly formatted in all it's glory.

user::RawFileName c:\\inetpub\\wwwroot\\a.txt

looks like a good file name to me?

why does the gui insist it's not a valid file name?

Raw File Destination Access Mode Filename from Variable Problem

I have a raw file destination and am using a variable to store the filename. In an earlier task, I create the value in the variable. User:Filename ... set to C:\Test.txt.

When I run the package, I get the "Error: 0xC0202070 at DFT Tekelec Call Events, RFD Tekelec [1365]: The file name property is not valid. The file name is a device or contains invalid characters". error. I then set a breakpoint to examine my variables on the DataFlow pre-execute event and found my variable showing the value "C:\\Text.txt" ... so apparently XMLA is adding the \ escape character when it stores the value in the variable but not retracting it when it uses the value as the filename.

What am I missing? Can I not use pathing in the variable? And if that's the case, how do I specify a path. Went back through my Rational Guide to Scripting SSIS but did not find this addressed specifically ... my second option being build the fiilename by script and set the raw file destination property directly via script.

The Watch window shows the C style encoded value, so \ becomes \\ in this display. Using a variable for the Raw File does work, and if setting a literal value it should just work. Have you perhaps got an expression on that variable? Could that confuse things?

Where does XMLA come into this?

Raw File as Source for Multiple Packages

I have a question regarding Raw Files. I am breaking a large package into more modular components for better processing and debugging.

The process will start with a preparatory dataflow that will create a Raw File(s). This Raw File will then be used as the source in possibly 6 data flows and/or packages.

My question is whether 1 Raw File can be read concurrently by the multiple jobs and how this would affect processing. I'm assuming that this would slow processing.

My other option is to Multicast the writing of the Raw File to 5 other versions of the file. All would be identical except for filename. Obviously this would use more disk space but this is not a concern as we have lots of disk space. Our concern is for speedy processing.

If you have experience with Raw Files, please let me know how you approached this issue. As always, blogs and specific examples are always great!

Thanks in advance.

Why not use a SQL Server table instead?|||

Do you mean to load my candidate items to a dynamically created temp table? And then I can query against this table in successive packages.

What is the process for creating the temp table? I think on my Control Flow palette, I use an ExecuteSQL task to execute a Create Temp table SQL...next a Data Flow Task can then load the table and finally after all Processing packages/flows complete, I use Execute SQL Task to Drop or Truncate the table.

Is this the process or is there a better way?

|||

omegarazor wrote:

Do you mean to load my candidate items to a dynamically created temp table? And then I can query against this table in successive packages.

What is the process for creating the temp table? I think on my Control Flow palette, I use an ExecuteSQL task to execute a Create Temp table SQL...next a Data Flow Task can then load the table and finally after all Processing packages/flows complete, I use Execute SQL Task to Drop or Truncate the table.

Is this the process or is there a better way?

This is the process, but I would like to add two refinements:

If you're going to be dynamically creating and dropping the table, be certain to build the first Execute SQL task so that it has a "IF EXISTS .. DROP TABLE; CREATE TABLE" logic, so it will run correctly regardless of whether the table already exists at run time. (Use the script generation tools in SSMS to build this script.) Set DelayValidation = True for any tasks that rely on the table, so the package can run regardless of whether the table exists.

RAW Devices

Does anybody have some experience with RAW devices in MSSQL7 or MSDE?
Thanx in advance.Only with 6.5; as far as I understood support was dropped with 7.0 (have not tried since 6.5)?