SSIS- timestamp issue - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

SSIS- timestamp issue

Author

Topic

machosql
Starting Member

5 Posts

Posted - 2007-08-22 : 13:44:14

HI,

i have a package which has several execute package tasks. this pkg takes the flat files and load it to different tables. in the table there is a column name for date. i want to fill the end date with the date of received flat files. THE FLAT FILE HAS A TIMESTAMP. how do i configure this in my package

machosql
Starting Member

5 Posts

Posted - 2007-08-23 : 15:13:23

no one has replied ?

pareshmotiwala
Constraint Violating Yak Guru

323 Posts

Posted - 2007-08-28 : 11:04:52

I was able to do it.
1. you have a control flow item.
2. Then in the data flow context, you will have to add a step called add a derived column( make that as a date/timestamp)
In the first column
you have to give the name of Derived Column.
with the next column as "Derived column" should have value
<add as a new column>
The next column is for expression: I put the value getdate()
The next column is data Type, I put database timestamp type.
Length,precision,scale and code page are left blank unless your target has different.

3. Ensure your target table has a provision for the new derived column.

See if this helps.
All the best, Paresh

Regards
Paresh Motiwala
Boston, USA

machosql
Starting Member

5 Posts

Posted - 2007-08-29 : 13:52:06

Thanx Motiwala
there is another solution for the prob

CREATE PROCEDURE GetFileDate(@FilePath varchar(200), @FileDate DateTime OUT) AS
SET NOCOUNT ON

DECLARE @CMD1 varchar(500)
DECLARE @CMD2 varchar(500)
DECLARE @FileDateStr varchar(50)

CREATE TABLE #OriginalFileList (
Col1 varchar(1000) NULL
)

SELECT @CMD1 = ''
SELECT @CMD2 = ''

SELECT @CMD1 = 'master.dbo.xp_cmdshell ' + char(39) + 'dir ' + @FilePath + char(39)

SELECT @CMD2 = 'INSERT INTO #OriginalFileList(Col1)' + char(13) +
'EXEC ' + @CMD1
EXEC (@CMD2)

DELETE FROM #OriginalFileList
WHERE COL1 IS NULL

DELETE FROM #OriginalFileList
WHERE COL1 LIKE '%Volume%'

DELETE FROM #OriginalFileList
WHERE COL1 LIKE '%Directory%'

DELETE FROM #OriginalFileList
WHERE COL1 LIKE '%<DIR>%'

DELETE FROM #OriginalFileList
WHERE COL1 LIKE '%bytes%'

SELECT @FileDateStr = LTRIM(SUBSTRING (Col1, 1, 20)) FROM #OriginalFileList

IF (IsDate(@FileDateStr) = 1)
SET @FileDate = convert(datetime, @FileDateStr)

DROP TABLE #OriginalFileList

SET NOCOUNT OFF

then call this proc as follows:

declare @FileDate datetime

exec GetFileDate 'C:\temp\1053.pdf', @FileDate out

select @FileDate

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources