RE: Script task and OLEDB destination Performance

Hi fellows,

Sorry to disturb but just a question. I have a package which extracts all the records from table A and update to table B. These records may range from 100,000 to 500,000 records.

So my question is that whether is it more feasibile/efficient to use script task to pump all the rows into table B from table A or use OLEDB destionation using sql command. Which is more efficient and help me increase my package performance? Thanks again.

Regards,

Ken

If they are inserts, I'd use the OLEDB Destination to insert directly into the target table. If they are updates, use an OLEDB Destination to write the data to a temp table, then use a Execute SQL task after the data flow to issue a batch update.

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?