How to add Dynamic column in the Flatfile Source?

Question

How to add Dynamic column in the Flatfile Source?

Viewing 15 posts - 16 through 30 (of 38 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259197 More actions · Answer 1

OK. According to your original question, the package was already failing before you added this new column.

That issue needs to be solved first.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Koen Verbeeck SSC Guru Points: 259197 More actions · Answer 2

Phil Parkin (11/12/2014)
arunnrj87 (11/12/2014)
I have added by going to DataFlow Task -->Flatfile Source -- >Right Click Show Advanced Editor-->
Component Properties -- > Under Custom Properties-->In FileNameColumnName--> i have five the Column Name as FtpDate and then Clicked Ok
Thats all i have done.
This is not the way we suggested. Leave the flat file source untouched. Add a derived column after the flat file source.

It is exactly what I suggested though 😉

You add a column containing the filename of the flat file using that little trick, and then you retrieve the date from that filename.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Phil Parkin SSC Guru Points: 247180 More actions · Answer 3

Koen Verbeeck (11/12/2014)
Phil Parkin (11/12/2014)
arunnrj87 (11/12/2014)
I have added by going to DataFlow Task -->Flatfile Source -- >Right Click Show Advanced Editor-->
Component Properties -- > Under Custom Properties-->In FileNameColumnName--> i have five the Column Name as FtpDate and then Clicked Ok
Thats all i have done.
This is not the way we suggested. Leave the flat file source untouched. Add a derived column after the flat file source.
It is exactly what I suggested though 😉
You add a column containing the filename of the flat file using that little trick, and then you retrieve the date from that filename.

I should have read more carefully.

I was thinking you were suggesting the usual FEL --> SSIS variable --> Derived Column method.

Koen Verbeeck SSC Guru Points: 259197 More actions · Answer 4

Phil Parkin (11/12/2014)
I was thinking you were suggesting the usual FEL --> SSIS variable --> Derived Column method.

That's an option as well, but this trick is more for the lazy people 😀

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

arunnrj87 Mr or Mrs. 500 Points: 565 More actions · Answer 5

No it was running perfectly and after adding the new column into the database i got error.

arunnrj87 Mr or Mrs. 500 Points: 565 More actions · Answer 6

Ok i have added the Derived column and what Expression i need to give ..

arunnrj87 Mr or Mrs. 500 Points: 565 More actions · Answer 7

Hi all I have addedd the Derived Column and added the New column name

In that what Expression i need to give for getting the FIlename Date

Like ''ACTEST00001_20141111_0408_SV_APPT.Txt'' this is the File name and i need only the date from this file name ,so what kind of Expression i need to give pls suggest me !!

Koen Verbeeck SSC Guru Points: 259197 More actions · Answer 8

Do you need the date only or the time portion as well?

Start with SUBSTRING...

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

arunnrj87 Mr or Mrs. 500 Points: 565 More actions · Answer 9

arunnrj87

Mr or Mrs. 500

Points: 565

November 12, 2014 at 3:34 am

#1757881

I nend only the date present in that file name.

Koen Verbeeck SSC Guru Points: 259197 More actions · Answer 10

Try the following:

(DT_DBDATE)(SUBSTRING(myFileName,13,4) + "-" + SUBSTRING(myFileName,17,2) + "-" SUBSTRING(myFileName,19,2))

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

arunnrj87 Mr or Mrs. 500 Points: 565 More actions · Answer 11

This Expression i have already used and we cannot uss the SUBSTRING in date datatype.

THis is Comming Error.

Please suggest some other Expressions.

Which Extract only the date

Koen Verbeeck SSC Guru Points: 259197 More actions · Answer 12

arunnrj87 (11/12/2014)
This Expression i have already used and we cannot uss the SUBSTRING in date datatype.
THis is Comming Error.
Please suggest some other Expressions.
Which Extract only the date

This makes no sense.

The column that contains the filename does not have a date data type, it is a string.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

arunnrj87 Mr or Mrs. 500 Points: 565 More actions · Answer 13

C:\Clients\BlackBook\BlackBookMarketing\SQLScripts\AuthenticomDailyProject\Samplefiles

In this path i have the files and the filename is

ACTEST00001_20141111_0408_INV.TXT

How to use this Expression

(DT_DBDATE)(SUBSTRING(myFileName,13,4) + "-" + SUBSTRING(myFileName,17,2) + "-" SUBSTRING(myFileName,19,2))

to pull the above file name.

I have give Column Name FtpDate in the Derived Column.

Koen Verbeeck SSC Guru Points: 259197 More actions · Answer 14

If you have the full path in the file name column, then of course you don't start your count for the substring at 13, but at 100 (if I counted correctly).

Another option is to use FINDSTRING to find the first occurrence of an underscore in the filepath and then start your substring expression from there.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Phil Parkin SSC Guru Points: 247180 More actions · Answer 15

Just a quick check. Can you confirm the data type (in SQL Server) of the new date column please?