Data flow concept - Thoughts wanted

Question

Data flow concept - Thoughts wanted

Viewing 3 posts - 31 through 32 (of 32 total)

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

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

Jeff Moden - Monday, August 28, 2017 7:21 AM
Orlando Colamatteo - Sunday, August 27, 2017 9:34 PM
Hi Tom, One other thing to consider...has your company talked about moving any of its infrastructure to the Microsoft Cloud (Azure)? If so, the solution Jeff is proposing could not be ported to an Azure SQL Database service due to the file system access it relies on. In addition to choosing solutions that insulate me from changes unpredictable data partners make, I also look to choose solutions that insulate me from specific hardware or infrastructure choices. Leveraging PowerShell & SSIS from outside the database engine as opposed to using xp_cmdshell and BULK INSERT from inside the database engine will allow you a ton more flexibility long-term in this area.
Agreed. Azure doesn't lend itself well to such things even if the use of xp_CmdShell probably isn't necessary here.
Since I'm no wizard when it comes to Azure/etc, have you found any shortcomings for either PowerShell or SSIS in the cloud compared to on-site systems?

And, sorry about the delay for a demo... I got really hammered by my full time job and lost track of this thread. If the plan is to move to the cloud, then there's no use in such a demo here.

--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)

Tom_Hogan Hall of Fame Points: 3875 More actions · Answer 2

Jeff Moden - Monday, August 28, 2017 7:22 AM
And, sorry about the delay for a demo... I got really hammered by my full time job and lost track of this thread. If the plan is to move to the cloud, then there's no use in such a demo here.

Real work comes first.

While I'd love to see your demo, please don't stress over it. A long term solution to our "Artificial Intelligence" is way down on my priority list at work. I'm just glad to see the options. I totally forgot SQL had the BULK INSERT statement.

We have no plans to move to the cloud.

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 3

Jeff Moden - Monday, August 28, 2017 7:21 AM
Orlando Colamatteo - Sunday, August 27, 2017 9:34 PM
Hi Tom, One other thing to consider...has your company talked about moving any of its infrastructure to the Microsoft Cloud (Azure)? If so, the solution Jeff is proposing could not be ported to an Azure SQL Database service due to the file system access it relies on. In addition to choosing solutions that insulate me from changes unpredictable data partners make, I also look to choose solutions that insulate me from specific hardware or infrastructure choices. Leveraging PowerShell & SSIS from outside the database engine as opposed to using xp_cmdshell and BULK INSERT from inside the database engine will allow you a ton more flexibility long-term in this area.
Agreed. Azure doesn't lend itself well to such things even if the use of xp_CmdShell probably isn't necessary here.
Since I'm no wizard when it comes to Azure/etc, have you found any shortcomings for either PowerShell or SSIS in the cloud compared to on-site systems?

Azure Data Factory is the cloud offering of SSIS. The brochure says it is easy to lift existing SSIS Packages that run on premises and drop them into Data Factory however if they're doing anything non-standard like interacting with the command line or referencing third party objects "easy" won't hold up.

Another option is to run a SQL Server instance in an Azure VM. In this configuration it'd be like running SQL Server and SSIS on premises so you could run xp_cmdshell and whatever else you normally would. You'd just be doing it on hardware you don't have to maintain, on an OS and instance you do have to maintain, all in an Azure data center.

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