For troubleshooting this, I would suggest logging onto the server console with the same account that the SQL Agent uses. Then execute the script in the context of that...

Another option would be to use the Data Flow Script Component. With this component you could employ regular expressions to extract and transform the numeric data and load it...

The usual suspect in these cases is the permissions of the Windows account under which the job is executing. When you run the package interactively, it is executing with...

Here is an approach you might explore - although I don't know efficient it would be with your volume of data. Also, this assumes that your Subxx records have...

priya__

You're welcome. It was a slow day at the office and it was an interesting little exercise. I'm glad you were able to modify the SQL to better...

You have to include the WHILE loop to update the temp table with a new "batch" number at each break in the account number.

CREATE TABLE [dbo].[#test3](

...

Replace the final SELECT query with this query. It uses a CTE (Common Table Expression) and the ROW_NUMBER function.

WITH NoDuplicates AS

(

select Acntno,date1,Emplno,Type,ACtion, min(recid) as recid, batch

from #test3

group by...

OK - try this. It's not the most efficient SQL, it uses a WHILE loop, so you may have to tweak it if performance is an issue. ...

Oops, sorry, that first row is excluded by the INNER JOIN. Change that to a RIGHT JOIN and it will appear.

It's this line in my sample code:

"where b.recid = 1 -- always use first record"

Here is a SQL approach using a self joined temp table:

CREATE TABLE [dbo].[#test3](

[Acntno] [nvarchar](14) NULL,

...

You could use the Script Component for this. It would examine each row and compare it to the values of the previous row to determine if it's a dup....

You can use a Script Task in your Control Flow to change the value of a variable. Define your variable as ReadWrite In the script task properties dialog. ...

OK then, a few more questions:

- Do the source files always have the same name?

- Are there always the same number of source files with unique formats?

-...

1 - give the derived column a new name

2 - use this expression:

(YEAR(your_Datetime_field) * 10000) + (MONTH(your_Datetime_field) * 100) + DAY(your_Datetime_field)

3 set the data type = four-byte signed integer [DT_I4]

4...