January 3, 2018 at 2:29 pm
Caruncles - Wednesday, January 3, 2018 8:34 AMAmen on "start down the PowerShell learning path that I should have been going down for the past several years." I'm in that same boat. This looks like an excellent article, but is still way over my head. For starters I just want to convert this statement:
Execute xp_cmdshell 'BCP Mailing.dbo.tTotalJimsListNEW IN "\\MyServer\Shareddata\MyFolder\Mailing Lists\ListtoImport\RemovalsNoCusts.txt" -c -S SQL1 -U me -P mypassword'
to do the same thing, but with Powershell instead of Cmdshell. I have several scripts which use CmdShell and would like to replace them.
function Get-BCPUtility
{
$files = Get-ChildItem "C:\Program Files (x86)\Microsoft SQL Server\" -Recurse | where{$_.FullName -like "*bcp.exe"}
foreach($f in $files)
{
return $f.FullName
}
}
$ServerName = ".\LOCAL2016"
$DatabaseName = "Test"
$Table = "dbo.Test"
$file = "c:\Temp\Test.txt"
$User = "MyUser"
$Psw = "MyPassword"
$arg0 = ' "{0}.{1}" in "{2}" -c -S "{3}" -U "{4}" -P "{5}"'
$arg = $arg0 -f $DatabaseName, $Table, $file, $ServerName, $User, $Psw
$Program = Get-BCPUtility
$Program
$arg
Start-Process -FilePath $Program -ArgumentList $arg -NoNewWindow -Wait -RedirectStandardOutput "standard_output.txt" -RedirectStandardError "standard_error.txt"
January 3, 2018 at 2:41 pm
Wow! I'm going to play with this a while. That's the best way for me to learn.
Thanx!
Wallace Houston
Sunnyland Farms, Inc.
"We must endeavor to persevere."
January 3, 2018 at 3:21 pm
kevaburg - Wednesday, January 3, 2018 12:07 AMAlex Chamchourine - Tuesday, January 2, 2018 11:53 AMkevaburg - Sunday, December 31, 2017 7:55 AMJeff Moden - Saturday, December 30, 2017 6:47 PMkevaburg - Saturday, December 30, 2017 1:52 AMJeff Moden - Friday, December 29, 2017 7:51 PMAlex Chamchourine - Friday, December 29, 2017 11:48 AMHmmm.... they're overdue... I wonder when they're going to throw SSIS on the floor and replace it with something else like the old DTS was.
Both of them (I mean SSIS and DTS) are quite awful IMO 🙂I share that same opinion. Heh... my little bit of "expertise" in both DTS and SSIS came from learning how to read the packages well enough to replace them with stored procedures.
Although I don't have an opinion to DTS (I never had to use it) I do find SSIS (under the auspices of SSDT) an extremely useful tool. The only thing I find irritating, and it isn't a small thing I admit, is the additional time required to initiate the package on execution.
What would interest me and I am sure others, is why you both have a negative opinion of SSIS seeing as not only us but many software houses have become ardent supporters of it.
Heres hoping I haven't started a flame war..... 🙂
Regards,
KevNo flame wars on my part on this, I guarantee it. A good discussion on this shouldn't have any "heat" to it at all.
Let's start this off by citing part of the quote above. What is it that you find to be "extremely useful" about it for you?
For me the most important part is the speed at which a solution can be delivered and not only within SQL Server but also in hetrogenous environments. In the modern business environment that can be a very important capablity especially when complex ETL builds are involved and at very short notice.
That isn't to say it is perfect and I don't want to imply that it is. There have been times when TSQL has yielded better results than an IS object but IS has provided me with the tools to complete complex tasks that I may be doing for the first time with a shallower learning curve. If I choose later to convert the project to TSQL stored procedures and functions then fine, but in the first instance I have delivered and that is what counts.
There are features that are mising such as SCP for which add-ons are available at a cost, but I still found building an IS solution with WinSCP scripts jobs was a much more intuitive process than trying to figure it all out on the fly. Incidentally, this was my first experience of ETL from Oracle into SQL Server nd then back out to DB2 on iSeries.
If I was a person that could visualise all this work and could simply type it all in at short notice in such a way that it worked then I would probably put IS on the side as well, but I'm not and for that reason SSIS (and SSDT) help me build solutions my company needs.
Just my 10 pence worth.... 🙂
Now why do you find it so awful?
I will compare SSIS to a solution based on PoSh script:
#1. For the speed of delivering a solution - it's not the fastest way.
You need to have a special tool (VS with data tool) to start developing. With PoSh you can use Notepad.
You need all your connections to be "live" in order to develop the package (it could be your dev or staging environment of course but it still has to be "live"). With PoSh you don't care about it at all, all will be resolved later at execution time.
The deployment is not exactly a straight forward process. As you need to be able to switch between your dev and production environment settings, you will need a set of config params or a config file (using both is quite cumbersome IMO). With PoSh everything is clear and you only need to copy a file or a few.
#2.Life-cycle support.
Imaging that you are dealing with a SSIS package developed by someone else and you need to quickly change a few things.
Again you need a special tool to open it. Not so with PoSh.
Most likely you will need a full environment set. Not so with PoSh.
After you open the package you might have difficulties to find out some small but important bits of your package. SSIS has a lot of hidden parameters and features. You know, those, that you can get access to by clicking the mouse right button (in certain places only!) and selecting some items from (sometimes huge) menus. But to get there you need to know that they (the hidden params) exist in first place! It could be a real pain in ...
With PoSh everything is transparent (well as transparent as any code could be of course)Where SSIS supposedly shines? It's a very well structured things by its very nature. And here surely it beats any script. However, if you are dealing with a relatively small project you probably would prefer to trade its nice schematics to the simplicity of a script.
Ironically, dealing with a large, complex project could be a difficult thing in SSIS as well. Imaging the size of your diagram!Hi Alex,
thanks for the answers, but I can't agree with you on a few points:
#1: Yes, I need a special environment in order to create SSIS Packages but I only need to configure it once. This in my mind is a small price to pay for a speedy development environment. Yes, My connections do need to be live in order to develop and test, but then you can't test your PoSH scripts unless your test environment is available and so I consider this point from my perspective to be moot.
The most important point about your #1 is the deployment process. It is remarkably simple especially if using a IS Catalog. Projects can be configured with environment variables (file paths, connections etc;) and these can be configured as test, prod, pre-prod etc. A single click in a drop down menu enables the switching of environments and everything associated with them. It does take time to configure but then again, needs only to be done once.#2: Any firm not using source control is asking for trouble especially in a highly dynamic development environment. That means regardless of whether you are scripting in SQL or PoSH, all projects should be saved and versioned in source contol so that anyone can access them, make changes as required and roll back to previous versions if the situation requires it. If you are the sort of person that believes sending files to and from other project members via Email is a good solution then OK but for me that isn't a Team project and it is left open to too many potential issues.
OK, I agree that I can create a PoSH script in Wordpad and move it via copy and paste whereever I want but until the moment it is tested it is nothing more than a text file and in order to make that transition from text file to usable PoSH script I need the environment in which it is to be run, absolutely no different to an SSIS Project.
Sorry Alex, but with this post you haven't won me over.....
For #1 above, have you ever used that method to migrate, say, 400 packages from a pre-2012 environment to 2012 and above?
--Jeff Moden
Change is inevitable... Change for the better is not.
January 3, 2018 at 3:23 pm
Alex Chamchourine - Wednesday, January 3, 2018 2:29 PMCaruncles - Wednesday, January 3, 2018 8:34 AMAmen on "start down the PowerShell learning path that I should have been going down for the past several years." I'm in that same boat. This looks like an excellent article, but is still way over my head. For starters I just want to convert this statement:
Execute xp_cmdshell 'BCP Mailing.dbo.tTotalJimsListNEW IN "\\MyServer\Shareddata\MyFolder\Mailing Lists\ListtoImport\RemovalsNoCusts.txt" -c -S SQL1 -U me -P mypassword'
to do the same thing, but with Powershell instead of Cmdshell. I have several scripts which use CmdShell and would like to replace them.function Get-BCPUtility
{
$files = Get-ChildItem "C:\Program Files (x86)\Microsoft SQL Server\" -Recurse | where{$_.FullName -like "*bcp.exe"}
foreach($f in $files)
{
return $f.FullName
}
}$ServerName = ".\LOCAL2016"
$DatabaseName = "Test"
$Table = "dbo.Test"
$file = "c:\Temp\Test.txt"
$User = "MyUser"
$Psw = "MyPassword"$arg0 = ' "{0}.{1}" in "{2}" -c -S "{3}" -U "{4}" -P "{5}"'
$arg = $arg0 -f $DatabaseName, $Table, $file, $ServerName, $User, $Psw$Program = Get-BCPUtility
$Program
$argStart-Process -FilePath $Program -ArgumentList $arg -NoNewWindow -Wait -RedirectStandardOutput "standard_output.txt" -RedirectStandardError "standard_error.txt"
Ok... how do you schedule that? And how do you get around the fact that code requires user name and password to be called out somewhere along the line? Or, are you calling this from a PoSh task in an SQL Server job? If so, why is there a need for user name and password?
--Jeff Moden
Change is inevitable... Change for the better is not.
January 3, 2018 at 3:50 pm
Jeff Moden - Wednesday, January 3, 2018 3:23 PMAlex Chamchourine - Wednesday, January 3, 2018 2:29 PMCaruncles - Wednesday, January 3, 2018 8:34 AMAmen on "start down the PowerShell learning path that I should have been going down for the past several years." I'm in that same boat. This looks like an excellent article, but is still way over my head. For starters I just want to convert this statement:
Execute xp_cmdshell 'BCP Mailing.dbo.tTotalJimsListNEW IN "\\MyServer\Shareddata\MyFolder\Mailing Lists\ListtoImport\RemovalsNoCusts.txt" -c -S SQL1 -U me -P mypassword'
to do the same thing, but with Powershell instead of Cmdshell. I have several scripts which use CmdShell and would like to replace them.function Get-BCPUtility
{
$files = Get-ChildItem "C:\Program Files (x86)\Microsoft SQL Server\" -Recurse | where{$_.FullName -like "*bcp.exe"}
foreach($f in $files)
{
return $f.FullName
}
}$ServerName = ".\LOCAL2016"
$DatabaseName = "Test"
$Table = "dbo.Test"
$file = "c:\Temp\Test.txt"
$User = "MyUser"
$Psw = "MyPassword"$arg0 = ' "{0}.{1}" in "{2}" -c -S "{3}" -U "{4}" -P "{5}"'
$arg = $arg0 -f $DatabaseName, $Table, $file, $ServerName, $User, $Psw$Program = Get-BCPUtility
$Program
$argStart-Process -FilePath $Program -ArgumentList $arg -NoNewWindow -Wait -RedirectStandardOutput "standard_output.txt" -RedirectStandardError "standard_error.txt"
Ok... how do you schedule that? And how do you get around the fact that code requires user name and password to be called out somewhere along the line? Or, are you calling this from a PoSh task in an SQL Server job? If so, why is there a need for user name and password?
It's not my choice 🙂 The reader asked how to convert his cmd snippet to a PowerShell script. Personally I prefer integrated security. If one insists on using an sql account he can use ...SecureString functions to hide the sensitive data. As for a scheduling I simply use Windows Scheduler: powershell .\<script>
Please, do not get me wrong, I'm not trying to bash t-sql and stored procedures here. Actually I love them and as a matter of fact in our Data Warehouse ETL SSIS package most of the job is done by stored procedures. The package just transfers data between servers and logs the outcome. All the data collecting and merging to DW is done by the stored procedures.
January 3, 2018 at 5:00 pm
Alex Chamchourine - Wednesday, January 3, 2018 3:50 PMJeff Moden - Wednesday, January 3, 2018 3:23 PMAlex Chamchourine - Wednesday, January 3, 2018 2:29 PMCaruncles - Wednesday, January 3, 2018 8:34 AMAmen on "start down the PowerShell learning path that I should have been going down for the past several years." I'm in that same boat. This looks like an excellent article, but is still way over my head. For starters I just want to convert this statement:
Execute xp_cmdshell 'BCP Mailing.dbo.tTotalJimsListNEW IN "\\MyServer\Shareddata\MyFolder\Mailing Lists\ListtoImport\RemovalsNoCusts.txt" -c -S SQL1 -U me -P mypassword'
to do the same thing, but with Powershell instead of Cmdshell. I have several scripts which use CmdShell and would like to replace them.function Get-BCPUtility
{
$files = Get-ChildItem "C:\Program Files (x86)\Microsoft SQL Server\" -Recurse | where{$_.FullName -like "*bcp.exe"}
foreach($f in $files)
{
return $f.FullName
}
}$ServerName = ".\LOCAL2016"
$DatabaseName = "Test"
$Table = "dbo.Test"
$file = "c:\Temp\Test.txt"
$User = "MyUser"
$Psw = "MyPassword"$arg0 = ' "{0}.{1}" in "{2}" -c -S "{3}" -U "{4}" -P "{5}"'
$arg = $arg0 -f $DatabaseName, $Table, $file, $ServerName, $User, $Psw$Program = Get-BCPUtility
$Program
$argStart-Process -FilePath $Program -ArgumentList $arg -NoNewWindow -Wait -RedirectStandardOutput "standard_output.txt" -RedirectStandardError "standard_error.txt"
Ok... how do you schedule that? And how do you get around the fact that code requires user name and password to be called out somewhere along the line? Or, are you calling this from a PoSh task in an SQL Server job? If so, why is there a need for user name and password?
It's not my choice 🙂 The reader asked how to convert his cmd snippet to a PowerShell script. Personally I prefer integrated security. If one insists on using an sql account he can use ...SecureString functions to hide the sensitive data. As for a scheduling I simply use Windows Scheduler: powershell .\<script>
Please, do not get me wrong, I'm not trying to bash t-sql and stored procedures here. Actually I love them and as a matter of fact in our Data Warehouse ETL SSIS package most of the job is done by stored procedures. The package just transfers data between servers and logs the outcome. All the data collecting and merging to DW is done by the stored procedures.
Nope. Didn't think you were bashing a thing and no flames coming from me. I just wanted to know and your answers were pretty much what I expected.
As for the other stuff, sounds like you've got a pretty good handle on one of the good ways to use SSIS.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 4, 2018 at 8:35 am
the example given would be run from ad hoc queries in SSMS. Similar imports, which have a static naming convention run from stored procedures. Yes, a trusted connection could be used.
Thanx!
Wallace Houston
Sunnyland Farms, Inc.
"We must endeavor to persevere."
January 4, 2018 at 8:54 am
Caruncles - Thursday, January 4, 2018 8:35 AMthe example given would be run from ad hoc queries in SSMS. Similar imports, which have a static naming convention run from stored procedures. Yes, a trusted connection could be used.Thanx!
Which example are you speaking of?
--Jeff Moden
Change is inevitable... Change for the better is not.
January 4, 2018 at 9:02 am
Execute xp_cmdshell 'BCP Mailing.dbo.tTotalJimsListNEW IN "\\MyServer\Shareddata\MyFolder\Mailing Lists\ListtoImport\RemovalsNoCusts.txt" -c -S SQL1 -U me -P mypassword'
I run scripts like this from an SSMS query window,
Wallace Houston
Sunnyland Farms, Inc.
"We must endeavor to persevere."
Viewing 9 posts - 31 through 38 (of 38 total)
You must be logged in to reply to this topic. Login to reply