any ideas here guys..

After installing 'AccessDatabaseEngine.exe' from

http://www.microsoft.com/en-us/download/details.aspx?id=13255

I am able to see the columns of the excel file using excel connection manager..but while running the package..I am getting an error

'Error:...

Your solution worked for me.. Thanks a lot 🙂

ok..I tried almost everything..but the derived columns seems to be giving problem..so I have resorted to this:

I am creating a temp table..dumping the flat file as it is into it..and...

Data viewer on the Green pipeline is showing me all the records before that particular erroneous record

Yes, so there are no rows getting redirected on error output i.e. RED pipeline..and also the data viewer on Green pipeline is telling me where the error is

I checked it in the Data Viewer

Now instead of Date and time conversion..I tried following:

SUBSTRING(LTRIM(RTRIM([Date])),LEN(LTRIM(RTRIM([Date]))) - 3,LEN(LTRIM(RTRIM([Date])))) + "-" + SUBSTRING(LTRIM(RTRIM([Date])),1,FINDSTRING(LTRIM(RTRIM([Date])),"/",1) - 1) + "-" + SUBSTRING(LTRIM(RTRIM([Date])),FINDSTRING(LTRIM(RTRIM([Date])),"/",1) + 1,2)+" 00:00:00.000"

STILL NOT WORKING 🙁

Now instead of Date and time conversion..I tried following:

SUBSTRING(LTRIM(RTRIM([Date])),LEN(LTRIM(RTRIM([Date]))) - 3,LEN(LTRIM(RTRIM([Date])))) + "-" + SUBSTRING(LTRIM(RTRIM([Date])),1,FINDSTRING(LTRIM(RTRIM([Date])),"/",1) - 1) + "-" + SUBSTRING(LTRIM(RTRIM([Date])),FINDSTRING(LTRIM(RTRIM([Date])),"/",1) + 1,2)+" 00:00:00.000"

STILL NOT WORKING 🙁

I get the error...

Thank you very much guys..that helped a lot 🙂

Answers I have found so far:

Quickest way to move the data:

1) SSIS

2) DELETE FROM dbo.Table1 OUTPUT deleted.* INTO dbo.Table2

3) SELECT * INTO dbo.Table2 from dbo.Table2 where 1=0

Quickest way to count...

I completely agree with you..even I will never build anything like..from the moment when you realize that the table is going to be that big..one should immediately think about partitioning...

Thanks for the guidance mate..I had an interview today and the interviewer asked me the same questions..I answered it the best I could and suggested everything from count(*),@@rowcount,ranking functions to...

Works great..Thanks mate:-)

I solved it with friend's help..instead of CAST(@BLANK_COUNT AS VARCHAR(5)) it should be CAST(@BLANK_COUNT AS VARCHAR(10))