April 14, 2011 at 10:57 am
This seems like it should be easy to do, but I can't figure it out.
In my Control Flow I have an Execute SQL task that populates an ADO Recordset variable. That works fine, the variable holds a list of server names.
Then I have that connected to a Foreach Loop Container, which loops through the server names, connects to each server, reads some info, and writes that to a table. That also works, except for those servers that it can't connect to.
So here's what I'd like to do: before I connect to the server, write the value of the variable (which contains the server name) to a table in SQL, along with getdate() so I know when it started trying to connect.
How do I just write the value of a variable to a table? If I just use an OLE DB Destination, there are no input columns.
My best thought was to use a Script Component and somehow get that value, and then somehow make it an output column, which I then connect to the OLE DB Destination as an input column??
If I do this in a C# Script Component:
MessageBox.Show(Dts.Variables["myVariable"].Value.ToString());
it works just fine, so I know my variable is there an available.
Any suggestions appreciated!
April 14, 2011 at 11:36 am
You can use an Execute SQL Task.
Use the Parameter Mapping pane to map your servername to a variable. Then in the SQL Statement you can reference that variable and insert it into a table. The trick is you have to use a ? (question mark) as a placeholder for the variable (I have no idea what the origin of that is.) If you have multiple variables, it is important to reference them in the DELCARE / SET statements in the same order that you mapped them in the Paramert Mapping pane.
So your SQL statement will look something like this:
DELCARE @svr sysname
SET @svr = ?
INSERT INTO dbo.table (svr, dt)
SELECT @svr, getdate()
April 14, 2011 at 1:17 pm
Thank you, that's extremely helpful!
Can you recommend a good, comprehensive book on SSIS?
April 14, 2011 at 1:32 pm
Glad that helped.
When SSIS first came out I bought the book Professional SQL Server 2005 Integration Services by Wrox. It was good for me, it gave me the jump start I needed.
Where I really get a lot of good free training from is http://pragmaticworks.com. They do all sorts of free BI webinars throughout the month, generally about an hour long each. (I'm not affiliated with them, just dig their content :-))
April 14, 2011 at 2:14 pm
JP, if you need to get your variables into a dataflow, another approach is to use the Script Component in the Dataflow as a datasource. It works pretty well and will let you get your variables into the stream. I typically use it when I'm doing strange and interesting things with Recordset Destinations. 🙂
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply