April 3, 2008 at 9:10 am
Hi Guys,
Im new using SSIS and i just created a data flow that reads from multiple flat files (with a mutipleflatfiles conn manager) and inserts the files data to an sql server (with an ole db destination)
The thing is i need to insert the created date property of each file im importing and i dont know how to do this.
I've read i could do this with a script but im not very familiar to it.
Could someone help me there?
Thnx
April 9, 2008 at 10:29 pm
Yes, this is entirely possible by using a script component. When you create a script component, you'll notice an Inputs and Outputs window, which will allow you to create a new output column to store your time stamp for the file creation date/time.
' Create the FileInfo object using the filename. You would probably use a variable
' for this in a production package
Dim file As New System.IO.FileInfo("C:\test_file.txt")
' Assign the creationTime value to a time stamp column you create
Output0Buffer.TimestampColumn = file.CreationTime
hth,
Tim
Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices
April 10, 2008 at 9:03 am
hey tim plz could you give me some good links or pdfs to learn abt scripting ...beacuse i am not familiar with the script you wrote like dim as such stuff so could you please let me know where can i get good mtrl so that atleast i can know a bit ...i have a bit idea in programming but not to that extent..bcoz whenever i thought of usinf script task active x script i get woories asking code for someone else so plz let me know...
Thanks,
Chinna
Its the Journey which gives you Happiness not the Destination-- Dan Millman
April 10, 2008 at 9:27 am
Hi Tim,
I tried the code you have given above.Correct me if Im wrong, all I have to do is to add an output column in the script component, given name of output column is [Output1] and copy the script you have given above to the Script. I did the following and received an error stating
"Ouput0Buffer is not declared"
I tried replacing my output column name "Output1" to "Ouput0Buffer", but still gives me an error.Am I missing something here?
Thank You
[font="TimesNewRoman"] “I haven't failed, I've found 10,000 ways that don't work”........Thomas Alva Edison[/font]
April 10, 2008 at 10:46 am
hi lookup but how can i add new column to the script component i just had read variable write varibale and expression but i couldnt find anyhting to add a new output column could u plz let me know thkz in advance
Thanks,
Chinna
Its the Journey which gives you Happiness not the Destination-- Dan Millman
April 10, 2008 at 11:13 am
Hi Chinna,
To add an Output or input Column:
In Script Component[In data flow],go to Inputs and Outputs Tab,
Click on Output0,
Click on Output Column Folder.
Look below, you would be able to see Add Column Button.
Click on it.
Once New column added,Give a name and change the data type accordingly on your right.
In this case Im guessing the datatype should be DT_Date.
[font="TimesNewRoman"] “I haven't failed, I've found 10,000 ways that don't work”........Thomas Alva Edison[/font]
April 10, 2008 at 11:17 am
chinna (4/10/2008)
hi lookup but how can i add new column to the script component i just had read variable write varibale and expression but i couldnt find anyhting to add a new output column could u plz let me know thkz in advance
Chinna,
Im thinking you are looking at the SQL Script task in the Control Flow.Tim is talking about the Script Component in Data Flow tab.
Read my latest reply on the steps to set the Output Column.Im still waiting for Tim's reply on the doubts I have about the code.
[font="TimesNewRoman"] “I haven't failed, I've found 10,000 ways that don't work”........Thomas Alva Edison[/font]
April 10, 2008 at 11:23 am
yeah till now i was looking in the script component so i was a bit confused ....I have attched a screenshot so please let me know if that is the one and could you suggest me some good sites or links to learn scripting useful for active x script and script task...
Thanks,
Chinna
Its the Journey which gives you Happiness not the Destination-- Dan Millman
April 10, 2008 at 11:24 am
sorry i said component in the last post srry it is script task
yeah till now i was looking in the script task so i was a bit confused ....I have attched a screenshot so please let me know if that is the one and could you suggest me some good sites or links to learn scripting useful for active x script and script task...
Thanks,
Chinna
Its the Journey which gives you Happiness not the Destination-- Dan Millman
April 10, 2008 at 11:55 am
Chinna,
The attachment shows that you are looking at the Flat File connection mng.Tim is talking about the Script Component in Data Flow tab.
[font="TimesNewRoman"] “I haven't failed, I've found 10,000 ways that don't work”........Thomas Alva Edison[/font]
April 10, 2008 at 12:00 pm
thkz lookup i gotch u now....i found it ..could u recomend me some sites for scripting plzzz
Thanks,
Chinna
Its the Journey which gives you Happiness not the Destination-- Dan Millman
April 10, 2008 at 12:03 pm
Chinna,
Sorry to saw Im also pretty new to scripting and am also searching for good sites and tutorial.Should keep you posted if I find any .
[font="TimesNewRoman"] “I haven't failed, I've found 10,000 ways that don't work”........Thomas Alva Edison[/font]
April 10, 2008 at 12:07 pm
plzzzz ..my mail id is Kurraraghu@gmail.com
Thanks,
Chinna
Its the Journey which gives you Happiness not the Destination-- Dan Millman
April 10, 2008 at 1:49 pm
Lookup_BI (4/10/2008)
Hi Tim,I tried the code you have given above.Correct me if Im wrong, all I have to do is to add an output column in the script component, given name of output column is [Output1] and copy the script you have given above to the Script. I did the following and received an error stating
"Ouput0Buffer is not declared"
I tried replacing my output column name "Output1" to "Ouput0Buffer", but still gives me an error.Am I missing something here?
Thank You
By default, your output is named Output0, and its associated buffer is named Output0Buffer. So, if you create an output named MyOutput, the output buffer you will use is MyOutputBuffer. I know, it's not terribly intuitive, is it? :w00t:
As far as a primer for scripting in SSIS, there aren't many exhaustive books with SSIS scripting information. You'll need to know some about programming in VB.NET, though you don't have to be an expert. Brian Knight's book on SSIS has a chapter on scripting if I remember correctly, which should help you get started.
hth,
Tim
Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply