Marcus Aurelius
asked on
SSIS - How to test run a package "outside" of its normal Schedule?
Experts,
I have a SSIS Pkg in Production that is schedule to run nightly. It takes on average 9.5 hours to process.
I would like to TEST this process outside of its normal schedule in my DEVELOPMENT environment. I have access via Visual Studio 2010 to the package in our Source Control.
Can someone please tell or explain to me the best / easiest way to RUN this package manually in my DEV Environment so that I can test the run time outside of PROD?
Is it as simple as clicking the RUN button and letting it run..?
Thanks
I have a SSIS Pkg in Production that is schedule to run nightly. It takes on average 9.5 hours to process.
I would like to TEST this process outside of its normal schedule in my DEVELOPMENT environment. I have access via Visual Studio 2010 to the package in our Source Control.
Can someone please tell or explain to me the best / easiest way to RUN this package manually in my DEV Environment so that I can test the run time outside of PROD?
Is it as simple as clicking the RUN button and letting it run..?
Thanks
ASKER
So, my understanding is that it will definitely process faster on DEV due to your comment above in paragraph 1. I understand
My project is to Optimize or Refactor/Rebuild it IF I'm able.
The package, in my opinion is very straight-forward,... one item of note is that this process deals with alot of TEXT fields. I have 3 separate sources pulling the same 50 or so fields then Merging these fields into my Data Warehouse. Each source pulls in about 500K rows per night...
Yes, I am logging start/end times into a local table and I use this to measure performance of all my ETL over time....
My project is to Optimize or Refactor/Rebuild it IF I'm able.
The package, in my opinion is very straight-forward,... one item of note is that this process deals with alot of TEXT fields. I have 3 separate sources pulling the same 50 or so fields then Merging these fields into my Data Warehouse. Each source pulls in about 500K rows per night...
Yes, I am logging start/end times into a local table and I use this to measure performance of all my ETL over time....
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
3 Separate sources. Not "joined", but rather pulled separately..(in-tandem?) via SQL Script
Overview of SSIS Process Steps
-------------------------- ----------
1. Source1, Source2, Source3 PULLED via SQL Script
2. then Transformed with CAST of Text fields to DATE using a Function
3. then Merge Data into Data Warehouse...
Merged is done via "MERGE" command SQL statement using SQL TASK in SSIS
Overview of SSIS Process Steps
--------------------------
1. Source1, Source2, Source3 PULLED via SQL Script
2. then Transformed with CAST of Text fields to DATE using a Function
3. then Merge Data into Data Warehouse...
Merged is done via "MERGE" command SQL statement using SQL TASK in SSIS
ASKER
Any additional,,.... ideas...?
Keep in mind though that the PROD resources (number of threads, competing processes at the time it's run, etc.) is going to be different than your DEV box, so it's not exactly an apples-to-apples performance comparison.
>It takes on average 9.5 hours to process.
I'd look into optimizing this. Divide it up into batches, optimize the SQL, determine if any blocking components can be removed, etc.
Also, are you logging to a local table the start/end times of each performance run?