Package failure still causing partial load

I have a package that is failing because of a truncation error. Now, by default (and I leave this for ALL my packages) if one row fails processing the entire package should fail and nothing gets loaded into db. But instead I am actually getting a partial db load.

I have confirmed the "Rows per btach" value (blank) and the "Maximum insert commit size" value (0) for the OLE DB Destination Editor so I have no idea what is going on. Are there any other properties I should be checking?

Thanks.

Jason

Why are you surprised that there is a partial load? You'll have to set MICS to equal to or greater than the number of rows coming into the source to have it such that if one row is bad, the whole batch is aborted.

The settings you have now are likely committing each row as they are inserted.|||According to the documentation, a "value of 0 indicates that all data is committed in a single batch after all rows have been processed". This, to me, tells me that all rows will be written or none. Since I am getting a truncation error, should I not be getting zero rows written?|||Well, I'm not so sure that SQL Server can accept an arbitrarily large bulk load. That is, I believe there's a limit to the size of the batch, and if it's exceeded, it will have to issue a commit or fail. I'm not sure.

You could redirect error rows out of the OLE DB command and try to see where that error occurs (row number or something).|||

Thanks for the responses Phil.

I know exactly where the error is happening. Just that in the past (with other packages) the execution loaded everything or nothing. (No matter the number of rows in the source.) It is just that the behavior for this package is not what I am used to and I cannot figure out why it is doing a partial load when the settings (as far as I can tell) are telling it not to.

- Jason

|||

To be honest, I have been playing with those 2 properties in the OLE DB Destination, so I can get bigger batches but I only can get around 9K-10K rows per commit as maximum. Not sure if SSIS or Bulk_insert look at the available resources and decided what value to use. I have no gotten the time to dig into that.

|||

Rafael Salas wrote:

To be honest, I have been playing with those 2 properties in the OLE DB Destination, so I can get bigger batches but I only can get around 9K-10K rows per commit as maximum. Not sure if SSIS or Bulk_insert look at the available resources and decided what value to use. I have no gotten the time to dig into that.

I think that SQL Server can only handle a batch size of 256 MB.

http://msdn2.microsoft.com/en-us/library/ms143432.aspx

Package Configuration

Hi All,

I have couple of properties in my package Configuration

(1) Lets me know the Folder path - where I have file processing

(2) Connection String

When working in the Development machine, I am able to play around these values and test the application. I have tried both XML Configuration and SQL Server Configuration

But during deployment in the Test Server, the values in the Configuration are not getting reflected automatically and takes the values assigned during deployment

I tried importing the XML file and it works. But the values are not persistant.After running the package, the value gets resetted to the old value. Every time I run my package, I am reassingning my package variables.

Be it XML Configuration File or SQL Server, I would like to have the variables pulled out from the Configuration I am setting during the deployment. the values should be persistant and should refer the updated values.

Any help will be highly appreciated...thanks

When you move to the test server, do you:
- copy the configuration file to the server
- ensure that SSIS knows to look for the file in the correct place on the new server
- have the configuration file listed in the Package Configurations list? (Right-click Control flow background, select Package Configurations)|||

Phil, Thanks for sharing your thought.

I have configured the XML Package Configuration File during my development and placed the file along with my SSIS Packages in the same folder. But my IS is not picking up the Configuraiton file in my test Environment (new Server.

Any suggestions on how to solve this issue.

Thanks

Evan

|||

Web Explorer wrote:

Phil, Thanks for sharing your thought.

I have configured the XML Package Configuration File during my development and placed the file along with my SSIS Packages in the same folder. But my IS is not picking up the Configuraiton file in my test Environment (new Server.

Any suggestions on how to solve this issue.

Thanks

Evan

Pretty much I have nothing more to add other than the list of things to check above. Is the directory path to the config file exactly the same on the test server compared to the dev server?|||When you open the package on the test server, do you get any warnings? (CTRL-E to open that window if it isn't already)|||

no....I purposely wanted to have the folder structure different in Dev and test Server, because during the actual deployment, we should have the flexibility to change the folder structure accoding to the environment.

I am sure If the directory matches IS is going to pick our files. But I want my setting in the XML Config file to be referred in the test server

Thanks

Evan

|||I dont get any warnings Error. When the package runs, it says that the for each container is empty as its trying to refer a different folder|||

Web Explorer wrote:

no....I purposely wanted to have the folder structure different in Dev and test Server, because during the actual deployment, we should have the flexibility to change the folder structure accoding to the environment.

I am sure If the directory matches IS is going to pick our files. But I want my setting in the XML Config file to be referred in the test server

Thanks

Evan

Right, but you have to understand that SSIS needs to know where to look for the XML configuration file. The path specified cannot change, UNLESS you use an environment variable to define the path to the XML configuration file.|||There are obviously two things going on here.

1 - Can SSIS find the XML configuration file
2 - When it does find the configuration file, is it picking up the values contained within

Assuming SSIS picks up the configuration file in DEV and sets the value accordingly, then it should work in TEST provided it can find the configuration file.|||

Thanks a lot for your useful thoughts.

Finally got this working.

Configured a XML Configuration File and Also configured an Environment Variable.

After deploying the XML configuration file in the test server and configuring the Environment variable, a re-boot is necessary.

I found that after restarting the machine, the test server started to work perfectly.

Thanks a ton for your timely help.

Regards,

Evan

|||Check out this thread of similar problems, for an explanation, perhaps of what was going on with your issue. Specifically my last post.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1347857&SiteID=1

Package Configuration

Hi All,

I have couple of properties in my package Configuration

(1) Lets me know the Folder path - where I have file processing

(2) Connection String

When working in the Development machine, I am able to play around these values and test the application. I have tried both XML Configuration and SQL Server Configuration

But during deployment in the Test Server, the values in the Configuration are not getting reflected automatically and takes the values assigned during deployment

I tried importing the XML file and it works. But the values are not persistant.After running the package, the value gets resetted to the old value. Every time I run my package, I am reassingning my package variables.

Be it XML Configuration File or SQL Server, I would like to have the variables pulled out from the Configuration I am setting during the deployment. the values should be persistant and should refer the updated values.

Any help will be highly appreciated...thanks

When you move to the test server, do you:
- copy the configuration file to the server
- ensure that SSIS knows to look for the file in the correct place on the new server
- have the configuration file listed in the Package Configurations list? (Right-click Control flow background, select Package Configurations)|||

Phil, Thanks for sharing your thought.

I have configured the XML Package Configuration File during my development and placed the file along with my SSIS Packages in the same folder. But my IS is not picking up the Configuraiton file in my test Environment (new Server.

Any suggestions on how to solve this issue.

Thanks

Evan

|||

Web Explorer wrote:

Phil, Thanks for sharing your thought.

I have configured the XML Package Configuration File during my development and placed the file along with my SSIS Packages in the same folder. But my IS is not picking up the Configuraiton file in my test Environment (new Server.

Any suggestions on how to solve this issue.

Thanks

Evan

Pretty much I have nothing more to add other than the list of things to check above. Is the directory path to the config file exactly the same on the test server compared to the dev server?|||When you open the package on the test server, do you get any warnings? (CTRL-E to open that window if it isn't already)|||

no....I purposely wanted to have the folder structure different in Dev and test Server, because during the actual deployment, we should have the flexibility to change the folder structure accoding to the environment.

I am sure If the directory matches IS is going to pick our files. But I want my setting in the XML Config file to be referred in the test server

Thanks

Evan

|||I dont get any warnings Error. When the package runs, it says that the for each container is empty as its trying to refer a different folder|||

Web Explorer wrote:

no....I purposely wanted to have the folder structure different in Dev and test Server, because during the actual deployment, we should have the flexibility to change the folder structure accoding to the environment.

I am sure If the directory matches IS is going to pick our files. But I want my setting in the XML Config file to be referred in the test server

Thanks

Evan

Right, but you have to understand that SSIS needs to know where to look for the XML configuration file. The path specified cannot change, UNLESS you use an environment variable to define the path to the XML configuration file.|||There are obviously two things going on here.

1 - Can SSIS find the XML configuration file
2 - When it does find the configuration file, is it picking up the values contained within

Assuming SSIS picks up the configuration file in DEV and sets the value accordingly, then it should work in TEST provided it can find the configuration file.|||

Thanks a lot for your useful thoughts.

Finally got this working.

Configured a XML Configuration File and Also configured an Environment Variable.

After deploying the XML configuration file in the test server and configuring the Environment variable, a re-boot is necessary.

I found that after restarting the machine, the test server started to work perfectly.

Thanks a ton for your timely help.

Regards,

Evan

|||Check out this thread of similar problems, for an explanation, perhaps of what was going on with your issue. Specifically my last post.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1347857&SiteID=1

overflow of the data type while processing dimension

While trying to process a cube the error below came up while processing one of the dimensions.

The database is an ORACLE database running on a UNIX platform even though that piece of information would not be neccesary in solving this riddle.

I believe it has to do with date format differences on both database platforms. Is there anyone out there who has been able to resolve such a problem or point me out in the right direction.

See below for an excerpt of the log.

Start time: 30-Mar-07 13:04:47; End time: 30-Mar-07 13:05:02; Duration: 0:00:15
SQL queries 1
SELECT
DISTINCT
"T_0"."DEALER_CODE" "DIMM_SUBSCRIBER_DEALER_XREF0_0"
FROM "DLRPROFILE"."DIMM_SUBSCRIBER_DEALER_XREF" "T_0"
Processing Dimension Attribute 'ENTRY DATE' failed. 1 rows have been read.
Start time: 30-Mar-07 13:04:47; End time: 30-Mar-07 13:06:06; Duration: 0:01:19
SQL queries 1
SELECT
DISTINCT
"T_0"."ENTRY_DATE" "DIMM_SUBSCRIBER_DEALER_XREF0_0"
FROM "DLRPROFILE"."DIMM_SUBSCRIBER_DEALER_XREF" "T_0"
Error Messages 1
Errors in the back-end database access module. OLE DB reported an overflow of the data type for column 0. Errors in the OLAP storage engine: An error occurred while the 'ENTRY DATE' attribute of the 'DIMM SUBSCRIBER DEALER XREF' dimension from the 'DEALER_SALES_CONNECTION_ANALYSIS' database was being processed.
Processing Dimension Attribute 'REGION CODE' completed successfully. 10 rows have been read.
Start time: 30-Mar-07 13:04:47; End time: 30-Mar-07 13:06:06; Duration: 0:01:19

Please another question is there some sort of literature addressing SSAS and ORACLE database working in tandem.

I need Help.......

What is the data type of the column for the 'ENTRY DATE' attribute in Oracle compared with the data type that is setup for the Attribute in the dimension? It sounds like the attribute is using a smaller data type.