Frank,

I decided to stage data into STAGE_DDA_REV_ADJ table

because I need to see new COLUMN NAMES. Not File Source column names.

It's confusing.

So again, I still don't understand how I can implement...

So here is a sample of what I need to do

and how can I do it with just SSIS, no "Execute SQL" task?

I have loaded data from REVADJ.txt file.

Now...

Brandie,

You wrote:

On the other hand, there are other situations where you can't do what you need by using an Execute SQL Task. I always look at both situations first and...

But isn't a faster execution of code a good enough reason?

Plus you don't browse through tens of different SSIS task objects like "Conversion,Split,Merge,Lookup",etc.

It's all in one nice stored procedure.

I might...

Brandie,

The boss won't like it either.

It's not that he doesn't want to see too many "Execute SQL" tasks in SSIS package.

He doesn't want to call stored procedures from SSIS in...

No looping.

Just simple:

and so on.

I talked to SSIS developers in one of the big banks...

You're right!

I commented out NULL's and SELECT INTO worked.

SELECT

TIM.time_key --int

,CUS.cust_key --int ...

Hi Ken,

The size of the data file for BAP_PROD_2006

is 33 GB.

Ken,

How exactly SSIS helps to reduce bacth size

and what is that "batch size"?

What does it mean?

Could you tell a few...

Ken,

Sorry I didn't mention it earlier.

This was INSERT ... SELECT FROM ...kind of statement that filled up transaction log.

Because it was causing problems I killed the transaction

and troubleshooted the SELECT...

Hi Ken,

So Table scan is the worst.

RID is next to Table scan.

And the goal is to switch to Index scan.

Am I correct?

BAP_PROD_2006_Log.LDF (Transaction Log of BAP_PROD database)

was growing.

BAP_PROD_2006 is an...

So this Execution Plan I posted is not good enough?

Table definitions:

---------------

/*

cust_agrmnt_dim

cis_customer_Dim

service_type_dim

current_account_dim

BRANCH_DIM

*/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[cis_customer_Dim]') AND type in (N'U'))

BEGIN

CREATE TABLE [dbo].[cis_customer_Dim](

[TIME_KEY] [int] NOT NULL,

[CUST_KEY] [int] NOT NULL,

[CIS_KEY] [char](17) NOT...

Index on the second external table

is on CHAR(17) column. And the values are like

"0002!E!ESYSTE0001"

It's probably not very effective.

The first index on the first external table

BAP_PROD_2006.dbo.cust_agrmnt_dim.CUST_AGRMNT_NUM

is VARCHAR(22) but it's actually a...

So I added an index on the second "external" table - BAP_PROD_2006.dbo.cis_customer_Dim,

uncommented second JOIN with this table.

Ran the query... and it's hanging...more than 10 min and still running....

Oh-oh...

Now my SELECT COUNT(*)

with just one JOIN to external table

hangs up. It takes more than 4 min.

I cancelled query. I actually have two indexes on this table:

cust_agrmnt_dim

1. [CUST_AGRMNT_NUM]

2. [TIME_KEY]

[TIME_KEY]...