February 19, 2014 at 10:28 am
Hello - i am tasked to build a ssis package that:
1. Grabs Data
2. Export it to Flat file
3. Ftp
My first question is within the query it grabs data with a year date of 2015. Next july 1st the users would need it to change to 2016. What is the best way to do this?
February 19, 2014 at 10:37 am
elee1969 (2/19/2014)
My first question is within the query it grabs data with a year date of 2015. Next july 1st the users would need it to change to 2016. What is the best way to do this?
Use a configuration so that you can feed the package changes externally. Setup a variable, set that as the parameter for the query, and then put the variable into a configuration.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 19, 2014 at 11:32 am
Use an execute sql task to grab the data the you need (i.e. from 2015 to 2016), then use a data flow task to take that data from that table in yourDB and flow it into a flat file destination, then outside of the data flow task set up a ftp connection with appropriate credentials to send the file you've just created via ftp.
February 19, 2014 at 11:53 am
Evil Kraig F (2/19/2014)
elee1969 (2/19/2014)
My first question is within the query it grabs data with a year date of 2015. Next july 1st the users would need it to change to 2016. What is the best way to do this?Use a configuration so that you can feed the package changes externally. Setup a variable, set that as the parameter for the query, and then put the variable into a configuration.
Thanks Craig for the info. So what your saying is every July 1st i would have to go into the config file and change the year?
February 19, 2014 at 11:55 am
stormsentinelcammy (2/19/2014)
I googled a few sites on delpoyment. So i kind of got the gist of things. One thing i am unsure of is when deploying the package do i just copy the bin folder from my local laptop to the server then double click the manifest file to install on my production sql server?
February 19, 2014 at 12:55 pm
elee1969 (2/19/2014)
Evil Kraig F (2/19/2014)
elee1969 (2/19/2014)
My first question is within the query it grabs data with a year date of 2015. Next july 1st the users would need it to change to 2016. What is the best way to do this?Use a configuration so that you can feed the package changes externally. Setup a variable, set that as the parameter for the query, and then put the variable into a configuration.
Thanks Craig for the info. So what your saying is every July 1st i would have to go into the config file and change the year?
Yeah, that's the easy way if you want it user configurable. If you're looking for it automated, just detect your current date and adjust what year to put in the variable based on crossing the June 1 threshold.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 19, 2014 at 1:02 pm
if you're going from one environment to another then you should use a configuration file to hold the username/password to the server connection as well as for the ftp username/password,etc... (you'll need to open the configuration file once you create it in development and make sure that the username/password needed for that server/database is correct and deploy both objects together to production). Be sure to take the .dtsx package and the config file and you'll be fine on that front.
Make sure that whatever folder/path you're dumping that file that you're creating off the query that'll be ftp'd is in either a shared folder that you have access to in production or that you create the same folder path you used in development a good practice is to use a folder structure that already exists in production so that you're assured of not running into avoidable errors.
February 19, 2014 at 2:38 pm
Evil Kraig F (2/19/2014)
elee1969 (2/19/2014)
Evil Kraig F (2/19/2014)
elee1969 (2/19/2014)
My first question is within the query it grabs data with a year date of 2015. Next july 1st the users would need it to change to 2016. What is the best way to do this?Use a configuration so that you can feed the package changes externally. Setup a variable, set that as the parameter for the query, and then put the variable into a configuration.
Thanks Craig for the info. So what your saying is every July 1st i would have to go into the config file and change the year?
Yeah, that's the easy way if you want it user configurable. If you're looking for it automated, just detect your current date and adjust what year to put in the variable based on crossing the June 1 threshold.
How would i do this? in the sql query or is there some where ssis i can do this? Sorry for all the questions.
thanks!!
February 19, 2014 at 2:48 pm
You can do either/or, but its best to do it in the query that has the initial list of all the data that you want, best to get the initial list of what you need than have to filter in the ssis package.
Do it in the query, this will give you today's date
SELECT CONVERT(DATE, GETDATE()) --2014-02-19
so something like this
select a,b,c
from table
where datecolumn >= CONVERT(DATE, GETDATE())
February 19, 2014 at 2:56 pm
elee1969 (2/19/2014)
How would i do this? in the sql query or is there some where ssis i can do this? Sorry for all the questions.
You would do this in either a script component or in an execute T-SQL component. The T-SQL would look something like this:
SELECT
CASE WHEN MONTH( GETDATE()) >= 6
THEN YEAR( GETDATE()) + 2
ELSE YEAR( GETDATE()) + 1
END AS Result
You'd drop the result off to a variable in the settings on the execute SQL task.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 19, 2014 at 3:06 pm
Evil Kraig F (2/19/2014)
elee1969 (2/19/2014)
How would i do this? in the sql query or is there some where ssis i can do this? Sorry for all the questions.You would do this in either a script component or in an execute T-SQL component. The T-SQL would look something like this:
SELECT
CASE WHEN MONTH( GETDATE()) >= 6
THEN YEAR( GETDATE()) + 2
ELSE YEAR( GETDATE()) + 1
END AS Result
You'd drop the result off to a variable in the settings on the execute SQL task.
Thanks Craig for your help!
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply