Predicate Vs Residual Predicate

hi ,
what is the definition and difference between predicate and residual predicate.
give me some examples..Basically the columns used in where clause are called as predicates. Am i right.I hope Craig Freedman's Blog may help full for you http://blogs.msdn.com/craigfr/archive/2006/07/07/652668.aspxsql

Possible Validation Problem with Flat File Between Two Data Flows in a Package

I have a package set up basically with two consecutive data flows. The first flow takes data from an OLE DB Source and stores it into a Flat File Destination. The second flow uses this same flat file as a source, alters the data, and stores the data in the same flat file, overwriting the old file. I set DelayValidation to True on the flat file. Still, here are the error messages I am receiving:

Error: 0xC020200E at DO, Flat File Destination [7676]: Cannot open the datafile "C:\Temp.txt".

Error: 0xC004701A at DO, DTS.Pipeline: component "Flat File Destination" (7676) failed the pre-execute phase and returned error code 0xC020200E.

I am new to SSIS, so I'm sure I have a setting wrong or something. Is the problem that SSIS is trying to write to a file from which it is simultaneously reading data?

Thank you.

Allen H wrote:

I have a package set up basically with two consecutive data flows. The first flow takes data from an OLE DB Source and stores it into a Flat File Destination. The second flow uses this same flat file as a source, alters the data, and stores the data in the same flat file, overwriting the old file. I set DelayValidation to True on the flat file. Still, here are the error messages I am receiving:

Error: 0xC020200E at DO, Flat File Destination [7676]: Cannot open the datafile "C:\Temp.txt".

Error: 0xC004701A at DO, DTS.Pipeline: component "Flat File Destination" (7676) failed the pre-execute phase and returned error code 0xC020200E.

I am new to SSIS, so I'm sure I have a setting wrong or something. Is the problem that SSIS is trying to write to a file from which it is simultaneously reading data?

Thank you.

You'll have to set DelayValidation=TRUE on the second data-flow.

You should probably use raw files rather than flat files. Unless that is you want to use the files elsewhere, other than in SSIS.

-Jamie

|||I tried some things in this order:

1) I set DelayValidation = TRUE on the second data flow. This was when I still had the flat files implemented. Same error as before.

2) I took out all of the flat files and put in raw files to store the data. Now when I run I get these new errors, based in the second data flow:

Error: 0xC0202069 at DO, Raw File Source [7848]: Unexpected end-of-file encountered while reading 4 bytes from file "C:\Temp.raw". The file ended prematurely because of an invalid file format.
Error: 0xC0047038 at DO, DTS.Pipeline: The PrimeOutput method on component "Raw File Source" (7848) returned error code 0x80004005. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
Error: 0xC0047021 at DO, DTS.Pipeline: Thread "SourceThread0" has exited with error code 0xC0047038.
Error: 0xC0047039 at DO, DTS.Pipeline: Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.
Error: 0xC0047021 at DO, DTS.Pipeline: Thread "WorkThread0" has exited with error code 0xC0047039.

3) This is just a question. Why raw files for this instead of flat files? The raw file is about 60% larger than the flat file after the data has been populated in each.
|||

Allen H wrote:

I tried some things in this order:

1) I set DelayValidation = TRUE on the second data flow. This was when I still had the flat files implemented. Same error as before.

2) I took out all of the flat files and put in raw files to store the data. Now when I run I get these new errors, based in the second data flow:

Error: 0xC0202069 at DO, Raw File Source [7848]: Unexpected end-of-file encountered while reading 4 bytes from file "C:\Temp.raw". The file ended prematurely because of an invalid file format.
Error: 0xC0047038 at DO, DTS.Pipeline: The PrimeOutput method on component "Raw File Source" (7848) returned error code 0x80004005. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
Error: 0xC0047021 at DO, DTS.Pipeline: Thread "SourceThread0" has exited with error code 0xC0047038.
Error: 0xC0047039 at DO, DTS.Pipeline: Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.
Error: 0xC0047021 at DO, DTS.Pipeline: Thread "WorkThread0" has exited with error code 0xC0047039.

Strange. Its as if the second data-flow is trying to read the file before it has finished been written. This would be consistent with the behaviour you were getting with flat files.

Allen H wrote:

3) This is just a question. Why raw files for this instead of flat files? The raw file is about 60% larger than the flat file after the data has been populated in each.

They're much much quicker.

-Jamie

|||

Allen H wrote:

I have a package set up basically with two consecutive data flows. The first flow takes data from an OLE DB Source and stores it into a Flat File Destination. The second flow uses this same flat file as a source, alters the data, and stores the data in the same flat file, overwriting the old file. I set DelayValidation to True on the flat file. Still, here are the error messages I am receiving:

Error: 0xC020200E at DO, Flat File Destination [7676]: Cannot open the datafile "C:\Temp.txt".

Error: 0xC004701A at DO, DTS.Pipeline: component "Flat File Destination" (7676) failed the pre-execute phase and returned error code 0xC020200E.

I am new to SSIS, so I'm sure I have a setting wrong or something. Is the problem that SSIS is trying to write to a file from which it is simultaneously reading data?

Thank you.

I think your problem is that you are trying to use the same flat file as a source and destination in the second data flow. SSIS has to get a write lock on the file to use it as a destination, but it probably can't since you've already got it open for reading. I'd alter Jamie's suggestion a bit: write the results of your first data flow to a RAW file, then use that as the source in your second data flow. Then have the second data flow write to the text file as it's destination. That way you are not trying to modifiy the source you are reading from, and you shouldn't get locking errors.

|||

jwelch wrote:

Allen H wrote:

I have a package set up basically with two consecutive data flows. The first flow takes data from an OLE DB Source and stores it into a Flat File Destination. The second flow uses this same flat file as a source, alters the data, and stores the data in the same flat file, overwriting the old file. I set DelayValidation to True on the flat file. Still, here are the error messages I am receiving:

Error: 0xC020200E at DO, Flat File Destination [7676]: Cannot open the datafile "C:\Temp.txt".

Error: 0xC004701A at DO, DTS.Pipeline: component "Flat File Destination" (7676) failed the pre-execute phase and returned error code 0xC020200E.

I am new to SSIS, so I'm sure I have a setting wrong or something. Is the problem that SSIS is trying to write to a file from which it is simultaneously reading data?

Thank you.

I think your problem is that you are trying to use the same flat file as a source and destination in the second data flow. SSIS has to get a write lock on the file to use it as a destination, but it probably can't since you've already got it open for reading. I'd alter Jamie's suggestion a bit: write the results of your first data flow to a RAW file, then use that as the source in your second data flow. Then have the second data flow write to the text file as it's destination. That way you are not trying to modifiy the source you are reading from, and you shouldn't get locking errors.

Yeah John's spot on. I glossed over this bit initially: "...and stores the data in the same flat file..."

Sorry about that.

Thanks John.

-Jamie

Possible to access no. rows in groups outside the table?

Hi there,

I'm currently grouping data on some criteria, the way the data works basically means that there are between 2-3 groups guaranteed (no more). In the Group Header I have CountRows(table1_group) thus giving me the total number of rows in each group at the top of each grouping. However, I need to do a calculation in a textbox above the table using these row counts. The method of grouping is not too difficult when using the VB syntax in the grouping expression but more difficult to get the same effect from the SQL side and hence I don't want to have separate datasets calling different queries to get the information that way. Is there anyway to get access to these row counts on the groups? Reporting Services can't know how many groups there will be before processing so I'm not sure that this is possible? Ideally I suppose if there was a Group CountRows collection of some kind that could be accessed in an expression in the textbox or custom code then this might be possible. I could also add an invisible column and set the values to something specific depending on the group if there was a way to count the number of values in the table (unique values repeated in columns in a group, but unique to that group).

Any help is much appreciated,
Thanks.

Sorry, but it would also be feasible to place the textbox within the table by moving the headings and other table data downward leaving a gap at the top. However, the textbox although in the table is presumably still outside the scope of the group. Just thought I'd mention it incase it sparked an idea by anyone.

Thanks again.

|||

Have an invisible list above your table and add group the list by the same fields as your table and use Count or any other aggregate functions in textboxes inside that list.

Shyam

|||

Thanks for your response, but I'm not sure how to implement that. If I have say 3 groupings in my table. The RowCount at the top of the group headers gives the following at the top of each grouping in the table:

Group1 Total: 4

Group2 Total: 7

Group3 Total: 22

Then a single textbox at the top of the page would say

Group1 + Group2 = 11

Group1 + Group2 = 50% of Group3.

How would I accomplish this using the invisible list?

Thanks again.

|||

Have a hidden table at the top of your report and have the same 3 groupings and then delete all the rows except the group header rows. Write 3 functions in report code which will increment a public variable (3 ublic variables declared at the top of the report) whenever it is called. Say, the function names are CountGroup1, CountGroup2, CountGroup3.

In each of your 3 group headers, call each of the corresponding functions in the code to count the group records. Have the expression in the group headers like this:

=Code.CountGroup1(CountDistinct(Fields!Field1.Value, "table1_Group1"))

=Code.CountGroup2(CountDistinct(Fields!Field1.Value, "table1_Group2"))

=Code.CountGroup3(CountDistinct(Fields!Field1.Value, "table1_Group3"))

And you can access the counts by referring to the public variables at the top of your main table using an expression something like this Code.VariableName

Shyam

Possible Timeout error??

Hi all,
Am basically doing a select star on a collection of tables in Access tables,
the data that i select is then taken and put into an SQL server database. No
w
i am getting the following error, which i believe is related to a timeout, a
s
i can transfer in the region of 3-4000 records before the error occurs. Is i
t
possible in the connection string to my sql database to specify a minimum
timeout time'
Here is the error:
-2147467259[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL serv
er does not
exist or access denied
Here is my connection string:
Set gcnMartConnect = New ADODB.Connection
'Open a connection with the Marts SQL Server Database
gcnMartConnect.Open "UID=;pwd=;Database=Marts;" & _
"Server=localhost;Driver={SQL Server};"
Have at various time added the following "ConnectionTimeout=100000" in an
attempt to solve the problem, but this doesn't seem to help.
Would appreciate any helpfull suggestions.
Mike55.You could run a profiler trace, and check for 'Attention' events. When you
get this error, can you open a command prompt and ping the machine on which
the SQL Server resides? If you run into errors then, sounds like it's
network load related.
Cheers,
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

Possible Timeout error??

Hi all,
Am basically doing a select star on a collection of tables in Access tables,
the data that i select is then taken and put into an SQL server database. Now
i am getting the following error, which i believe is related to a timeout, as
i can transfer in the region of 3-4000 records before the error occurs. Is it
possible in the connection string to my sql database to specify a minimum
timeout time?
Here is the error:
-2147467259[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL server does not
exist or access denied
Here is my connection string:
Set gcnMartConnect = New ADODB.Connection
'Open a connection with the Marts SQL Server Database
gcnMartConnect.Open "UID=;pwd=;Database=Marts;" & _
"Server=localhost;Driver={SQL Server};"
Have at various time added the following "ConnectionTimeout=100000" in an
attempt to solve the problem, but this doesn't seem to help.
Would appreciate any helpfull suggestions.
Mike55.
You could run a profiler trace, and check for 'Attention' events. When you
get this error, can you open a command prompt and ping the machine on which
the SQL Server resides? If you run into errors then, sounds like it's
network load related.
Cheers,
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

Possible Timeout error??

Hi all,
Am basically doing a select star on a collection of tables in Access tables,
the data that i select is then taken and put into an SQL server database. Now
i am getting the following error, which i believe is related to a timeout, as
i can transfer in the region of 3-4000 records before the error occurs. Is it
possible in the connection string to my sql database to specify a minimum
timeout time'
Here is the error:
-2147467259[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL server does not
exist or access denied
Here is my connection string:
Set gcnMartConnect = New ADODB.Connection
'Open a connection with the Marts SQL Server Database
gcnMartConnect.Open "UID=;pwd=;Database=Marts;" & _
"Server=localhost;Driver={SQL Server};"
Have at various time added the following "ConnectionTimeout=100000" in an
attempt to solve the problem, but this doesn't seem to help.
Would appreciate any helpfull suggestions.
Mike55.You could run a profiler trace, and check for 'Attention' events. When you
get this error, can you open a command prompt and ping the machine on which
the SQL Server resides? If you run into errors then, sounds like it's
network load related.
Cheers,
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.