Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

can I export data from a package variable object to an SQL destination? Expand / Collapse
Author
Message
Posted Thursday, December 4, 2008 5:49 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 2:57 AM
Points: 405, Visits: 1,137
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)
Post #613582
Posted Thursday, December 4, 2008 7:23 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 2:11 PM
Points: 252, Visits: 1,729
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.
Post #613674
Posted Thursday, December 4, 2008 8:04 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 2:57 AM
Points: 405, Visits: 1,137
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)
Post #613715
Posted Monday, December 8, 2008 5:06 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 2:57 AM
Points: 405, Visits: 1,137
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)
Post #615468
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse