can I export data from a package variable object to an SQL destination?

Question

can I export data from a package variable object to an SQL destination?

dave-dj

SSChampion

Points: 11297
More actions
December 4, 2008 at 5:49 am

#126184

Can anyone point me in the right direction.
I have a package that in short does a WMI Query for available disk space on a list of servers and then populates a package variable in a table format.
How can I export data from a package variable object to an SQL destination??
_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Dave23 Hall of Fame Points: 3903 More actions · Answer 1

I've never tried it on data that would comprise multiple columns in an output table at once, but for simple package variables, you can use a derived column transformation to insert them into the data flow.

dave-dj SSChampion Points: 11297 More actions · Answer 2

hi thanks, i tried that, but SSIS is complaining that it doesn't have an Input to the derived columns.

Also another issue now I can see whats coming out a script task compent to display the ADO recordset details, is that it's only showing the last record.

On the WMI query i've set the destination to append, but it doesn;t seem to be doing that?

(I know I could export to a CSV file, but I'd rather do all this processing in memory).

Any ideas ?

_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)

dave-dj SSChampion Points: 11297 More actions · Answer 3

Almost got this working now:

I used a script component as a source to access the details in the data in the ADO record set.

However, one problem still remains.

Once I have read in each server in the For Each Loop Container Server Quer the WMI Query is executed.

I've set the properties for the WMI Data Reader Task as follow:

OutputType: Data Table

OverwriteDestination: Append to Destination

DestinationType: Variable

Destination: User::DiskUsageResults

User::DiskUsageResults is a package level varaible of type object.

.... the remaining issue is that once the WMI query runs, it then executes the script component and Dataflow.

I have to do a for each loop container after each WMI Query in order to get the details out to the OLE destination.

Why does the WMI Data reader 'replace' the ADO table despite me setting the OverwriteDestination to Append to Destination?

_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)