All, I have a test sql server 64 bit box and a production 64 bit box. I have an SSIS package that takes data from various Teradata tables and puts them other Teradata tables that are used to build an SSAS cube. The only difference in terms of the SSIS run on the two boxes is that on test, the ETL process takes the data from the TD tables and stores them in a different TD database but ON the SAME server. In production, it takes the data from the TD tables and stores the manipulated data in tables in the SAME database. So for example, on test, the SSIS package takes data out of tedw.table1, manipulates and stores in dedw.ssas_table1 ON SAME SERVER. On the production sql server, the SSIS package takes data out of tedw.table1, manipulates it and stores in tedw.ssas_table1 sames database, same server. We have made sure both servers have same TD driver installed, they now have all the same version of Microsoft SQL Server components installed. But it only takes 30 minutes to run on test and over 4 hours on the production sql server. Any other ideas would be helpful to try and figure this out..it doesn't error out, it eventually finishes. It always gets stuck on one set of code,but the set of code is the same, so not sure why its running so much slower.MS

Just curious. The test TD tables (source and destination) have the same amount of data as the production TD tables? Also, is there any other database activity on the production server that is not in test? The ETL process may be waiting on locks.Russel Loski, MCT

When they run back to back (meaning run in test and then run in production), yes they should be the same counts. However, because I built in test last week, and built in production last night, the data is off by 470,000 records because the source tables change daily. The source and destination TD tables come from the same TD Server, and yes there are other processes running on that server, but we are running it on off hours so its minimal...in terms of other processes running on the SQL Server system, thats a good question...let me check with our SA. On the Production SQL Server SSIS box, it always gets stuck on a certain TD query...now at this point in the process everything is done on TD server, its just hte SSIS piece running on SQL Server box, until the SSAS cube builds which isn't until last in the process. MS

I don't know Teradata's architecture, but the fact that you're both reading and writing to the same database - in bulk - leads me to question whether the IO is a bottleneck. The two databases on your test machine may be hosted on two separate IO subsystems, allowing one to read only, and the other to write-only... sequentially. If you dual-purpose a single DB, you can thrash the disks as they attept to service mixed read and write requests to non-sequential disk areas. But I am not an expert on that! Your best diagnosis tool would be decomposition... Copy your original package, replacing the destination with a Row Count and see how fast that runs. Copy your original package again, replacing the destination with a Raw File Destination (on a non TD disk) and see how fast that runs. Create a new package that reads the Raw File and pushes it straight to the destination and see how fast that runs. You'll now have an idea how fast your extract/transform can run, and how much overhead inserting adds. Talk to me now on