Destination runs out of space => job never finishes

Hi,

I have developed a SSIS package that performs data cleansing before data is loaded into a DW. I'm using a Multicast transformation to load the cleansed data to both a production and a test environment.

A couple of time now, I have exprienced that the test environment runs out of disk space and can not grow the database file (I know - this should never happen, but tell that to the DBA :o).

For some reason this causes the package to hang - no error is returned and the job executing the package remains in "Executing job" status, meaning that the cube processing is never started. Shouldn't I get an error back when the disk runs out of space?

Regards,
SuneYes, you should. However, we rely on the provider returning the error from the SQL Server. If it doesn't then we have no way of knowing an error occurred.

Thanks,
Matt

desperate for help! PLEASE!

I installed the reporting service and everytime I start a new porject in
visual studio .net, I get a message saying that it is unable to load the
RSReportDesigner.config file.
I can't find any help online and I can't get into the beta newsgroup...
HELP PLEASE.
Thanks.
EmadI found out what was the problem. The evaluation version does NOT work on
windows XP only 2000. Strange!!!!
-Emad
"Emad Ibrahim" <iemad@.nospam.hotmail.com> wrote in message
news:uOw3uKI4DHA.632@.TK2MSFTNGP12.phx.gbl...
> I installed the reporting service and everytime I start a new porject in
> visual studio .net, I get a message saying that it is unable to load the
> RSReportDesigner.config file.
> I can't find any help online and I can't get into the beta newsgroup...
> HELP PLEASE.
> Thanks.
> Emad
>

desperate for help! PLEASE!

I installed the reporting service and everytime I start a new porject in
visual studio .net, I get a message saying that it is unable to load the
RSReportDesigner.config file.
I can't find any help online and I can't get into the beta newsgroup...
HELP PLEASE.
Thanks.
EmadI found out what was the problem. The evaluation version does NOT work on
windows XP only 2000. Strange!!!!
-Emad
"Emad Ibrahim" <iemad@.nospam.hotmail.com> wrote in message
news:uOw3uKI4DHA.632@.TK2MSFTNGP12.phx.gbl...

quote:

---

> I installed the reporting service and everytime I start a new porject in
> visual studio .net, I get a message saying that it is unable to load the
> RSReportDesigner.config file.
> I can't find any help online and I can't get into the beta newsgroup...
> HELP PLEASE.
> Thanks.
> Emad
>

---

Design/load question

Hi. I am redesigning a database to be more normalized. There are 4 columns that appear in many of the tables that I plan to add to a new table. Here is what the database looks like now:

table1
uniquecolumn1
uniquecolumn2
uniquecolumn3
samecolumn1
samecolumn2
samecolumn3
samecolumn4
uniquecolumn4
etc.

table2
uniquecolumn1
uniquecolumn2
uniquecolumn3
samecolumn1
samecolumn2
samecolumn3
samecolumn4
uniquecolumn4
etc.

table3
uniquecolumn1
uniquecolumn2
uniquecolumn3
samecolumn1
samecolumn2
samecolumn3
samecolumn4
uniquecolumn4
etc.

If I add a 4th table with the columns that are the same in each of the tables, I'd have this:

table1
uniquecolumn1
uniquecolumn2
uniquecolumn3
uniquecolumn4
etc.

table2
uniquecolumn1
uniquecolumn2
uniquecolumn3
uniquecolumn4
etc.

table3
uniquecolumn1
uniquecolumn2
uniquecolumn3
uniquecolumn4
etc.

table4
samecolumn1
samecolumn2
samecolumn3
samecolumn4

To link table4 to the other tables, I'd define foreign keys on table4 that reference the primary keys of tables 1-3. What I'd like to avoid is having a composite key on table4 made up of the primary keys of tables 1-3. That doesn't sound correct. How do I do this? Also when it comes time to load data, I know that I need to populate tables 1-3 first, but how do I then uniquely identify the rows in tables 1-3 that must go into table4? Thanks for the help!

Byron,

Could you please be more specific on the column names for each table?

Steve

|||Steve,

Thanks for the response. Here's what you asked for:

LodgingSummary (table1)
LodgingSummaryID
LoadTransactionCode
AccountNumber
PostingDate
TransactionReferenceNumber
SequenceNumber
NoShowIndicator
CheckInDate
DailyRoomRate
TotalOtherCharges
TotalTaxAmount
TotalFoodBeverageCharges
TotalPrepaidExpenses

CardTransaction (table2)
LoadTransactionCode
AccountNumber
PostingDate
TransactionReferenceNumber
SequenceNumber
Period
AcquiringBIN
CardAcceptorID
SupplierName
SupplierCity
SupplierStateProvinceCode

FleetService (table3)
FleetServiceID
LoadTransactionCode
AccountNumber
PostingDate
TransactionReferenceNumber
SequenceNumber
PurchaseType
FuelType
FuelUnitMeasureCode
FuelQuantity
FuelUnitCost
FuelGrossAmount

AccountInfo (table4)
AccountNumber

PostingDate

TransactionReferenceNumber

SequenceNumber

|||

Byron,

CREATE TABLE AccountInfo
(
AccountNumber (PK)
PostingDate
TransactionReferenceNumber
SequenceNumber
)

CREATE TABLE CardTransaction
(
LoadTransactionCode (PK)
LodgingSummaryID(FK)--allow nulls
FleetServiceID (FK) --allow nulls
Period
AcquiringBIN
CardAcceptorID
SupplierName
SupplierCity
SupplierStateProvinceCode
)

CREATE TABLE LodgingSummary
(
LodgingSummaryID (PK)
AccountNumber
NoShowIndicator
CheckInDate
DailyRoomRate
TotalOtherCharges
TotalTaxAmount
TotalFoodBeverageCharges
TotalPrepaidExpenses
)

CREATE TABLE FleetService
(
FleetServiceID (PK)
PurchaseType
FuelType
FuelUnitMeasureCode
FuelQuantity
FuelUnitCost
FuelGrossAmount
)

|||

CREATE TABLE AccountInfo
(
AccountNumber (PK)
PostingDate
TransactionReferenceNumber
SequenceNumber
)

If you do this, then that person can never use this account again. Better off making a surrogate for this data:

CREATE TABLE AccountInfo
(
AccountInfoId int identity(1,1) primary key, --I don't care about the value of this, it is just a surrogate for:

AccountNumber
PostingDate
TransactionReferenceNumber
SequenceNumber
UNIQUE (accountNumber, PostingDate, TransactionReferenceNumber, SequenceNumber)
)

I find that I am a bit wary of the data here. Is AcctNumber and TransactionRefNumber not unique in and of itself? Same with it and SequenceNumber. Make sure that your alt key(s) really define with is actually unique, and not just a blob like this (unless the same tranNumber and sequenceNumber might be repeated for the same account, but just on different days.

I am rarely happy when a date is part of a key that is not representing an Event of some sort (like if I had a table that recorded when I got gas for my car, it would have a date as the key.) I don't know your data, so I am not sure of course :)

|||Thanks for the response, Louis. I added my responses is blue.

"CREATE TABLE AccountInfo

(
AccountNumber (PK)
PostingDate
TransactionReferenceNumber
SequenceNumber
)

If you do this, then that person can never use this account again. Better off making a surrogate for this data:"

This is correct. There will be multiple transactions for the same account number.

"CREATE TABLE AccountInfo
(
AccountInfoId int identity(1,1) primary key, --I don't care about the value of this, it is just a surrogate for:

AccountNumber
PostingDate
TransactionReferenceNumber
SequenceNumber
UNIQUE (accountNumber, PostingDate, TransactionReferenceNumber, SequenceNumber)
)

I find that I am a bit wary of the data here. Is AcctNumber and

TransactionRefNumber not unique in and of itself? Same with it and

SequenceNumber. Make sure that your alt key(s) really define with is

actually unique, and not just a blob like this (unless the same

tranNumber and sequenceNumber might be repeated for the same account,

but just on different days.

I am rarely happy when a date is part of a key that is not

representing an Event of some sort (like if I had a table that recorded

when I got gas for my car, it would have a date as the key.) I don't

know your data, so I am not sure of course :)"

There are many more tables that could have transactions related to travel, lodging, purchases, etc. These are all in different tables according to the data spec I have to live with. TransactionRefNumber should be unique, but there is no guarantee. When you said:

"UNIQUE (accountNumber, PostingDate, TransactionReferenceNumber, SequenceNumber)"

What is this? Are you creating a unique constraint here? Thanks.|||One more thing, please. Now when I want to load my values into my newly normalized table (LodgingSummary), how do I select each row individually? LodgingSummary will have the primary key of AccountInfo as a foreign key. Let's look at a real table:

Table: LodgingSummary (the newly normalized table)
LodgingSummaryID (PK)
AccountInfoID (FK) <-- This is the primary key of AccountInfo
NoShowIndicator
CheckInDate
DailyRoomRate
TotalOtherCharges
TotalTaxAmount
TotalFoodBeverageCharges
TotalPrepaidExpenses

Table: AccountInfo (the table I'm "normalizing" to)
AccountInfoID (PK)
AccountNumber <--moved from tbl LodgingSummary to tbl AccountInfo
PostingDate <--moved from tbl LodgingSummary to tbl AccountInfo
TransactionReferenceNumber <--moved from tbl LodgingSummary to tbl AccountInfo
SequenceNumber <--moved from tbl LodgingSummary to tbl AccountInfo

After the load, there will be a 1:1 relationship between the rows in the two tables (primary key to foreign key). My datasource for the load is the original de-normalized table:

Table load_LodgingSummary
LodgingSummaryID (PK)
AccountNumber
PostingDate
TransactionReferenceNumber
SequenceNumber
NoShowIndicator
CheckInDate
DailyRoomRate
TotalOtherCharges
TotalTaxAmount
TotalFoodBeverageCharges
TotalPrepaidExpenses

Here's my current code to load the normalized table:

[CODE]
set identity_insert lodgingsummary on
insert into lodgingsummary
(
LodgingSummaryID,
AccountInfoID,
LoadTransactionCode,
NoShowIndicator,
CheckInDate,
DailyRoomRate,
TotalOtherCharges,
TotalTaxAmount,
TotalFoodBeverageCharges,
TotalPrepaidExpenses
)
select
LodgingSummaryID,
(select ai.accountinfoID AccountInfoID from accountinfo ai,
load_LodgingSummary ll
where ai.accountnumber=ll.accountnumber and ai.postingdate=ll.postingdate
and
ai.TransactionReferenceNumber=ll.TransactionReferenceNumber
and ai.sequencenumber=ll.sequencenumber),
LoadTransactionCode,
NoShowIndicator,
CheckInDate,
DailyRoomRate,
TotalOtherCharges,
TotalTaxAmount,
TotalFoodBeverageCharges,
TotalPrepaidExpenses
from load_lodgingsummary
set identity_insert lodgingsummary off
[/CODE]

The subquery in the INSERT will return more than 1 AccountInfoID. Is there any way to write a statement that will load each row individually or do I need to build a recordset and individually insert each row of the recordset?

design question

I've got a file I need to load into a table. The file contains
modifiers to a fast food order. For example, if you ordered a burger
and asked for no onions, the no onions request would be in the modifier
file.

So, the problem is there are "fake" duplicates in the file. What I
mean is this:
The file contains a Header ID, Detail ID and Modifier ID. Sometimes
the Modifier ID is duplicated for a certain detail record. But...it's
not a true duplicate. The record also contains what is modified. So,
the "No Onions" record might have a modifier ID of 1. For the same
detail line, there might be another Modifier record of "Add Cheese".
But that will also have the Modifier ID of 1. It doesn't happen very
often. And yes, it should be fixed in the program that creates the
files. But that's not happening.

The primary key on the table (I'm not allowed to change it) is the
Header ID, Detail ID, Modifier ID, Store #, and Business Date.

What I've done is moved the duplicates to a temporary file and inserted
the rest of the records. Next, I'm assigning new Modifier IDs to these
"duplicated" records, and then inserting them. This is working fine
for me up to a point. The problem is when the file is loaded twice.
These "duplicated" records are inserted again. And now they really are
duplicates, even though they have different modifier IDs. And no, I
don't expect the file to be loaded twice, but you just never know.
This is bugging me and I'm not really sure of a way to get around it.
I thought I'd throw it to the group here to see if anyone has run into
this before. Any ideas are appreciated.

Thanks,
JenniferJennifer wrote:

Quote:

Originally Posted by

I've got a file I need to load into a table. The file contains
modifiers to a fast food order. For example, if you ordered a burger
and asked for no onions, the no onions request would be in the modifier
file.
>
So, the problem is there are "fake" duplicates in the file. What I
mean is this:
The file contains a Header ID, Detail ID and Modifier ID. Sometimes
the Modifier ID is duplicated for a certain detail record. But...it's
not a true duplicate. The record also contains what is modified. So,
the "No Onions" record might have a modifier ID of 1. For the same
detail line, there might be another Modifier record of "Add Cheese".
But that will also have the Modifier ID of 1. It doesn't happen very
often. And yes, it should be fixed in the program that creates the
files. But that's not happening.
>
The primary key on the table (I'm not allowed to change it) is the
Header ID, Detail ID, Modifier ID, Store #, and Business Date.
>
What I've done is moved the duplicates to a temporary file and inserted
the rest of the records. Next, I'm assigning new Modifier IDs to these
"duplicated" records, and then inserting them. This is working fine
for me up to a point. The problem is when the file is loaded twice.
These "duplicated" records are inserted again. And now they really are
duplicates, even though they have different modifier IDs. And no, I
don't expect the file to be loaded twice, but you just never know.
This is bugging me and I'm not really sure of a way to get around it.
I thought I'd throw it to the group here to see if anyone has run into
this before. Any ideas are appreciated.
>
Thanks,
Jennifer

You could use DTS or Integration Services or some other ETL tool to
manipulate the data as it is loaded. Or you could load the data to a
working table and then populate your actual table using an INSERT
statement. Those are the two most common options for complex data
loads.

--
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
--|||"Jennifer" <J.Evans.1970@.gmail.comwrote in message
news:1167769570.931558.212480@.n51g2000cwc.googlegr oups.com...

Quote:

Originally Posted by

I've got a file I need to load into a table. The file contains
modifiers to a fast food order. For example, if you ordered a burger
and asked for no onions, the no onions request would be in the modifier
file.
>
So, the problem is there are "fake" duplicates in the file. What I
mean is this:
The file contains a Header ID, Detail ID and Modifier ID. Sometimes
the Modifier ID is duplicated for a certain detail record. But...it's
not a true duplicate. The record also contains what is modified. So,
the "No Onions" record might have a modifier ID of 1. For the same
detail line, there might be another Modifier record of "Add Cheese".
But that will also have the Modifier ID of 1. It doesn't happen very
often. And yes, it should be fixed in the program that creates the
files. But that's not happening.
>
The primary key on the table (I'm not allowed to change it) is the
Header ID, Detail ID, Modifier ID, Store #, and Business Date.
>
What I've done is moved the duplicates to a temporary file and inserted
the rest of the records. Next, I'm assigning new Modifier IDs to these
"duplicated" records, and then inserting them. This is working fine
for me up to a point. The problem is when the file is loaded twice.
These "duplicated" records are inserted again. And now they really are
duplicates, even though they have different modifier IDs. And no, I
don't expect the file to be loaded twice, but you just never know.
This is bugging me and I'm not really sure of a way to get around it.
I thought I'd throw it to the group here to see if anyone has run into
this before. Any ideas are appreciated.
>
Thanks,
Jennifer
>

Three possibilities:
1: Pre-process the entire file before loading, re-numbering any duplicates
found in a predictable manner so a second load would generate the same key
values and all duplicates would be rejected.
2. Use the "Modifier" text as part of a virtual primary key during the
secondary load.
3. Learn to live with your duplicates and use select distinct to "roll up"
multiple 'Add Cheese' requests.