SolvedPrivate

SSIS Data Load

Posted on 2013-05-17
3
32 Views
Last Modified: 2016-02-10
Hi Experts,

I have attached a query below. It is returning the results I want perfectly. Now I will have to load the results starting from '2009-01-01 00:00:00.000'  until '2013-05-16 00:00:00.000'. I will have to load the daily records stating from '2009-01-01 00:00:00.000' period into my table i.e

 where
rec_create_date between convert(varchar,'2009-1-01 00:00:00.000',102)
and convert(varchar,'2009-01-02 00:00:00.000',102)

and again
where
rec_create_date between convert(varchar,'2009-1-02 00:00:00.000',102)
and convert(varchar,'2009-01-03 00:00:00.000',102)

so on and so forth. These records are coming from Prod into my Rep Server. It is very tough to change the date everytime I run the package on the where condition. Could it be done any better so that I don't have to change the date in the source at all and it will itself change the where condition. I know it can be done with the help of a variable but can you explain me in detail of how to set up this step in my package. It would be a great help. Thanks.
Qry1.sql
0
Comment
Question by:Josh2442
[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
  • 2
3 Comments
 
LVL 49

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 39176709
I'm going to offer an observation here: do NOT use "between"

as is:

rec_create_date between convert(varchar,'2009-1-01 00:00:00.000',102)
and convert(varchar,'2009-01-02 00:00:00.000',102)

to be:
(
rec_create_date >= convert(varchar,'2009-1-01 00:00:00.000',102)
and rec_create_date < convert(varchar,'2009-01-02 00:00:00.000',102)
)

as you are performing an import, you run the (small) risk of records being equal to a time of 00:00:00.000, and because you are using "between" then there is a (small) risk of duplication

The safest, most reliable, method is a combination of >= with < as indicated above.
(don't forget the parentheses)

You might regard the risk to low to bother, that's fine, but please just note that using "between" isn't best practice for date range filters.

for more on this topic please see: "Beware of Between"
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39176712
To your specific question about variables in SSIS, I suggest you try these:

https://www.simple-talk.com/sql/ssis/working-with-variables-in-sql-server-integration-services/

http://dataqueen.unlimitedviz.com/2012/08/how-to-set-and-use-variables-in-ssis-execute-sql-task/

These should answer everything you would need to implement variables in that import I believe.
0
 

Author Closing Comment

by:Josh2442
ID: 39191869
Thanks Expert
0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

In couple weeks ago, I encountered an extremely difficult problem while deploying 2008 SSIS packages to a new environment (SQL Server 2014 standard).  My scenario is: We have one C# application that is calling 2008R2 SSIS packages to load text fi…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

688 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