Change the date in ssis package for XML file

Question

1.00/5 (1 vote)

See more:

Hi Developers, I am working on SSIS in my XML file I have reading dates like 2013-08-02, 2013-08-4, 2013-08-05 but I have to change the data date to from last two days, What I mean is that the data date stamp should be changed to 2017-05-21,2017-05-22,2017-05-22.

What I have tried:

what I was done is added a derived column in the SSIS package and changed the expression with GETDATE() in this case I am getting only today date, But I need to change data from last two days. Can anyone help me?

Posted 23-May-17 10:05am

Akhil Madivada

Add a Solution

Comments

Maciej Los 24-May-17 2:42am

Sorry, but you question is unclear. 22 may 2017 is counted twice. Can you explain why? Can you share your sample data?

Akhil Madivada 24-May-17 11:04am

Thank you for your reply, Here is my sample XML file data with timestamp,
<Reading TimeStamp="2013-08-02 03:45:00" RawReading="229.9"/>
<Reading TimeStamp="2013-08-03 00:00:00" RawReading="0"/>
<Reading TimeStamp="2013-08-04 18:30:00" RawReading="0"/>
<Reading TimeStamp="2013-08-07 20:15:00" RawReading="0"/>

I have to change the timestamp date from 2013-08-02 to 2017-05-21, 2013-08-03 to 2017-05-22, 2013-08-04 to 2017-05-23 and 2013-08-07 to 2017-05-24. this changes I have to do by using SSIS and my destination is OLEDB.

Maciej Los 24-May-17 11:19am

And what have you done till now?

Akhil Madivada 24-May-17 11:31am

created SSIS packages in data flow connecting XML source and the OLEDB destination and in the middle of this two transformations I placed a derived column transformation and added an expression GETDATE() in this case the dates in the data are changing to today's date but I but to change in a sequence from last days.

Maciej Los 24-May-17 12:04pm

Have you seen this: Date format Issues in SSIS Packages between MM/dd/yyyy & dd/MM/yyyy – Data Access / SQL BI Technologies[^]

BinnyVishwant 24-May-17 4:17am

could you explain it clearly, why you have repeated twice 2017-05-22,2017-05-22.

Akhil Madivada 24-May-17 11:05am

Thank you for your reply, Here is my sample XML file data with timestamp,
<Reading TimeStamp="2013-08-02 03:45:00" RawReading="229.9"/>
<Reading TimeStamp="2013-08-03 00:00:00" RawReading="0"/>
<Reading TimeStamp="2013-08-04 18:30:00" RawReading="0"/>
<Reading TimeStamp="2013-08-07 20:15:00" RawReading="0"/>

I have to change the timestamp date from 2013-08-02 to 2017-05-21, 2013-08-03 to 2017-05-22, 2013-08-04 to 2017-05-23 and 2013-08-07 to 2017-05-24. this changes I have to do by using SSIS and my destination is OLEDB.

BinnyVishwant 25-May-17 9:13am

Here is my take:

i have given you some sample format
Sql:

declare @somedaysago datetime
declare @now datetime
set @now = '2013-08-02'
set @somedaysago = dateadd(day,1388,@now)

select @now, @somedaysago

select dateadd(day, 1388, @now)

SSIS:

DATEADD("dd", 1388, GETDATE())

The equivalent SSIS expression is DATEADD("day", 1388, GETDATE()).
The keywords for the "datepart" argument in SSIS are not the same as in T-SQL

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)