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?
Go to Top of Page

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
Go to Top of Page

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?
Go to Top of Page

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
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-13 : 12:57:14
That's what I would have done.

Go to Top of Page
   

- Advertisement -