January 30, 2009 at 10:46 am
Hello Experts:
I am new to SSIS in SQL 2K5. I taught myself to create a ssis package by reading all the material I could find on the web. My intention was to learn to import an excel file to my sql server. I accomplished my goal with some difficulties. Now that I know how to create a SSIS package, I have set out to automate this process so that my data is imported into sql server on the first of each month.
Here are the things I do manually before I do the import.
I add a couple of columns to my excel sheet. One of the columns is the name of the company and the other one is the date of the last day of the previous month. For example, When I import my data in Feb 2009, my excel sheet will have all the data, plus the two columns one with 1-31-2009 and XYZ company name.
I want to avoid doing this step manually. Is there a script that I can use to get this job done?
Two, once I find such script, how do I go about automating the rest of the steps so that the SSIS package runs on its own on the first of each month?
TIA
Azhar
January 30, 2009 at 9:41 pm
One approach to getting the end of the previous month is to add a derived column transformation to the data flow task that reads the Excel file. It could have an entry like this:
name: LastMonthEnd
expression: DATEADD("dd",-DAY(GETDATE()),(DT_DATE)(DT_WSTR,10)GETDATE())
"dd" adds days
GETDATE() returns the current date and time
-DAY(GETDATE()) will subtract current day of month
(DT_WSTR,10)GETDATE() converts the date to a 10 character string (drops time)
(DT_DATE)(DT_WSTR,10)GETDATE()) converts the date (without time) to a date type
A more efficient approach is to write a SQL task that will do this once and store the result in a variable. Figuring out the exact format is a bit of a challenge but perhaps someone out there knows how.
As for adding the company name, do all rows get the same name or does it vary? If the latter, how do you determine which company name to put on a line?
You can set up a SQL Server Agent job to run a package on a schedule. You do that in the SQL Server Management Studio.
February 2, 2009 at 8:57 am
Thank You for your response. I will try to implement what you suggested about getting date using derived column in dataflow task.
As far as your question about adding the company name is concerned, it is the same for each row.
thanks again
Azhar
February 2, 2009 at 4:30 pm
Put the company name in a variable. You will be able to access it in a derived column task.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply