Best Option for Working with Files in SSIS

Question

Best Option for Working with Files in SSIS

Daniel Calbimonte

SSCertifiable

Points: 6200
More actions
May 9, 2017 at 12:00 am

#402042

Comments posted to this topic are about the item Best Option for Working with Files in SSIS
December 31, 2021 at 8:55 am

This was removed by the editor as SPAM

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

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

joeyhayesus SSC Enthusiast Points: 163 More actions · Answer 1

Thanks for sharing the results of this experiment.
I'd be extremely interested in discovering WHY things slowed down when they were ran in parallel.
Is it possible that the storage system was simply overwhelmed? Disk seeks were hammering your disk because files were in two different places?

Brent Leslie SSC-Addicted Points: 416 More actions · Answer 2

Ahhh... don't your results and conclusion contradict themselves?

Results:
"The File System task is the worst option and the slowest one with 130 seconds"

Conclusion:
"As you can see, the fastest solution is the File System Task."

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 3

Brent Leslie - Sunday, May 14, 2017 4:19 AM
Ahhh... don't your results and conclusion contradict themselves?
Results:
"The File System task is the worst option and the slowest one with 130 seconds"
Conclusion:
"As you can see, the fastest solution is the File System Task."

It IS a bit confusing until you realize that he was speaking of the non-parallel versions in his conclusion. It would have been handy if he pointed that out in that statement but he did clarify a bit further on in the Conclusion.

When we talked about the tasks in parallel, we did not get a better performance except with the cmd. In few words, in this case, having the process in parallel does not guarantee a better performance

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

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 4

@calbimonte.Daniel,

Except for the previously stated bit of confusion in the Conclusion of the article, I liked the article. It was simple, straight forward, and the experiment appears to be repeatable for any one that takes the time to try it. Thanks for taking the time to post it, Daniel.

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

daniel.swanson SSC Enthusiast Points: 197 More actions · Answer 5

I'm curious what the impact of reading/writing to the same disk had on the parallel execution. Would results improve by writing to another physical disk?

paul.hermeneutic SSC Rookie Points: 48 More actions · Answer 6

If there are multiple files, an analysis of ROBOCOPY would be interesting with various /MT settings.

sqlman63 SSC-Addicted Points: 499 More actions · Answer 7

Might also want to consider BCP and or bulk insert, sometimes it's really fast on imports..... have to see whats faster in situations with bigger files.

Phil Parkin SSC Guru Points: 246959 More actions · Answer 8

Sandy14 wrote:

MicrosoftSQL Server Integration Services (SSIS) is a platform for building high-performance data integration solutions, including extraction, transformation, and load (ETL) packages for data warehousing.

What's your point and how does it relate to the article?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 9

It's a direct quote from here...

https://docs.microsoft.com/en-us/sql/integration-services/ssis-how-to-create-an-etl-package?view=sql-server-ver15#what-is-sql-server-integration-services-ssis

Must be a spammer setting up.

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