August 3, 2011 at 9:34 am
Hello all,
Working with SSIS 2005 I'm having a problem setting a variable using column data from an excel spreadsheet. The SSIS package gets data from a table in one of my databases and appends that data to the spreadsheet. What I would like to do is set a date variable using a date (Max([Column1]) from the spreadsheet. I've gotten as far as getting it to open the spreadsheet and running the query to get the max date but I can't for the life of me figure out how to set my variable to that date.
On a side note do I need to take any special pains to ensure that the spreadsheet is closed before I begin the import of data? In other words is it the connection manager that is actually making the connection or is it my Data Flow Task?
Thanks!
Neil Denton
August 3, 2011 at 2:17 pm
Well I found a way of doing it, not what I would consider optimal though. I created a table in my DB that had one column set as datetime. I pulled the date out of the excel spreadsheet and dropped it into the table. I then ran a execute SQL task and pulled the date from that table and inserted into the variable.
If anyone knows how to do it directly from a Excel Source Task into a variable I would love to hear how.
Thanks,
Neil
August 7, 2011 at 11:32 pm
ndenton (8/3/2011)
I've gotten as far as getting it to open the spreadsheet and running the query to get the max date but I can't for the life of me figure out how to set my variable to that date.
Based on the above I'll assume that you've gotten your query to return the required row to the dataflow. Given that, all you need to do is create a Script Component as a Destination, expose your variable to the script editor as a ReadWriteVariable and write some code such as the following:
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Public Class ScriptMain
Inherits UserComponent
'Create a temporary variable to hold the value as you can only write to a
'package-variable in the Post Execute method
Private tempVar As String = "default"
'Load the temporary variable in the ProcessInputRow method
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
tempVar = Row.<ColumnName>
End Sub
'Set your variable in the Post Execute method to the tempVar value
Public Overrides Sub PostExecute()
Me.Variables.<MyVariableName> = tempVar
MyBase.PostExecute()
End Sub
End Class
Obviously this is a simplified example, if you have more than a single row of data the above code will overwrite the temp value on each pass.
As for your question about ensuring the excel sheet is closed, I'm fairly sure it will give you an error if the sheet is locked (someone/thing has it open). It's a simple test though, right?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply