Configuration question

We do several imports into our database per day which may contain > 150k
records in 1 table, 50k in another and 10k in a third. These imports need to
run within a transaction because if any part of the import fails we need to
rollback all the data.
I recently got a Suspect database with a corrupt log file due to the very
large transaction file.
Is there any specific maintenance I should do or configuration setting to
better handle this? I guess I would like to clear the log and shrink it
after each day. Also, there seems to be an implicit table lock when the
imports are done. Is there a way to avoid this?
Thanks,
JoeTo keep the trans log down, you want to keep the transaction small. But in
your case, this is unavoidable. All you could do is to schedule this import
at low time. Also, you might consider dumping the data into staging tables
before inserting into the base tables.
--
-oj
"Joe" <J_no_spam@._no_spam_Fishinbrain.com> wrote in message
news:e3AY2XjQFHA.2252@.TK2MSFTNGP15.phx.gbl...
> We do several imports into our database per day which may contain > 150k
> records in 1 table, 50k in another and 10k in a third. These imports need
> to
> run within a transaction because if any part of the import fails we need
> to
> rollback all the data.
> I recently got a Suspect database with a corrupt log file due to the very
> large transaction file.
> Is there any specific maintenance I should do or configuration setting to
> better handle this? I guess I would like to clear the log and shrink it
> after each day. Also, there seems to be an implicit table lock when the
> imports are done. Is there a way to avoid this?
> Thanks,
> Joe
>

Configuration

Hi,
I have a sql server database (1 table, 500.000 records with 10 fields and
full text search index) and an ASP.NET website (IIS).
What is a good harware configuration ?
A P4 3.2 Ghz and 1 Go RAM is enough for sql server and IIS or do i need to
have sql server running on is own server ? Or 2 Go RAM ?
Thanks.
Sorry for the consulting answer but "It depends". You don't mention any
response time expectations, expected usuage, size of the IIS site, or any
availability requirements.
In general, a production SQL Server should have two or more processors.
Memory is generally very cheap and more is better.
"Steph" <no_spam@.no_spam.org> wrote in message
news:OFdiaVRZFHA.2128@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I have a sql server database (1 table, 500.000 records with 10 fields and
> full text search index) and an ASP.NET website (IIS).
> What is a good harware configuration ?
> A P4 3.2 Ghz and 1 Go RAM is enough for sql server and IIS or do i need to
> have sql server running on is own server ? Or 2 Go RAM ?
> Thanks.
>
|||Try starting off by actually using distributed architecture and buy a
seperate, dedicate box for the SQL Server installation; otherwise, you'll be
back here again in a few months sqwaking about how much memory SS is
consuming and starving your poor little web pages to death.
If you wan to keep everything in a single soup can, buy a mainframe or a
midrange system.
Anthony Thomas

"Steph" <no_spam@.no_spam.org> wrote in message
news:OFdiaVRZFHA.2128@.TK2MSFTNGP15.phx.gbl...
Hi,
I have a sql server database (1 table, 500.000 records with 10 fields and
full text search index) and an ASP.NET website (IIS).
What is a good harware configuration ?
A P4 3.2 Ghz and 1 Go RAM is enough for sql server and IIS or do i need to
have sql server running on is own server ? Or 2 Go RAM ?
Thanks.
sqlsql

Configuration

Hi,
I have a sql server database (1 table, 500.000 records with 10 fields and
full text search index) and an ASP.NET website (IIS).
What is a good harware configuration ?
A P4 3.2 Ghz and 1 Go RAM is enough for sql server and IIS or do i need to
have sql server running on is own server ? Or 2 Go RAM ?
Thanks.Sorry for the consulting answer but "It depends". You don't mention any
response time expectations, expected usuage, size of the IIS site, or any
availability requirements.
In general, a production SQL Server should have two or more processors.
Memory is generally very cheap and more is better.
"Steph" <no_spam@.no_spam.org> wrote in message
news:OFdiaVRZFHA.2128@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I have a sql server database (1 table, 500.000 records with 10 fields and
> full text search index) and an ASP.NET website (IIS).
> What is a good harware configuration ?
> A P4 3.2 Ghz and 1 Go RAM is enough for sql server and IIS or do i need to
> have sql server running on is own server ? Or 2 Go RAM ?
> Thanks.
>|||Try starting off by actually using distributed architecture and buy a
seperate, dedicate box for the SQL Server installation; otherwise, you'll be
back here again in a few months sqwaking about how much memory SS is
consuming and starving your poor little web pages to death.
If you wan to keep everything in a single soup can, buy a mainframe or a
midrange system.
Anthony Thomas
"Steph" <no_spam@.no_spam.org> wrote in message
news:OFdiaVRZFHA.2128@.TK2MSFTNGP15.phx.gbl...
Hi,
I have a sql server database (1 table, 500.000 records with 10 fields and
full text search index) and an ASP.NET website (IIS).
What is a good harware configuration ?
A P4 3.2 Ghz and 1 Go RAM is enough for sql server and IIS or do i need to
have sql server running on is own server ? Or 2 Go RAM ?
Thanks.

Configuration

Hi,
I have a sql server database (1 table, 500.000 records with 10 fields and
full text search index) and an ASP.NET website (IIS).
What is a good harware configuration ?
A P4 3.2 Ghz and 1 Go RAM is enough for sql server and IIS or do i need to
have sql server running on is own server ? Or 2 Go RAM ?
Thanks.Sorry for the consulting answer but "It depends". You don't mention any
response time expectations, expected usuage, size of the IIS site, or any
availability requirements.
In general, a production SQL Server should have two or more processors.
Memory is generally very cheap and more is better.
"Steph" <no_spam@.no_spam.org> wrote in message
news:OFdiaVRZFHA.2128@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I have a sql server database (1 table, 500.000 records with 10 fields and
> full text search index) and an ASP.NET website (IIS).
> What is a good harware configuration ?
> A P4 3.2 Ghz and 1 Go RAM is enough for sql server and IIS or do i need to
> have sql server running on is own server ? Or 2 Go RAM ?
> Thanks.
>|||Try starting off by actually using distributed architecture and buy a
seperate, dedicate box for the SQL Server installation; otherwise, you'll be
back here again in a few months sqwaking about how much memory SS is
consuming and starving your poor little web pages to death.
If you wan to keep everything in a single soup can, buy a mainframe or a
midrange system.
Anthony Thomas
"Steph" <no_spam@.no_spam.org> wrote in message
news:OFdiaVRZFHA.2128@.TK2MSFTNGP15.phx.gbl...
Hi,
I have a sql server database (1 table, 500.000 records with 10 fields and
full text search index) and an ASP.NET website (IIS).
What is a good harware configuration ?
A P4 3.2 Ghz and 1 Go RAM is enough for sql server and IIS or do i need to
have sql server running on is own server ? Or 2 Go RAM ?
Thanks.

Conert Char to Datetime

Hello,

I have made a slight error in my SQL2000 database and have found this out with 10000 plus records under my belt.

I have a field called Char with entries 12mid and 12noon. I need to convert these to Datetime. All records convert OK except these two.

I am using DTS to transfer records form old table to new table. How do I write a procedure (or something) to enable conversion of the records 12mid and 12noon to 12am and 12pm.

Thanks

GoongYou run one batch that "REPLACE"s noon/mid with pm|||pkr,

When you say batch, what exactly is that. Is is the transformation in the DTS options.
When you say replace, how would I go about it, Is it through a query, if so, how would it possibly look.

Thanks
Goong|||Batch as in 'SQL Query'. You can run a batch in DTS but since you prob' want to do this once I'd just run it from your fav' query tool.

The clue was in my response, "REPLACE". Use it with an Update query.

Conditions on latest record

I have a table that has records layed out as so:

Table:
fd_Id INT IDENTITY (1, 1)
fd_User VARCHAR(30)
fd_Effective DATETIME

Data could be as follows:
1 | "user1" | 6/20/2001
2 | "user2" | 6/1/2002
3 | "user2" | 6/5/2002
4 | "user2" | 6/5/2002
5 | "user2" | 2/1/2002
6 | "user3" | 9/1/2003
7 | "user3" | 10/2/2002
8 | "user4" | 1/1/2005

What I need to retrieve from that table is the SINGLE LATEST item of
each fd_User.

Results:
1 | "user1" | 6/20/2001
3 | "user2" | 6/5/2002 (or 4 | "user2" | 6/5/2002) since the dates are
the same but only 1 of them
6 | "user3" | 9/1/2003
8 | "user4" | 1/1/2005Untested

SELECT
MAX(FD_ID) AS 'FD_ID',
FD_USER,
MAX(FD_EFFECTIVE) AS 'FD_EFFECTIVE'
FROM F_TABLE
GROUP FD_USER|||select min(a.fd_Id) as fd_Id,
a.fd_User,
a.fd_Effective
from mytable a
inner join (select fd_User,max(fd_Effective) as fd_Effective
from mytable
group by fd_User) b on a.fd_User=b.fd_User and
a.fd_Effective=b.fd_Effective
group by a.fd_User,a.fd_Effective|||Verticon:: wrote:
> I have a table that has records layed out as so:
> Table:
> fd_Id INT IDENTITY (1, 1)
> fd_User VARCHAR(30)
> fd_Effective DATETIME
> Data could be as follows:
> 1 | "user1" | 6/20/2001
> 2 | "user2" | 6/1/2002
> 3 | "user2" | 6/5/2002
> 4 | "user2" | 6/5/2002
> 5 | "user2" | 2/1/2002
> 6 | "user3" | 9/1/2003
> 7 | "user3" | 10/2/2002
> 8 | "user4" | 1/1/2005
> What I need to retrieve from that table is the SINGLE LATEST item of
> each fd_User.
> Results:
> 1 | "user1" | 6/20/2001
> 3 | "user2" | 6/5/2002 (or 4 | "user2" | 6/5/2002) since the dates are
> the same but only 1 of them
> 6 | "user3" | 9/1/2003
> 8 | "user4" | 1/1/2005

First add the constraint that you're apparently missing:

ALTER TABLE tbl
ADD CONSTRAINT ak1_tbl
UNIQUE (fd_User, fd_Effective);

Then:

SELECT fd_Id, fd_User, fd_Effective
FROM tbl
WHERE fd_Effective =
(SELECT MAX(fd_Effective)
FROM tbl AS t
WHERE t.fd_User = tbl.fd_User);

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--sqlsql

Conditionally skip or jump steps in a job

Is it possible to conditionally skip or jump steps in a job without failing
a
step?
For example I don’t want to run a step if the are no records in a table.
I also need to loop based on a condition.About loops:
http://www.sqldts.com/default.aspx?246
About conditional execution:
http://www.sqldts.com/default.aspx?218
http://www.sqldts.com/default.aspx?214
Chris
"Dave" wrote:

> Is it possible to conditionally skip or jump steps in a job without failin
g a
> step?
> For example I don’t want to run a step if the are no records in a table.
> I also need to loop based on a condition.
>|||You could place your (IF.. BEGIN.. END) condition within the T-SQL or stored
procedure beging called.
"Dave" <Dave@.discussions.microsoft.com> wrote in message
news:27AF395F-A67D-43E6-9F40-009ACCCB0877@.microsoft.com...
> Is it possible to conditionally skip or jump steps in a job without
> failing a
> step?
> For example I don't want to run a step if the are no records in a table.
> I also need to loop based on a condition.
>sqlsql

Conditionally Parameterizing a SQLDataSource

I'm pararmeterizing a SQLDataSource, but I've encountered a problem. The users want all records returned if no ID is provided. So when they first hit the page, they will see all records, then they can filter down to one ID if needed. However, if I add a selectparameter of ID, I have to default it to a value, which won't give the users all records.

Suggestions?

The SQLDataSource has a CancelSelectOnNullParameter property and your parameters have a ConvertEmptyStringToNull property. These should help you achieve what you want. Read about these properties on MSDN.

Conditionally end a report

I am new to CR so this might be a really stupid question. CR version 10.

I want to end the report after X number of records based on a parameter field. Specifically I want to list customers in order of total sales but specify how many customers to print out. Like, Top 100 Customers by Sales.

I know I could print all records to the screen then choose to only print X number of pages but that's not possible if the report goes right to the printer.

Thank you.Type TOP into Crystal's online help (index) and see what you get.l

Conditional Split to send top 10, top20, and top 30 in three different flows ?

I have dataflow that i wish to split. The recordset that i uses is sorted by the Sort Task. After that i'll like to take the Top 10 records and send in one direction, then ill like to take the top 20 and send in another direction and finally Top 30 in a third direction.

How can i do that ?

You can use a script component to give each record a number using the technique described here: http://www.sqlis.com/default.aspx?37

Then you can filter on the values that are created using a conditional split transform component.

Make sense?

-Jamie

|||Yes but it seems to be a bit of a workaround. I was looking for an easier way like using SQL select top 10 * from....|||

cgpl wrote:

Yes but it seems to be a bit of a workaround. I was looking for an easier way like using SQL select top 10 * from....

Yeah I agree, that would be nice. Perhaps you could request it at betaplace or http://lab.msdn.microsoft.com/productfeedback/Default.aspx?

-Jamie|||You could do it all in one script component. Add some extra outputs and then direct the row to the correct output based on the counter value.

I'd demonstrate if I knew what the equivalent of buffer.DirectRow(<outputindex>) was in the script component, but that would be if the syntax for a full component.|||

DarrenSQLIS wrote:

You could do it all in one script component. Add some extra outputs and then direct the row to the correct output based on the counter value.

I'd demonstrate if I knew what the equivalent of buffer.DirectRow(<outputindex>) was in the script component, but that would be if the syntax for a full component.

Oh yeah, Darren's right. And the syntax he's on about is <buffername>.AddRow()

-Jamie|||The easiest way to deal with that, would be if you could disable the "Random" feature in the Row Sample Task. Then you could do a Multicast to 3 different Row Sample Tasks|||

yep, I have the same problem. I have a dataset containing a record for each bill in the past month and I want to flag the top 10 customers according to the aggregated amount pr customer.

Like this in plain SQL:

select top 10 CustName, sum(Amount) as sum_Amount into #ek_temp

from erhverv_kreditnota group by CustName order by sum_Amount

go

update erhverv_kreditnota

set top10 = 'Top 10'

where CustName in (select CustName from #ek_temp)

go

Any way to do this without scripting?

|||

cgpl wrote:

The easiest way to deal with that, would be if you could disable the "Random" feature in the Row Sample Task. Then you could do a Multicast to 3 different Row Sample Tasks

Yeah another excellent idea, you would assume that selecting the first N rows is easier than selecting a random number. Did you submit the idea to Microsoft?

-Jamie|||

jesal wrote:

yep, I have the same problem. I have a dataset containing a record for each bill in the past month and I want to flag the top 10 customers according to the aggregated amount pr customer.

Like this in plain SQL:

select top 10 CustName, sum(Amount) as sum_Amount into #ek_temp

from erhverv_kreditnota group by CustName order by sum_Amount

go

update erhverv_kreditnota

set top10 = 'Top 10'

where CustName in (select CustName from #ek_temp)

go

Any way to do this without scripting?

As explained, no, not really. Is there any reason why you don't want to do this in a script?

-Jamie|||I did!

http://lab.msdn.microsoft.com/productfeedback/viewfeedback.aspx?feedbackid=2bc4ee52-aec6-409d-b39c-6e2fb4945799|||Out of interest, would you want to send:
Rows 1-10 to output1
Rows 1-20 to output2
Rows 1-30 to output3

or

Rows 1-10 to output1
Rows 11-30 to output2
Rows 31-60 to output3

?

I might have a crack at this myself...it sounds quite interesting.

-Jamie|||

I would like the first scenario:

Rows 1-10 to output1
Rows 1-20 to output2
Rows 1-30 to output3

-

There are two reasons why I would like to do this without a script. First I don’t have that much experience with scripting. Second I think that a simple and common task like this should be available “off the shelves”.

I tried scripting based on Jamie’s answer to the thread “Script Transformation” (http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=60464).

1. My problem is that the number of output rows is different from the number of input rows – so I need to limit the script to run on the first 10-30 rows, not the entire recordset.

2. Jamie suggests the syntax <buffername>.AddRow() and Outputbuffer (in the “Script Transformation” thread). I used the default declaration (ByVal Row As Input0Buffer) – but as far as I can see this buffer contains both the input and output and I cannot find Row.AddRow?

Any suggestions to a script that redirects the first 10 rows from input to output?

- Jeppe

|||

jesal wrote:

I would like the first scenario:

Rows 1-10 to output1
Rows 1-20 to output2
Rows 1-30 to output3

-

There are two reasons why I would like to do this without a script. First I don’t have that much experience with scripting. Second I think that a simple and common task like this should be available “off the shelves”.

I tried scripting based on Jamie’s answer to the thread “Script Transformation” (http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=60464).

1. My problem is that the number of output rows is different from the number of input rows – so I need to limit the script to run on the first 10-30 rows, not the entire recordset.

2. Jamie suggests the syntax <buffername>.AddRow() and Outputbuffer (in the “Script Transformation” thread). I used the default declaration (ByVal Row As Input0Buffer) – but as far as I can see this buffer contains both the input and output and I cannot find Row.AddRow?

Any suggestions to a script that redirects the first 10 rows from input to output?

- Jeppe

Jeppe,
This should do it: http://blogs.conchango.com/jamiethomson/archive/2005/07/27/1877.aspx

-Jamie|||Thank you Jamie!

Your "SSIS Nugget: Select Top N in a data-flow" worked great. I have one question though - I couldnt get the "Output0Buffer" to work, but when I changed the SynchronousInputID for the Output to 0 it worked, what is the connection between the declaration of the Output0Buffer and the Synchronous setting?

Thansk again

- Jeppe

Conditional Split Question

Hello,

I am have an ID column that sometimes contains all numeric characters and sometimes contains all digits. I would like to the records with all digits (0-9) to continue downstream in my Data Flow. I would like the records that contain characters other than digits to be logged to a table.

This sounds like a job for the Conditional Split transformation, but I don't see a way to easily test for a numeric value. For example, I would like to use something like ISNUMERIC([MyIDField]) for testing the values in my Conditional Split, but I don't see a way to do this.

Do I have to create a Derived Column transformation prior to my conditional split that populates a "numeric" ID column for each of my records then test this Derived Column in my Conditional Split? Seems like more work than I would to see for something as simple as testing for a numeric...

TIA...

Brian

"numeric characters and sometimes contains all digits"... digits are numeric? Anyway I'd use my Regular Expression Transform http://www.sqlis.com/default.aspx?91. It will handle the test and split, and regular expressions are great for validating things like this.

|||

Brian,

You are correct, there is no ISNUMERIC() function in the expression evaluator. However, in your case, there is a fairly decent workaround I think.

If you are SURE that the string is never a mix of numeric and character data, you could use the following expression to direct alpha strings (where Col is the name of your input column:

FINDSTRING("0123456789", SUBSTRING(Col, 1, 1), 1) == 0

This expresssion will be true if the first character of Col is an alpha character.

Hope this helps.

Mark

Conditional Split Question

I have a package which has a conditional task which directs rows to its respective OLEDB command. The records are sorted from the source system in chronological order. The problem I am experiencing is that some of the operations do not seem to be occurring in the same order. An example of this would be someone inserts a record, deletes the record and reinserts in the record in that order. When we run the package we can see the records are coming down in chronological order but the delete from the split seems to occur after the inserts. Has anyone else experienced this? Is there anything I might be missing to ensure things happen in the order they should? Any advice would be greatly appreciated. Thank you.Can you provide more details surrounding your data flow setup? Try to illustrate to us how you have the data flow built from source all of the way through the destinations (or OLE DB Commands).|||

Bagles1 wrote:

I have a package which has a conditional task which directs rows to its respective OLEDB command. The records are sorted from the source system in chronological order. The problem I am experiencing is that some of the operations do not seem to be occurring in the same order. An example of this would be someone inserts a record, deletes the record and reinserts in the record in that order. When we run the package we can see the records are coming down in chronological order but the delete from the split seems to occur after the inserts. Has anyone else experienced this? Is there anything I might be missing to ensure things happen in the order they should? Any advice would be greatly appreciated. Thank you.

If you have 2 OLE DB Command tasks in the same dataflow you should not rely on the rows being actioned in the order that they enter the pipeline. There is no sychronisation between the two paths. Once the rows are in different paths then they are two seperate streams of data and will be teated as such. If you want to ensure that the deleted happen after the insertions push the data for deletioninto a raw file and issue the deletes from another data-flow.

-Jamie

|||

Jamie Thomson wrote:

If you have 2 OLE DB Command tasks in the same dataflow you should not rely on the rows being actioned in the order that they enter the pipeline. There is no sychronisation between the two paths. Once the rows are in different paths then they are two seperate streams of data and will be teated as such. If you want to ensure that the deleted happen after the insertions push the data for deletioninto a raw file and issue the deletes from another data-flow.

-Jamie

Yep, or create separate data flows, with precedence enforced at the control flow level.|||I have a data reader that pulls data from a staging environment whereas the data is sorted in chronological order. It immediately goes down into a conditional split where a field holds 1 of 3 values; I, U, D (Insert, Update, Delete). From there each condition has a RowCount transformation to count the rows as they pass through and then onto an OLEDB command that performs the necessary operation. The stream stops at the OLEDB command and that is all there is.|||Ugggghhhhh! That hurts.|||

Bagles1 wrote:

I have a data reader that pulls data from a staging environment whereas the data is sorted in chronological order. It immediately goes down into a conditional split where a field holds 1 of 3 values; I, U, D (Insert, Update, Delete). From there each condition has a RowCount transformation to count the rows as they pass through and then onto an OLEDB command that performs the necessary operation. The stream stops at the OLEDB command and that is all there is.

As I said above, there is no guarantee of the order in which rows get processed, especially when you send them to different data paths.

There is also no guarantee that data will actually enter the pipeline from the staging environment in the order that you think it does. There is no concept of a set of data being ordered - there are lot of things that can influence the order that rows appear in the pipeline.

-Jamie

|||

Jamie Thomson wrote:

Bagles1 wrote:

I have a data reader that pulls data from a staging environment whereas the data is sorted in chronological order. It immediately goes down into a conditional split where a field holds 1 of 3 values; I, U, D (Insert, Update, Delete). From there each condition has a RowCount transformation to count the rows as they pass through and then onto an OLEDB command that performs the necessary operation. The stream stops at the OLEDB command and that is all there is.

As I said above, there is no guarantee of the order in which rows get processed, especially when you send them to different data paths.

There is also no guarantee that data will actually enter the pipeline from the staging environment in the order that you think it does. There is no concept of a set of data being ordered - there are lot of things that can influence the order that rows appear in the pipeline.

-Jamie

Jamie,

Are you saying that if I have an Order By statement in my data reader that there is no guarantee that it will actually be in that order or have I misunderstood your statement?

|||

Bagles1 wrote:

Jamie Thomson wrote:

Bagles1 wrote:

I have a data reader that pulls data from a staging environment whereas the data is sorted in chronological order. It immediately goes down into a conditional split where a field holds 1 of 3 values; I, U, D (Insert, Update, Delete). From there each condition has a RowCount transformation to count the rows as they pass through and then onto an OLEDB command that performs the necessary operation. The stream stops at the OLEDB command and that is all there is.

As I said above, there is no guarantee of the order in which rows get processed, especially when you send them to different data paths.

There is also no guarantee that data will actually enter the pipeline from the staging environment in the order that you think it does. There is no concept of a set of data being ordered - there are lot of things that can influence the order that rows appear in the pipeline.

-Jamie

Jamie,

Are you saying that if I have an Order By statement in my data reader that there is no guarantee that it will actually be in that order or have I misunderstood your statement?

Well, YES, the order will be retained until some other downstream data flow component rearranges the order. Surely you can't expect a union all to maintain order, for instance.|||

Bagles1 wrote:

Jamie,

Are you saying that if I have an Order By statement in my data reader that there is no guarantee that it will actually be in that order or have I misunderstood your statement?

In that case then yes, it will enter the pipeline in the order decreed by the ORDER BYstatement but thereafter you should not rely on the ordering within the pipeline. And you should DEFINATELY not rely on rows reaching a destination/OLE DB Command in some order when those rows are in different data paths.

-Jamie

|||Thank you for your explanation. A very painful lesson to learn this late in the project. Unfortunately in our case we have to process the rows in chronological order so it looks like it will be the script component once again.|||

Bagles1 wrote:

Thank you for your explanation. A very painful lesson to learn this late in the project. Unfortunately in our case we have to process the rows in chronological order so it looks like it will be the script component once again.

If this is true then it sounds as though you have some sort of procedural logic going on - that's not really possible with the standard components. Script component may help though.

I don't actually know your requirement but wouldn't it just make more sense to count the number of inserts and deletes per "thing". If there are more inserts than deletes then you insert the "thing", otherwise you don't.

Good luck with it anyway.

-Jamie

Conditional split on date ?

Hi,

I have a DT_DATE column. I'd like to achieve a conditional split to ignore all records for which the date is below a specific hardcoded date (eg: 2007-03-01).

I'm having a hard time trying to express this using the conditional split transform.

What is the correct syntax to express a DT_DATE literal ?

eg:
[date] < (DT_DATE) "2007-03-01"

regards

Thibaut

What you have should work fine. I built a little test package to verify, and each of these worked as expected:

Code Snippet

HireDate < (DT_DATE)"1998-01-30"

Code Snippet

[HireDate] < (DT_DATE)"1998-01-30"

Code Snippet

HireDate < (DT_DATE)"01/30/1998"

Code Snippet

[HireDate] < (DT_DATE)"01/30/1998"

What behavior are you experiencing that prompts you to ask the question?

|||Are you sure [date] is a DT_DATE column and not a DT_DBTIMESTAMP column? That is, does it contain a time component?

Just double checking.

Conditional Split - Expression Evaluates to Null

Hi everyone!

I'm using a conditional split to discriminate modified records. My expression looks like this:

col1_source != col1_dest || col2_source != col2_des.....and so on. I use OLE DB Command afterward to update modified records.

It all works fine if no columns evaluate to null. If any (source or dest.) evaluates to null, component fails.

Any tips how to solve a problem?

It has to work like this:

If colX_source is null and colX_dest is not null --> Update

If colX_source is not null and colX_dest is null --> Update

If both colX_source and colX_dest are null --> No update

p.s. i apologize if a similar thread exists, I haven't found something of use to me.

Use the ISNULL() function to test your columns for NULLs.

For instance:

ISNULL(colX_source) && !ISNULL(colX_dest)

|||

Simply add to your expression:

(IsNull(colX_Source) && IsNull(colX_dest)) || ((IsNull(colX_source) == false) && (IsNull(colX_dest)))

etc...

Or, use a derived column before the conditional split to eliminate the nulls:

Output1 / add as new column / IsNull(colX_Source) ? "" : colX_Source

|||

Thank you both for a quick response.

I understood the derived column idea but not the isnull idea.

If I want to cover all the cases in which I want the update to execute, my expression (if I haven’t missunderstood sth) should look like this:

isNull(col1_source) && !IsNull(col1_dest) || !isNull(col1_source) && IsNull(col1_dest) || col1_source != col1_dest

Isn’t it true that again, the last part of the expression col1_source != col1_dest evaluates to null if one of the columns is null? And that the only solution to the problem is a derived column which transforms null values in “”?

I’m new to SSIS and slowly learning that programming logic is not always the SSIS logic.

|||

The way it is written, it will only get to the last branch if both columns are NULL, which would render the last branch a moot point anyway because they would have to be "equal."

Use the derived column idea to change the NULLs to empty strings and then your conditional split will be easier to write/maintain. Plus, if you're not supposed to have NULL data, then it would be the proper thing to do to clean it up.

|||

I think my initial post is a little misguiding because I omitted a condition I have to test (which in fact is the last branch (a moot point as you say )).

If both colX_source and colX_dest are not null and different --> also Update!

+ the one I mentioned in the 1st post:

If colX_source is null and colX_dest is not null --> Update

If colX_source is not null and colX_dest is null --> Update

If both colX_source and colX_dest are null --> No update

|||( !ISNULL(colX_source) && !ISNULL(colX_dest) ) && (colX_source != colX_dest)|||

Wow, it works! Thank you for your patience!

Conditional Split

I am using a conditional split to evaluate the condition below. It should only send records to my SQL Server database if the PatientZip matches one of the eight below and the PatientCity is not Wichita Falls (you wouldn't believe how bad this is mispelled sometimes). I checked the output table and it has all records for the zipcodes below both matching and non-matching the cityname of Wichita Falls. The table should not have entries for records with the cityname of Wichita Falls. Do I have the code correct or could I have missed something?

LTRIM(PatientCity) != "Wichita Falls" && (PatientZip == "76301" || PatientZip == "76302" || PatientZip == "76305" || PatientZip == "76306" || PatientZip == "76307" || PatientZip == "76308" || PatientZip == "76309" || PatientZip == "76310")

One thing to look at is if you're sending the correct output from the Conditional Split to your destination.

Another thing is that you may want to RTRIM to catch trailing spaces instead of just LTRIMming to catch leading spaces.

|||

Thanks for the replay Matthew. I added the RTRIM as you suggested. My output name for my condition is "Bad City Name" and the default output name is "Correct City Name". I connected each output to different SQL Server tables that are exactly the same except for the table names. The Bad City Name output table is still being populated with data that is actually correct (city = "Wichita Falls" and is in the zipcodes listed above). The Correct City Name output table is being populated with any and all entries except (city = "Wichita Falls" and is in the zipcodes listed above).

As a check; I just ran the following query against the source database after replacing the logical operators with their SQL equivalents and the double quotes (") with single quotes (') and the query returned exactly what I am attempting to achieve with Integration Services.

select PatientName, PatientCity, PatientState, PatientZip

from ampfm.rpt_PatientDemographics

where LTRIM(RTRIM(PatientCity)) != 'Wichita Falls'

and (PatientZip = '76301' or PatientZip = '76302'

or PatientZip = '76305' or PatientZip = '76306'

or PatientZip = '76307' or PatientZip = '76308'

or PatientZip = '76309' or PatientZip = '76310')

I am at a loss as to why the Integration Services routine is not returning the correct row data. I must have something designed incorrectly. This is the first of several similar packages I am creating as the cornerstone to our audit process, but I need the correct data in the output (reporting) tables first. Please advise anything you feel may be in error that I can check.

Thanks!

|||

Have you tried putting a data viewer on the path going into and out of the conditional split? It might help to see what data you are getting in, and what data is on which path going out... (perhaps you have your tables flipped on your destinations, etc)

|||

I appreciate your post. Yes, I had earlier added data viewers and they showed the same data that querying the output tables were showing. I have everything set correctly as far as I can tell, it just isn't working as expected.

I finally deleted the conditional split and went with a Lookup object using the query below and it is pulling the correct information and putting it in the correct output tables. I guess I'll try to tackle conditional split issues at another time.

select PatientName, PatientCity, PatientState, PatientZip

from ampfm.rpt_PatientDemographics3

where LTRIM(RTRIM(PatientCity)) <> 'Wichita Falls'

and LTRIM(RTRIM(PatientZip)) IN ('76301','76302',

'76305','76306','76307','76308','76309','76310')

Thanks to all who have responded.

Conditional Send Mail Task

Hi,

I want send email if certain conditions are met (by send mail task)... if compnay records does not exists in some table (returns null)... not task failure.... how do I achieve this without using Script task?

does any one have an idea about it,

regards

You need conditional workflow: http://www.sqlis.com/default.aspx?306

You can base your expression on a boolean variable that can be set from various places, including a script task.

-Jamie

|||

In contiuation i would like to ask another question i.e. I want to send the results of my query (complete result sets) from email task? How will I able to achieve the task.?

ur help will be appreciated

|||

Zadoras wrote:

In contiuation i would like to ask another question i.e. I want to send the results of my query (complete result sets) from email task? How will I able to achieve the task.?

ur help will be appreciated

Hmmm interesting one. probably the easiest way is to push that data into a flat file destination and then email that file as an attachment.

-Jamie

|||

that's what i was thinking... but i m searching for other way (may be the easiest way)

BTW... thanx for your support

if ne one come across to ne better idea than that please let me know

Conditional 'Order By' statement

I have a table that stores three columns of data, namely
1. Order_ID
2. Exam_Start_Date
3. Order_Received_Date
I want to order the records as follows:
If the Exam_Start_Date is within the next 10 days or past, then order by the
exam_start_date. Otherwise order by the order_Received_Date. Therefore the
result set should display records where the exam_start_date is within next 10
days first, then display all other records.
I have tried the following SQL but it doesnt appear to work (i.e. Order of
the records is not exam_start_date (if within next 10 days), otherwise
Order_Received_Date.
select order_id, order_received_date, exam_start_date
from orders
ORDER BY CASE WHEN Exam_Start_date < '20050118' THEN Exam_Start_Date ELSE
Order_Received_Date END
Any suggestions greatly appreciated!
Wes.
On Sat, 7 Jan 2006 18:51:02 -0800, Wez wrote:

>I have a table that stores three columns of data, namely
>1. Order_ID
>2. Exam_Start_Date
>3. Order_Received_Date
>I want to order the records as follows:
>If the Exam_Start_Date is within the next 10 days or past, then order by the
>exam_start_date. Otherwise order by the order_Received_Date. Therefore the
>result set should display records where the exam_start_date is within next 10
>days first, then display all other records.
>I have tried the following SQL but it doesnt appear to work (i.e. Order of
>the records is not exam_start_date (if within next 10 days), otherwise
>Order_Received_Date.
>select order_id, order_received_date, exam_start_date
>from orders
>ORDER BY CASE WHEN Exam_Start_date < '20050118' THEN Exam_Start_Date ELSE
>Order_Received_Date END
>Any suggestions greatly appreciated!
Hi Wes,
Try if this works better:
ORDER BY CASE WHEN Exam_Start_date < '20050118'
THEN Exam_Start_Date
ELSE '20050118'
END DESC,
Order_Received_Date DESC
Or, a more generic version:
ORDER BY CASE WHEN Exam_Start_date < DATEADD(day,-10, CURRENT_TIMESTAMP)
THEN Exam_Start_Date
ELSE DATEADD(day,-10, CURRENT_TIMESTAMP)
END DESC,
Order_Received_Date DESC
Hugo Kornelis, SQL Server MVP

Conditional iif statement

Ok I have a sp that returns a number of records, each identfied as being part
of a group based on the Type field.
Type Name Amount
1 Test1 1.00
1 Test2 1.00
2 Test3 2.00
3 Test4 3.00
What I am using is iff(Type.value = 1, Price.value, 0) When I do this it
works fine but when I use them middle records iff(Type.value = 2,
Price.value, 0) it displays 1.00 as the price for type 2, and it should
display 2.00 for type 2.
Any help is greatly apprciated. Also, due to the formatting specifics I am
using single textboxes instead of a table or list.
DigivixTry this...
=iif(Fields!Type.Value=1, Fields!Price.Value,0)
the same with Value =2
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"DigitalVixen" <DigitalVixen@.discussions.microsoft.com> wrote in message
news:2B698793-0708-4538-9DCD-595819A758D8@.microsoft.com...
> Ok I have a sp that returns a number of records, each identfied as being
> part
> of a group based on the Type field.
> Type Name Amount
> 1 Test1 1.00
> 1 Test2 1.00
> 2 Test3 2.00
> 3 Test4 3.00
> What I am using is iff(Type.value = 1, Price.value, 0) When I do this it
> works fine but when I use them middle records iff(Type.value = 2,
> Price.value, 0) it displays 1.00 as the price for type 2, and it should
> display 2.00 for type 2.
> Any help is greatly apprciated. Also, due to the formatting specifics I
> am
> using single textboxes instead of a table or list.
> Digivix|||Sorry for the typo's but that is exactly what i am using and it is giving me
the first record's price only.
"Wayne Snyder" wrote:
> Try this...
> =iif(Fields!Type.Value=1, Fields!Price.Value,0)
> the same with Value =2
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "DigitalVixen" <DigitalVixen@.discussions.microsoft.com> wrote in message
> news:2B698793-0708-4538-9DCD-595819A758D8@.microsoft.com...
> > Ok I have a sp that returns a number of records, each identfied as being
> > part
> > of a group based on the Type field.
> >
> > Type Name Amount
> > 1 Test1 1.00
> > 1 Test2 1.00
> > 2 Test3 2.00
> > 3 Test4 3.00
> >
> > What I am using is iff(Type.value = 1, Price.value, 0) When I do this it
> > works fine but when I use them middle records iff(Type.value = 2,
> > Price.value, 0) it displays 1.00 as the price for type 2, and it should
> > display 2.00 for type 2.
> >
> > Any help is greatly apprciated. Also, due to the formatting specifics I
> > am
> > using single textboxes instead of a table or list.
> >
> > Digivix
>
>|||Can't you go back to a table and simulate the look & feel? I think if you
did it in the detail row it would pull the right values.
--
"Everyone knows something you don't know"
"DigitalVixen" wrote:
> Sorry for the typo's but that is exactly what i am using and it is giving me
> the first record's price only.
> "Wayne Snyder" wrote:
> > Try this...
> >
> > =iif(Fields!Type.Value=1, Fields!Price.Value,0)
> > the same with Value =2
> >
> >
> >
> > --
> > Wayne Snyder, MCDBA, SQL Server MVP
> > Mariner, Charlotte, NC
> > www.mariner-usa.com
> > (Please respond only to the newsgroups.)
> >
> > I support the Professional Association of SQL Server (PASS) and it's
> > community of SQL Server professionals.
> > www.sqlpass.org
> >
> > "DigitalVixen" <DigitalVixen@.discussions.microsoft.com> wrote in message
> > news:2B698793-0708-4538-9DCD-595819A758D8@.microsoft.com...
> > > Ok I have a sp that returns a number of records, each identfied as being
> > > part
> > > of a group based on the Type field.
> > >
> > > Type Name Amount
> > > 1 Test1 1.00
> > > 1 Test2 1.00
> > > 2 Test3 2.00
> > > 3 Test4 3.00
> > >
> > > What I am using is iff(Type.value = 1, Price.value, 0) When I do this it
> > > works fine but when I use them middle records iff(Type.value = 2,
> > > Price.value, 0) it displays 1.00 as the price for type 2, and it should
> > > display 2.00 for type 2.
> > >
> > > Any help is greatly apprciated. Also, due to the formatting specifics I
> > > am
> > > using single textboxes instead of a table or list.
> > >
> > > Digivix
> >
> >
> >|||Hi David,
Thank you for the reply, however I don't understand what is meant by
"simulate the look & feel", can you please be a little more specific?
Thanks
"David Bienstock" wrote:
> Can't you go back to a table and simulate the look & feel? I think if you
> did it in the detail row it would pull the right values.
> --
> "Everyone knows something you don't know"
>
> "DigitalVixen" wrote:
> > Sorry for the typo's but that is exactly what i am using and it is giving me
> > the first record's price only.
> >
> > "Wayne Snyder" wrote:
> >
> > > Try this...
> > >
> > > =iif(Fields!Type.Value=1, Fields!Price.Value,0)
> > > the same with Value =2
> > >
> > >
> > >
> > > --
> > > Wayne Snyder, MCDBA, SQL Server MVP
> > > Mariner, Charlotte, NC
> > > www.mariner-usa.com
> > > (Please respond only to the newsgroups.)
> > >
> > > I support the Professional Association of SQL Server (PASS) and it's
> > > community of SQL Server professionals.
> > > www.sqlpass.org
> > >
> > > "DigitalVixen" <DigitalVixen@.discussions.microsoft.com> wrote in message
> > > news:2B698793-0708-4538-9DCD-595819A758D8@.microsoft.com...
> > > > Ok I have a sp that returns a number of records, each identfied as being
> > > > part
> > > > of a group based on the Type field.
> > > >
> > > > Type Name Amount
> > > > 1 Test1 1.00
> > > > 1 Test2 1.00
> > > > 2 Test3 2.00
> > > > 3 Test4 3.00
> > > >
> > > > What I am using is iff(Type.value = 1, Price.value, 0) When I do this it
> > > > works fine but when I use them middle records iff(Type.value = 2,
> > > > Price.value, 0) it displays 1.00 as the price for type 2, and it should
> > > > display 2.00 for type 2.
> > > >
> > > > Any help is greatly apprciated. Also, due to the formatting specifics I
> > > > am
> > > > using single textboxes instead of a table or list.
> > > >
> > > > Digivix
> > >
> > >
> > >

Conditional Delete without Logging Transactions

Hi,
I need to delete many records in a table based on some conditions in the
where clause. This delete is taking plenty of time. How do I disable the
logging of transactions?
Thanks and Regards,
Prasanth
Hi
You cannot disable logging during deletion as in your case.
Divide the deletion into small transaction and try to run it. Also check out
if there are some indexes defined on the table, try to drop them and after
deletion to recreate them.
"Prasanth" <Prasanth@.discussions.microsoft.com> wrote in message
news:98AE9AA6-EF24-4334-AA8A-65E919119B5C@.microsoft.com...
> Hi,
> I need to delete many records in a table based on some conditions in the
> where clause. This delete is taking plenty of time. How do I disable the
> logging of transactions?
> --
> Thanks and Regards,
> Prasanth
|||Thanks, I will do with that.
"Uri Dimant" wrote:

> Hi
> You cannot disable logging during deletion as in your case.
> Divide the deletion into small transaction and try to run it. Also check out
> if there are some indexes defined on the table, try to drop them and after
> deletion to recreate them.
> "Prasanth" <Prasanth@.discussions.microsoft.com> wrote in message
> news:98AE9AA6-EF24-4334-AA8A-65E919119B5C@.microsoft.com...
>
>

Conditional Delete without Logging Transactions

Hi,
I need to delete many records in a table based on some conditions in the
where clause. This delete is taking plenty of time. How do I disable the
logging of transactions?
--
Thanks and Regards,
PrasanthHi
You cannot disable logging during deletion as in your case.
Divide the deletion into small transaction and try to run it. Also check out
if there are some indexes defined on the table, try to drop them and after
deletion to recreate them.
"Prasanth" <Prasanth@.discussions.microsoft.com> wrote in message
news:98AE9AA6-EF24-4334-AA8A-65E919119B5C@.microsoft.com...
> Hi,
> I need to delete many records in a table based on some conditions in the
> where clause. This delete is taking plenty of time. How do I disable the
> logging of transactions?
> --
> Thanks and Regards,
> Prasanth|||Thanks, I will do with that.
"Uri Dimant" wrote:
> Hi
> You cannot disable logging during deletion as in your case.
> Divide the deletion into small transaction and try to run it. Also check out
> if there are some indexes defined on the table, try to drop them and after
> deletion to recreate them.
> "Prasanth" <Prasanth@.discussions.microsoft.com> wrote in message
> news:98AE9AA6-EF24-4334-AA8A-65E919119B5C@.microsoft.com...
> > Hi,
> >
> > I need to delete many records in a table based on some conditions in the
> > where clause. This delete is taking plenty of time. How do I disable the
> > logging of transactions?
> > --
> > Thanks and Regards,
> > Prasanth
>
>