• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 451
  • Last Modified:

manipulating data using ssis in sql server 2005

Hi,
I have an SSIS package that imprts data from a flat file into a database table.
During the import a column is added to the source data with the timestamp of when the package is run...
 ssis
this adds the timestamp to  the LoadDate column of the table
 loaddata
I want the time component of the timestamp to be all zeros
so in this example the entries for LoadDate would be
22/09/2011 00:00:00

I tried  putting the following in  the expression
dateadd(dd,0, datediff(dd,0,getdate())),
but it is not accepted.
any help appreciated thanks.
0
blossompark
Asked:
blossompark
  • 3
  • 2
1 Solution
 
zvytasCommented:
Expression:

convert(datetime, convert(char, getdate(), 106))
0
 
blossomparkAuthor Commented:
hi zvytas,
thanks for your input....
this results in the following error message
TITLE: Microsoft Visual Studio
------------------------------

Error at Data Flow Task [Derived Column [278]]: Attempt to find the input column named "datetime" failed with error code 0xC0010009. The input column specified was not found in the input column collection.

Error at Data Flow Task [Derived Column [278]]: Attempt to parse the expression "convert(datetime, convert(char, getdate(), 106)) " failed and returned error code 0xC00470A2. The expression cannot be parsed. It might contain invalid elements or it might not be well-formed. There may also be an out-of-memory error.

Error at Data Flow Task [Derived Column [278]]: Cannot parse the expression "convert(datetime, convert(char, getdate(), 106)) ". The expression was not valid, or there is an out-of-memory error.

Error at Data Flow Task [Derived Column [278]]: The expression "convert(datetime, convert(char, getdate(), 106)) " on "output column "LoadDate" (308)" is not valid.

Error at Data Flow Task [Derived Column [278]]: Failed to set property "Expression" on "output column "LoadDate" (308)".



------------------------------
ADDITIONAL INFORMATION:

Exception from HRESULT: 0xC0204006 (Microsoft.SqlServer.DTSPipelineWrap)

------------------------------
BUTTONS:

OK
------------------------------
0
 
blossomparkAuthor Commented:
this works

"(DT_DATE)   (DT_DBDATE) GetDate()".

0
 
zvytasCommented:
Sorry, forgot that SSIS expressions are a but limited. The following should work:

(DT_WSTR,30)(DT_DBDATE)getdate()
0
 
blossomparkAuthor Commented:
thanks for the update, previous one worked ...will stockpile yours for future use !!
0

Featured Post

[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now