Just curious: why do you use a script task as a destination? What is your destination?

Maybe there are easier alternatives.

As long as you don't use any blocking transformations (sort, aggregate...

BIDS is meant to develop and debug the SSIS packages.

After that, you need to deploy the packages to a server which will run them.

You either deploy them to SQL Server...

Yes. You can also specify the filename.dat in the flat file...

I'm not 100% sure of the syntax.

Try EXECUTE AS 'dbo'

char, varchar and text are all character strings.

char has a fixed length, while varchar has a varying length.

text is also varying length, but is used for blob storage. It is...

First I would optimize the query a bit.

select distinct fiscalyear from VW_DIM_FISCAL_CALENDAR_PERIOD where periodid='201302'

I assume this only returns one single value? If yes, I would execute this query first and...

Use another data type instead of ntext?

Ntext is deprecated by the way.

It would be useful if you would supply us some sample data for the .dat file.

But as Luis suggested, a flat file destination will probably do the trick.

It might be useful if you also gave us the query...

(if you run the query and check the actual execution plan, it gives a hint about possible indexes)

I spotted at least 3 correct answers 😀

Why would you want to delete a transaction log?

You can still attach a database without a transaction log.

You really need to give more specific details.

What exactly do you mean with "processed"? Which transformations are you using?

Can you be a bit more specific?

Is the job scheduled with SQL Server Agent, or with another scheduler?

With which tool did you make the ETL job? If SSIS, did you...

Since you mention datatable, I assume you're using .NET?

Take a look at MARS (multiple active result sets):

Manipulating Data

http://stackoverflow.com/questions/6043685/can-we-return-a-multiple-result-set-in-stored-procedure-and-how-to-handle-it-in

SQLSophist (10/23/2013)

---

It occurs to me that the publication of this article violates the License terms of SQL Server, in particular the "DeWitt clause":

"BENCHMARK TESTING. You must obtain Microsoft's prior written...