Using powershell in tsql scripts

Question

Using powershell in tsql scripts

kevaburg

SSCoach

Points: 18131
More actions
March 15, 2015 at 1:23 am

#315659

Hi,
I would like to use powershell commands within my TSQL scripts. Does anyone know how to do that?
The reason is that up until now I have been using SQLCMD and XP_CMDSHELL to rename files before importing them into a database. Powershell is a better tool for this job.
Look forward to hearing you replies!
Regards,
Kev

Viewing 12 posts - 1 through 11 (of 11 total)

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

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 1

I'm not sure why anyone would think that file handling with PowerShell is any better than it is with DOS, but that's another subject.

To the best of my knowledge, there is no way to call PowerShell directly from T-SQL (in a stored procedure, for example). That hasn't stopped me, though. I call PowerShell using xp_CmdShell.

It is possible to create a job that has a PowerShell task but hate that even worse than SSIS, which is another place that it's possible.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

kevaburg SSCoach Points: 18131 More actions · Answer 2

Hi Jeff,

the reason is because I am trying to remove the periods from a filename (or at least replace them) before doing a bulk Import. For me it seems far easier in PS to do than in DOS.....

Because Scripting is a better solution than creating Jobs or SSIS packages (I share your opinion but believe they still have their place in the order of things) i would like to perform all of the necessary Tasks in a single script rather than have to call a script from a script in orer to Keep the entire process as simple as possible from start to finish.

Regards Kev

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 3

I'd probably reverse build on this. If you need to manipulate files and do t-sql, I'd write it in PowerShell and call the T-SQL from there rather than trying to burst out to do PoSh from within the T-SQL.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

roger.price-1150775 Default port Points: 1458 More actions · Answer 4

Not seen SQL call Powershell but seen lots of powershell scripts calling SQL.

Why are you renaming the files ?

If it's to ensure a file is only processed once you may be able to do some simple stuff in SQL to manage that. I've done a few import routines scanning a folder for files to process and rather than renaming them as I import them I keep a table of the files with a status and summary of the rowcounts and stuff and refer to it each time so I know what to process.

kevaburg SSCoach Points: 18131 More actions · Answer 5

Grant Fritchey (3/16/2015)
I'd probably reverse build on this. If you need to manipulate files and do t-sql, I'd write it in PowerShell and call the T-SQL from there rather than trying to burst out to do PoSh from within the T-SQL.

I hadn't thought of it from that angle....I will try it and post the results here.

Thanks!

kevaburg SSCoach Points: 18131 More actions · Answer 6

roger.price-1150775 (3/16/2015)
Not seen SQL call Powershell but seen lots of powershell scripts calling SQL.
Why are you renaming the files ?
If it's to ensure a file is only processed once you may be able to do some simple stuff in SQL to manage that. I've done a few import routines scanning a folder for files to process and rather than renaming them as I import them I keep a table of the files with a status and summary of the rowcounts and stuff and refer to it each time so I know what to process.

The Problem is that we have a Microstrategy BI Solution in-house and it produces CSV Files with a period separated date. The periods cause the Name to be truncated during a bulk insert and my solution is to rename the files by removing the additional periods.

Lowell SSC Guru Points: 323518 More actions · Answer 7

kevaburg (3/16/2015)
roger.price-1150775 (3/16/2015)
Not seen SQL call Powershell but seen lots of powershell scripts calling SQL.
Why are you renaming the files ?
If it's to ensure a file is only processed once you may be able to do some simple stuff in SQL to manage that. I've done a few import routines scanning a folder for files to process and rather than renaming them as I import them I keep a table of the files with a status and summary of the rowcounts and stuff and refer to it each time so I know what to process.
The Problem is that we have a Microstrategy BI Solution in-house and it produces CSV Files with a period separated date. The periods cause the Name to be truncated during a bulk insert and my solution is to rename the files by removing the additional periods.

you can call Powershell or an SSIS package which renames via c# commands in a Script task as a job step or as an independent job.(sp_start_job).

i would simply make that the fist step in a job, which then calls whatever you are doing witht eh bulk insert of files

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 8

kevaburg (3/16/2015)
roger.price-1150775 (3/16/2015)
Not seen SQL call Powershell but seen lots of powershell scripts calling SQL.
Why are you renaming the files ?
If it's to ensure a file is only processed once you may be able to do some simple stuff in SQL to manage that. I've done a few import routines scanning a folder for files to process and rather than renaming them as I import them I keep a table of the files with a status and summary of the rowcounts and stuff and refer to it each time so I know what to process.
The Problem is that we have a Microstrategy BI Solution in-house and it produces CSV Files with a period separated date. The periods cause the Name to be truncated during a bulk insert and my solution is to rename the files by removing the additional periods.

I'm a bit confused as to what you mean there. Bulk Insert will pull in period separated dates just fine and will pull in any file that can be named in DOS. It doesn't sound like this is a problem with Bulk Insert. Can you provide more detail on this problem?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

kevaburg SSCoach Points: 18131 More actions · Answer 9

Jeff Moden (3/16/2015)
kevaburg (3/16/2015)
roger.price-1150775 (3/16/2015)
Not seen SQL call Powershell but seen lots of powershell scripts calling SQL.
Why are you renaming the files ?
If it's to ensure a file is only processed once you may be able to do some simple stuff in SQL to manage that. I've done a few import routines scanning a folder for files to process and rather than renaming them as I import them I keep a table of the files with a status and summary of the rowcounts and stuff and refer to it each time so I know what to process.
The Problem is that we have a Microstrategy BI Solution in-house and it produces CSV Files with a period separated date. The periods cause the Name to be truncated during a bulk insert and my solution is to rename the files by removing the additional periods.
I'm a bit confused as to what you mean there. Bulk Insert will pull in period separated dates just fine and will pull in any file that can be named in DOS. It doesn't sound like this is a problem with Bulk Insert. Can you provide more detail on this problem?

Now you have said that I am inclined to think I have forgotten a parameter somewhere.

Here is the script:

--create procedure sp_Ladung_OptionGroesseKollektionStatistik

--as

--Create a holding table for the filelist

create table #filelist(pos int identity(1,1), name nvarchar(max))

insert into #filelist exec xp_cmdshell 'dir C:\NOS_Test\OptionGroesseKollektionStatistik\ /A-D /B'

--select * from #filelist;

--DECLARATIONS

declare @fname nvarchar(100)--Name of the CSV file

declare @path nvarchar(100) --Abolute path to the folder containing the files

declare @open nvarchar(100) --Starting statement for the Bulk Insert

declare @params nvarchar(100)--Bulk Insert parameters

declare @filepath nvarchar(100)--Complete abolute path including the filename

declare @sql nvarchar(max)--The SQL statement for the Bulk Insert

declare @fcount int --How any files are in the source folder?

declare @i int --Required for the loop

declare @move varchar(255) --Used for the Windows batch statement

--CONSTRUCT THE DYNAMIC SQL

set @open = 'bulk insert [Test_KBu]..[OptionGroesseKollektionStatistik] from '''

set @path = 'C:\NOS_Test\OptionGroesseKollektionStatistik\'

set @params = '''with (firstrow = 2, datafiletype = ''widechar'');'''

set @fcount = (select count(*) from #filelist where name is not null and name like 'NOS_%')

set @i = 0

--BULK IMPORT ALL FILES AND MOVE TO THE STORE ON SUCCESSFULL COMPLETION

while (@i < @fcount)

begin try

begin

set @fname = (select name from #filelist where name is not null and pos=@i+1)

set @filepath = @path + @fname

set @sql = @open + @filepath + @params

set @i = @i +1

exec sp_executesql @sql

set @move = 'copy C:\NOS_Test\OptionGroesseKollektionStatistik\' + @fname + ' D:\NOS_Mount\'

exec xp_cmdshell @move

end

end try

--SEND EMAIL ON FAILURE

begin catch

exec msdb..sp_send_dbmail

@profile_name = 'SendMail',

@recipients = 'kevin.burgess@myfirm.de',

@subject = 'CBR-SQLNODE1\ERP_MODULE: NOS Ladelauf Fehlgeschlagen',

@body = 'The NOS Load has encountered a problem. Check the logs for more information.',

@importance = 'high';

end catch

drop table #filelist;

And here the results of the debug run:

The filename as found in the DIR: NOS_OptionGroesseKollektionStatistik_2015-02-19_08.39.35.csv

And as it is inserted into the BULK INSERT statement using the variable @sql:

bulk insert [Test_KBu]..[OptionGroesseKollektionStatistik] from 'C:\NOS_Test\OptionGroesseKollektionStatistik\NOS_OptionGroesseKollektionStatistik_2015-02-19_08'

with (firstrow = 2, datafiletype = 'widechar');

As can be seen, the filename is read up until the first period and the remainder is truncated. I reasoned that by renaming the file to exclude the periods I could solve the issue.

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 10

I believe you've been bitten by the dynamic SQL. To prove what I'm thinking the problem is, change all of your path, filename, and other variables that have anything to do with dynamic SQL to NVARCHAR(MAX) as see if that fixes or changes the problem.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 11

kevaburg (3/16/2015)
Grant Fritchey (3/16/2015)
I'd probably reverse build on this. If you need to manipulate files and do t-sql, I'd write it in PowerShell and call the T-SQL from there rather than trying to burst out to do PoSh from within the T-SQL.
I hadn't thought of it from that angle....I will try it and post the results here.
Thanks!

Jeff has a good lead that may fix your immediate issue but long term I must give a +1 to shifting your approach towards what Grant described.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato