Wrong date

Dec 19, 2009 at 9:14 AM

Hello Todd,

I have used File Properties Task(FPT) in control flow. I have done the following steps.

  1. I have dragged a FPT from control flow items to control flow Canvas
  2. I have dragged a DFT on control flow as well
  3. I have created two variable i.e. FileName and File creation date. The scope for the variables are package(this is the name of the package) the default value for both variables are null
  4. I have used excel file source
  5. One derived column (FileDate) and assigned the value(DT_DBTIMESTAMP)@[User::FileCreationDate]

Now it is inserting the wrong date i.e. 1899-12-30 00:00:00.000

Am i missing something?

and

It runs fine when you run first time. Second time it gives an error file does not exist. I do not understand why it had run first time and gives an error second time that file does not exist.

Thanks for your help.

Regards,

Mustafa

Coordinator
Dec 19, 2009 at 6:07 PM

You're omitting some information - I can't help you very much without it.

First, you can't be setting those variables of yours to "null" - unless you have them defined as Object types, which would mean you couldn't use them at all.  You have to have a default value in them - something!

Second, you haven't told me how you've used those variables in the FPT.  What properties of the FPT are you using, and how are you using your variables with it?

Dec 19, 2009 at 8:29 PM

Thank you so much Todd for your reply. 

I have created two variables

Name                   Scope               Datatype                value

FileCreation Date  Package            Date Time              Blank or 18/12/2009  (I have tried with this default value as well but it always insert this value not the file creation date, if I leave it blank then it insert 1899-30-12 value) 

FileName              Package            String                     test.xls 

I am using the following properties of FPT

Top of the window I have selected File Source name variable, then in varible drop down i have selected User::FileName

Second property i have used creation date/time. I have selected Read from dropdown list and into User::FileCreationDate.

If i leave value blank in FileName varible it runs fine only first time. Second time it gives error file does not exist. If i remove all the records from the table and run the package again it will run without any error, if there are records already in the table or you run the package second time it gives file does not exist error.

What am i missing here? The scope of variable is Package

 

Coordinator
Dec 21, 2009 at 3:55 PM

OK - thanks.  Now can you copy and paste the contents of the "Output" window here?  The FPT writes a lot of useful information there.

Dec 21, 2009 at 6:19 PM

Hello Todd,

Thank you so much for your reply. These are the contents of output window.

SSIS package "Package.dtsx" starting.

Information: 0x4004300A at Data Flow Task, SSIS.Pipeline: Validation phase is beginning.

Warning: 0x802092A7 at Data Flow Task, OLE DB Destination [26]: Truncation may occur due to inserting data from data flow column "PPCODE " with a length of 255 to database column "ppcode" with a length of 50.

Error: 0x0 at File Properties Task, File Properties Task: File '' does NOT exist. Task is configured to fail if the file is missing.

Task failed: File Properties Task

Warning: 0x80019002 at Package: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

SSIS package "Package.dtsx" finished: Failure.

Dec 22, 2009 at 6:36 AM

Thank you so much todd for your help.

For other users, the complete discussion is available on

http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/d36f4db6-925e-4515-8427-4f943393481a

regards,

Mustafa