Post reply

SQL Code in SSIS package

  • March 28, 2016 at 4:32 am

    #314414

    The following underneath code when try to(preview) run in the SSIS package throws an error.

    I want this to run in the OLEDB Source Editor as a SQL Command.ATtached is the error message.The result of the select from the temp table (#output) should go into a SQL table.And I plan to run this statement as part of an SSIS package where the dataflow task will be part of a for each loop which is used to loop through a list of sql servers.Any responses are appreciated.What should be done to execute this.

    Thanks

    declare @svrName varchar(255)

    declare @sql varchar(400)

    set @svrName = @@SERVERNAME

    set @sql = 'powershell.exe -c "Get-WmiObject -ComputerName ' + QUOTENAME(@svrName,'''') + ' -Class Win32_Volume -Filter ''DriveType = 3'' | select name,capacity,freespace | foreach{$_.name+''|''+$_.capacity/1048576+''%''+$_.freespace/1048576+''*''}"'

    CREATE TABLE #output

    (line varchar(255))

    insert #output

    EXEC xp_cmdshell @sql

    insert into dbo.ServerDriveSpace(ServerName,Drivename,TotalSpace,FreeSpace)

    select @@Servername as ServerName,rtrim(ltrim(SUBSTRING(line,1,CHARINDEX('|',line) -1))) as drivename

    ,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('|',line)+1,

    (CHARINDEX('%',line) -1)-CHARINDEX('|',line)) )) as Float),0) as 'capacity(MB)'

    ,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('%',line)+1,

    (CHARINDEX('*',line) -1)-CHARINDEX('%',line)) )) as Float),0) as 'freespace(MB)'

    from #output

    where line like '[A-Z][:]%'

    order by drivename

    drop table #output

  • March 30, 2016 at 6:05 am

    #1868554

    Hi

    What is the main objective you are trying to achieve with the overall process? This seems complicated for what I think you are trying to do.

    If you cycled around the severs and ran something like this then perhaps it would be easier

    SELECT f.database_id,

    DB_NAME(f.database_id),

    f.file_id,

    volume_mount_point,

    file_system_type,

    total_bytes / 1024 /1024 as TotalMB,

    available_bytes / 1024 /1024 as AvailableMB,

    f.size * 8 / 1024 as DataSizeMB

    FROM sys.master_files AS f

    CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id) as s;

    Rich

  • April 4, 2016 at 10:05 am

    #1869582

    #temp tables only exist for the duration of the connection that created them.

    Also, I guess that you want to store the results on a different server from where the xp_cmdshell executes.

    Try having everything up to the Exec xp_cmdshell as the source in the data flow, a conditional split to exact the information you need and a destination of where you want to store the information.

    Jez

  • April 13, 2016 at 12:48 am

    #1871590

    I am trying to collect drives space details (total and available) from across a list of 150 servers using t-sql and SSIS with t-sql as part of the SSIS package.

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

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