Solved

manipulating data using ssis in sql server 2005

Posted on 2011-09-22
5
401 Views
Last Modified: 2012-05-12
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
Comment
Question by:blossompark
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 5

Expert Comment

by:zvytas
ID: 36581287
Expression:

convert(datetime, convert(char, getdate(), 106))
0
 

Author Comment

by:blossompark
ID: 36581380
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
 

Author Comment

by:blossompark
ID: 36581478
this works

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

0
 
LVL 5

Accepted Solution

by:
zvytas earned 500 total points
ID: 36581492
Sorry, forgot that SSIS expressions are a but limited. The following should work:

(DT_WSTR,30)(DT_DBDATE)getdate()
0
 

Author Closing Comment

by:blossompark
ID: 36581752
thanks for the update, previous one worked ...will stockpile yours for future use !!
0

Featured Post

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question