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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 SSIS and Import/Export (2005)
 Script Component variable in destination table
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

enslinb
Starting Member

South Africa
4 Posts

Posted - 10/13/2014 :  07:58:05  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

2780 Posts

Posted - 10/13/2014 :  08:28:22  Show Profile  Reply with Quote
What happens? Crash? Wrong results?
Go to Top of Page

enslinb
Starting Member

South Africa
4 Posts

Posted - 10/13/2014 :  08:49:37  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

2780 Posts

Posted - 10/13/2014 :  10:21:01  Show Profile  Reply with Quote
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

South Africa
4 Posts

Posted - 10/13/2014 :  11:08:12  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

2780 Posts

Posted - 10/13/2014 :  12:57:14  Show Profile  Reply with Quote
That's what I would have done.

Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000