SolvedPrivate

SSIS Data Load

Posted on 2013-05-17
3
29 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
  • 2
3 Comments
 
LVL 48

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 48

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Merige returns error code when updating 15 54
How can i use WITH CTE for checking exist value? 3 46
Help Required 2 39
Data Migration: SSIS vs. Microsoft Dynamic CRM 7 28
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
My client has a dictionary table. They're defining a list of standard naming convention. Now, they are requiring my team to provide us a mechanism how to match new incoming data with existing data in their system.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

828 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