October 1, 2009 at 6:38 am
I have a text file that I need to import. The text file is named '20090930_Virtual_Daily_Extract_test_delimited.txt'
My first step - Using an SSIS package, I need to strip out the date (20090930) convert it to (2009-09-30) and then insert it into a date field.
I'm new to SSIS and have no idea how to do this...
October 1, 2009 at 7:08 am
You have more issues, I think. For instance, is this the only file in the DIR you read it from, or is this the most recent file, 1 of several?
But OK, to extract the date I would simply use the T-SQL statement SELECT CONVERT(datetime,LEFT('20090930abcd', 8),111); this can be done in an ExecuteSQL Task. There are many other ways to do this.
I hope you know how to read the name of the file and put it in a package variable?
Greetz,
Hans Brouwer
October 1, 2009 at 9:21 am
no I don't know how to do that can you explain?
October 2, 2009 at 5:59 am
I'm fairly confident that the format you have, if imported as string not integer, will directly convert to a SQL Server DateTime data type.
CREATE TABLE #date (theDate DATETIME)
INSERT INTO #date
(
theDate
)
VALUES
(
'20090930'
)
SELECT
theDate
FROM
#date AS D
DROP TABLE #date
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 5, 2009 at 1:37 am
Have a look here, you'll probly learn from this article:
http://www.sqlservercentral.com/articles/SSIS/67871/
Greetz,
Hans Brouwer
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply