Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SSIS - How to test run a package "outside" of its normal Schedule?

Posted on 2014-02-06
5
Medium Priority
?
455 Views
Last Modified: 2016-02-10
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
0
Comment
Question by:MIKE
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 66

Expert Comment

by:Jim Horn
ID: 39838949
Yes.

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?
0
 
LVL 17

Author Comment

by:MIKE
ID: 39838982
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....
0
 
LVL 66

Accepted Solution

by:
Jim Horn earned 1500 total points
ID: 39839013
> I have 3 separate sources pulling the same 50 or so fields
Are the three separate, or is there a JOIN somewhere of these three?  Reason I ask is because JOINs require sorting, which is a blocking component, which will prevent streaming and slow down the feed.

>then Merging these fields into my Data Warehouse.
How is the MERGE happening?  A T-SQL Merge, Pragmatic Works' Upsert Destination, lots of ugly code...
0
 
LVL 17

Author Comment

by:MIKE
ID: 39839294
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
0
 
LVL 17

Author Comment

by:MIKE
ID: 39847310
Any additional,,.... ideas...?
0

Featured Post

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question