Regarding Bulk Insert

Hello,
I have a DTS package where it does bulk inserts into multiple tables
it works fine and all of a sudden it throws a stack dump in the sql server
error
log with this error..
Error: 0, Severity: 19, State: 0
> SqlDumpExceptionHandler: Process 82 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process..
Whenever you see a SqlDump... error, check the default LOG path for a
SQLDUMPxx.txt file. It will give you a more detail stack dump trace.
However, this usually indicates a binary mismatch for the SQL Server
executables or code bug.
You should contact MS PSS immediately for code bug resolution.
Sincerely,
Anthony Thomas

"chinn" <chinn@.discussions.microsoft.com> wrote in message
news:59C507C6-08AB-4155-BD3E-153B558AE779@.microsoft.com...
Hello,
I have a DTS package where it does bulk inserts into multiple tables
it works fine and all of a sudden it throws a stack dump in the sql server
error
log with this error..
Error: 0, Severity: 19, State: 0
> SqlDumpExceptionHandler: Process 82 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process..
sql

Regarding Bulk Insert

Hello,
I have a DTS package where it does bulk inserts into multiple tables
it works fine and all of a sudden it throws a stack dump in the sql server
error
log with this error..
Error: 0, Severity: 19, State: 0
> SqlDumpExceptionHandler: Process 82 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process..Whenever you see a SqlDump... error, check the default LOG path for a
SQLDUMPxx.txt file. It will give you a more detail stack dump trace.
However, this usually indicates a binary mismatch for the SQL Server
executables or code bug.
You should contact MS PSS immediately for code bug resolution.
Sincerely,
Anthony Thomas
"chinn" <chinn@.discussions.microsoft.com> wrote in message
news:59C507C6-08AB-4155-BD3E-153B558AE779@.microsoft.com...
Hello,
I have a DTS package where it does bulk inserts into multiple tables
it works fine and all of a sudden it throws a stack dump in the sql server
error
log with this error..
Error: 0, Severity: 19, State: 0
> SqlDumpExceptionHandler: Process 82 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process..

Regarding Bulk Insert

Hello,
I have a DTS package where it does bulk inserts into multiple tables
it works fine and all of a sudden it throws a stack dump in the sql server
error
log with this error..
Error: 0, Severity: 19, State: 0
> SqlDumpExceptionHandler: Process 82 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process..Whenever you see a SqlDump... error, check the default LOG path for a
SQLDUMPxx.txt file. It will give you a more detail stack dump trace.
However, this usually indicates a binary mismatch for the SQL Server
executables or code bug.
You should contact MS PSS immediately for code bug resolution.
Sincerely,
Anthony Thomas
"chinn" <chinn@.discussions.microsoft.com> wrote in message
news:59C507C6-08AB-4155-BD3E-153B558AE779@.microsoft.com...
Hello,
I have a DTS package where it does bulk inserts into multiple tables
it works fine and all of a sudden it throws a stack dump in the sql server
error
log with this error..
Error: 0, Severity: 19, State: 0
> SqlDumpExceptionHandler: Process 82 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process..

reg DTS Package

hi can any one help me in this issue.i use to get one text file as an
attachment from one email id weekly.the file name and email id is
same.so i want that file to be automaticaly imported to a table and
updated as per the schedule i have assigned.so wen i get that mail that
pervious table should be droped and new one should be update...can any
body help me in this issue?any ideas how to do?
Regards
samay
Instead of dropping and creating the table (and then loading the data) why
not just truncate and load the table each time?
It is helpful that the file name remains the same. That makes the process a
bit easier.
Do you have to use DTS or can you use BCP or BULK INSERT?
Whichever route you choose, you probably want one standard place for the
location of the files. Perhaps you can create a share on your database
server that you can use as an input area for data files that you need to
import.
Create the table structure.
It is fairly easy to use the Import Data wizard to create and save a DTS
package that will truncate a table and load data from a file.
Do you need any more information or pointers?
Keith Kratochvil
"samay" <sumi_r2@.rediffmail.com> wrote in message
news:1159409542.874173.160470@.b28g2000cwb.googlegr oups.com...
> hi can any one help me in this issue.i use to get one text file as an
> attachment from one email id weekly.the file name and email id is
> same.so i want that file to be automaticaly imported to a table and
> updated as per the schedule i have assigned.so wen i get that mail that
> pervious table should be droped and new one should be update...can any
> body help me in this issue?any ideas how to do?
>
> Regards
> samay
>

Reference Package Level Variables in a Script Component.

I am trying to reference a package level variable in a script component (in the Code) and am unable to do so successfully. I have it listed as a ReadOnlyVariables in the custom properties of the script component, however unable to reference it in the code.

Any help will be appreciated.

Thanks,

Andy.

Type "Me." and let intellisense help you from there.

-Jamie

|||

Jamie,

Thanks for your suggestion. I did that and I was able to locate the variable. What does "ME" refer to? Is it refering to the Script Component? Does typing ME in different procedures/functions within the Script Component refer to different things?

Thanks,

Andy.

|||

Now that I can reference the variable I am getting script component errors.

I am basically using the RowCount Component variable and reading into the script and writing it out to the destination.

I put the RowCount Variable in the ReadWriteVariables property of the Script Component since having it in the ReadOnlyVariables was hanging the application. But when I put it under the ReadWriteVariables, I get the following error.

The collection of variables locked for read and write access is not available outside of PostExecute.

at Microsoft.SqlServer.Dts.Pipeline.ScriptComponent.get_ReadWriteVariables()

at ScriptComponent_e27beb91ebac4849bc732f8d8577fd5c.Variables.get_CountID()

at ScriptComponent_e27beb91ebac4849bc732f8d8577fd5c.ScriptMain.WriteTrailerCount2()

at ScriptComponent_e27beb91ebac4849bc732f8d8577fd5c.ScriptMain.Input0_ProcessInput(Input0Buffer Buffer)

at ScriptComponent_e27beb91ebac4849bc732f8d8577fd5c.UserComponent.ProcessInput(Int32 InputID, PipelineBuffer Buffer)

at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.ProcessInput(Int32 inputID, PipelineBuffer buffer)

|||

OK, a couple of things:

1) Are your script and rowcount components in the same data-flow? This is not a good idea because the Rowcount component will not populate the variable until the data-flow is complete. If you want to use that value in the script component then you will have to break the data-flow into 2 and pass the data between the 2 using a raw file.

2) You can't reference variables listed in ReadWriteVariables inside the Input0_ProcessInputRow(Row) method. You can override the PostExecute() method which gets called when all the rows in the pipeline have passed through the component. ReadWriteVariables CAN be accessed in there.

What is it you're trying to achieve here?

-Jamie

|||

Jamie,

Firstly, I am very new to SSIS with no prior DTS experience, so please pardon my stupidity while asking questions.

1) Yes, RowCount and Script component are in the same dataflow. I wasn't aware that the RowCount variable gets populated once the data flow is completed.

2) By the time I reference my variable in the PostExecute() method isn't it too late since I need to write it out to the output buffer? I was able to programatically count the input rows and write it out. I was trying another way of accomplishing the same thing but instead of me programatically counting, I was wanting the RowCount Component to do that for me and me just grabbing the variable with the count in it and writing it out to the destination. Having the variable as ReadOnly on the script component basically hangs the entire dataflow and having it as ReadWrite causes an error when it reaches the script component.

3) Is there any documentation on the sequence of execution of methods in the script component?

|||

Andy_1979 wrote:

Jamie,

Firstly, I am very new to SSIS with no prior DTS experience, so please pardon my stupidity while asking questions.

No problem. We're all here to learn right? :)

Andy_1979 wrote:

1) Yes, RowCount and Script component are in the same dataflow. I wasn't aware that the RowCount variable gets populated once the data flow is completed.

Well this may not actually physically be the case but logically that is how you should think of it. There is certainly no guarantee that a variable populated in a component will be readable by another component in the same data-flow. You should never attempt to do this.

Don't think of the components as seperate "things". The lowest unit of execution is a data-flow. The components are just the constituent parts of that and it helps to think of them as of they are actually all executing at the same time

Andy_1979 wrote:

2) By the time I reference my variable in the PostExecute() method isn't it too late since I need to write it out to the output buffer?

Well it depends what you want to do. If you want to write it to the output buffer then you should be able to do this by splitting the data-flow into 2 as I previously suggested.

I'm actually more concerned about why you want to do this. I know I don't know your requirement but do you really need to put the same value into every single row in the pipeline? That strikes me as a strange thing to do.

Andy_1979 wrote:

I was able to programatically count the input rows and write it out. I was trying another way of accomplishing the same thing but instead of me programatically counting, I was wanting the RowCount Component to do that for me and me just grabbing the variable with the count in it and writing it out to the destination. Having the variable as ReadOnly on the script component basically hangs the entire dataflow and having it as ReadWrite causes an error when it reaches the script component.

The reason putting it in ReadOnlyVariables causes it to hang is because you are trying to read from it before the Rowcount component has finished with it. The Rowcount component has locked the variable. I refer you to my earlier point about how a data-flow executes. it is not as simple as just a series of sequential operations even though it looks as though it is.

Andy_1979 wrote:

3) Is there any documentation on the sequence of execution of methods in the script component?

Errr... probably yeah. Its fairly intuitive though. Perhaps this article in BOL will help: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/dtsref9/html/2a0aae82-39cc-4423-b09a-72d2f61033bd.htm

Why do you want to put this value into the pipeline?

-Jamie

|||

Jamie,

Thank you so much for taking time to explain all of this. The RowCount Variable is written out to destination at the end of processing as a new row. It is not outputted on every single row. I basically want to count the number of input rows coming into my script component, process them and write them out but at the end write out an additional line which has the count of total rows processed.

Andy.

|||

Well it sounds as though the extra row you want to output will most likely have different column metadata to the rest of the pipeline and if that's the case then you won't be able to do this.

But...if you do want to...then here's what you should do.

Use a script component|||This whole variable story is a joke isn't it?|||

orbit wrote:

This whole variable story is a joke isn't it?

That's not a particularly useful statement. What do you mean? Do you have feedback for the product? If so, submit it at Microsoft Conenct.

-Jamie

Reference Package Level Variables in a Script Component.

I am trying to reference a package level variable in a script component (in the Code) and am unable to do so successfully. I have it listed as a ReadOnlyVariables in the custom properties of the script component, however unable to reference it in the code.

Any help will be appreciated.

Thanks,

Andy.

Type "Me." and let intellisense help you from there.

-Jamie

|||

Jamie,

Thanks for your suggestion. I did that and I was able to locate the variable. What does "ME" refer to? Is it refering to the Script Component? Does typing ME in different procedures/functions within the Script Component refer to different things?

Thanks,

Andy.

|||

Now that I can reference the variable I am getting script component errors.

I am basically using the RowCount Component variable and reading into the script and writing it out to the destination.

I put the RowCount Variable in the ReadWriteVariables property of the Script Component since having it in the ReadOnlyVariables was hanging the application. But when I put it under the ReadWriteVariables, I get the following error.

The collection of variables locked for read and write access is not available outside of PostExecute.

at Microsoft.SqlServer.Dts.Pipeline.ScriptComponent.get_ReadWriteVariables()

at ScriptComponent_e27beb91ebac4849bc732f8d8577fd5c.Variables.get_CountID()

at ScriptComponent_e27beb91ebac4849bc732f8d8577fd5c.ScriptMain.WriteTrailerCount2()

at ScriptComponent_e27beb91ebac4849bc732f8d8577fd5c.ScriptMain.Input0_ProcessInput(Input0Buffer Buffer)

at ScriptComponent_e27beb91ebac4849bc732f8d8577fd5c.UserComponent.ProcessInput(Int32 InputID, PipelineBuffer Buffer)

at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.ProcessInput(Int32 inputID, PipelineBuffer buffer)

|||

OK, a couple of things:

1) Are your script and rowcount components in the same data-flow? This is not a good idea because the Rowcount component will not populate the variable until the data-flow is complete. If you want to use that value in the script component then you will have to break the data-flow into 2 and pass the data between the 2 using a raw file.

2) You can't reference variables listed in ReadWriteVariables inside the Input0_ProcessInputRow(Row) method. You can override the PostExecute() method which gets called when all the rows in the pipeline have passed through the component. ReadWriteVariables CAN be accessed in there.

What is it you're trying to achieve here?

-Jamie

|||

Jamie,

Firstly, I am very new to SSIS with no prior DTS experience, so please pardon my stupidity while asking questions.

1) Yes, RowCount and Script component are in the same dataflow. I wasn't aware that the RowCount variable gets populated once the data flow is completed.

2) By the time I reference my variable in the PostExecute() method isn't it too late since I need to write it out to the output buffer? I was able to programatically count the input rows and write it out. I was trying another way of accomplishing the same thing but instead of me programatically counting, I was wanting the RowCount Component to do that for me and me just grabbing the variable with the count in it and writing it out to the destination. Having the variable as ReadOnly on the script component basically hangs the entire dataflow and having it as ReadWrite causes an error when it reaches the script component.

3) Is there any documentation on the sequence of execution of methods in the script component?

|||

Andy_1979 wrote:

Jamie,

Firstly, I am very new to SSIS with no prior DTS experience, so please pardon my stupidity while asking questions.

No problem. We're all here to learn right? :)

Andy_1979 wrote:

1) Yes, RowCount and Script component are in the same dataflow. I wasn't aware that the RowCount variable gets populated once the data flow is completed.

Well this may not actually physically be the case but logically that is how you should think of it. There is certainly no guarantee that a variable populated in a component will be readable by another component in the same data-flow. You should never attempt to do this.

Don't think of the components as seperate "things". The lowest unit of execution is a data-flow. The components are just the constituent parts of that and it helps to think of them as of they are actually all executing at the same time

Andy_1979 wrote:

2) By the time I reference my variable in the PostExecute() method isn't it too late since I need to write it out to the output buffer?

Well it depends what you want to do. If you want to write it to the output buffer then you should be able to do this by splitting the data-flow into 2 as I previously suggested.

I'm actually more concerned about why you want to do this. I know I don't know your requirement but do you really need to put the same value into every single row in the pipeline? That strikes me as a strange thing to do.

Andy_1979 wrote:

I was able to programatically count the input rows and write it out. I was trying another way of accomplishing the same thing but instead of me programatically counting, I was wanting the RowCount Component to do that for me and me just grabbing the variable with the count in it and writing it out to the destination. Having the variable as ReadOnly on the script component basically hangs the entire dataflow and having it as ReadWrite causes an error when it reaches the script component.

The reason putting it in ReadOnlyVariables causes it to hang is because you are trying to read from it before the Rowcount component has finished with it. The Rowcount component has locked the variable. I refer you to my earlier point about how a data-flow executes. it is not as simple as just a series of sequential operations even though it looks as though it is.

Andy_1979 wrote:

3) Is there any documentation on the sequence of execution of methods in the script component?

Errr... probably yeah. Its fairly intuitive though. Perhaps this article in BOL will help: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/dtsref9/html/2a0aae82-39cc-4423-b09a-72d2f61033bd.htm

Why do you want to put this value into the pipeline?

-Jamie

|||

Jamie,

Thank you so much for taking time to explain all of this. The RowCount Variable is written out to destination at the end of processing as a new row. It is not outputted on every single row. I basically want to count the number of input rows coming into my script component, process them and write them out but at the end write out an additional line which has the count of total rows processed.

Andy.

|||

Well it sounds as though the extra row you want to output will most likely have different column metadata to the rest of the pipeline and if that's the case then you won't be able to do this.

But...if you do want to...then here's what you should do.

Use a script component|||This whole variable story is a joke isn't it?|||

orbit wrote:

This whole variable story is a joke isn't it?

That's not a particularly useful statement. What do you mean? Do you have feedback for the product? If so, submit it at Microsoft Conenct.

-Jamie