[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 436
  • 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

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

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