Script Component variable in destination table

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)

Script Component variable in destination table

Author

Topic

enslinb
Starting Member

4 Posts

Posted - 2014-10-13 : 07:58:05

Hi, this is my 1st post, hope Im doing it right

I have a for each loop container in my ssis package where i loop through the files in a folder, (1) draw the data, (2) get the file creation date and use it as a (3)derived column and (4)insert all the info into a table
I cannot seem to bind the creation date to the variable created, FileDate

Public Overrides Sub PostExecute()
MyBase.PostExecute()
Dim fileinfo As FileInfo
Dim current As String = Me.Variables.Current
fileinfo = New FileInfo(Current)
Me.Variables.FileDate = fileinfo.CreationTime
End Sub

In the package i have a flat file source, next i have the script component, then a derived column and lastly the destination table

What am i doing wrong?
I am trying the insert a picture of what i have but not sure how to

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-13 : 08:28:22

What happens? Crash? Wrong results?

enslinb
Starting Member

4 Posts

Posted - 2014-10-13 : 08:49:37

It uses the static value date when creating the variable. Not sure what to insert into Value in Variable window

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-13 : 10:21:01

What static value date? I can see from your code that you pull the date from the FileInfo object and assign it to your variable. If you put pre- and post-execution breakpoints on your script component, what is in the variable before and after the script component executes?

enslinb
Starting Member

4 Posts

Posted - 2014-10-13 : 11:08:12

I just solved it after reading this article http://microsoft-ssis.blogspot.com/2011/01/how-to-use-variables-in-script.html
"You can't get the new variable value until the Data Flow Task is finished. So you can't get its value within the same Data Flow Task"

I removed the Script Component from the Data Flow and created a Script Task in front of the Data Flow in the loop container
It is assigned to my variable correctly
Thank you gbritton, i will be asking many questions as scripting in ssis is new to me

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-13 : 12:57:14

That's what I would have done.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources