Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
dlorenc
Posting Yak Master
172 Posts |
Posted - 2009-01-28 : 15:55:36
|
I was using the sql server enterprise manager to create some data update jobs...now my IT guys have given me visual studio to do ssis scripts....I'm not familar with the tools box, so if someone can get me started?..I want to read data from one sql server database, massage it a bit, then write it out to a different sql server database....what tools in the toolbox should I look at? (control flow - data flow task?, execute sql tsk?; data flow - lookup? oledb destination?)...I was previously doing this work in one database, so the code looks like this:insert into scorecardmetrics(MetricID, ReportWeek, Target, Value1, Value2, MetricValue, Entered)SELECT 40 as MetricID ,CONVERT(char(10) , DATEADD(d, - DATEPART(dw, GETDATE()), GETDATE()), 101) AS Saturday ,.8 as Target ,COUNT(AgencyCode) AS Value1 , 15 as Value2 , COUNT(*) / 15. AS MetricValue , GETDATE() AS EnteredFROM ConfirmWHERE (Action = 'fy-10 Budget') AND (Confirmed = 1) |
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
Posted - 2009-01-28 : 19:09:46
|
one two week rookie to another two week rookiewell for starters just create an empty SSIS package . create two ole db connections one for your <FromDatabase> and the other for you <toDatabase>then create a SQL Task that dumps data from <FromDatabase> to <toDatabase>. if it requires lots of massaging dump it into a staging tableSELECT * INTO <toDatabase>.dbo.<StagingTable> FROM <FromDatabase>.dbo.<YourTable>if it does not need much massaging do the formatting as in your exampleinsert into <toDatabase>.dbo.scorecardmetrics(MetricID, ReportWeek, Target, Value1, Value2, MetricValue, Entered)SELECT 40 as MetricID,CONVERT(char(10), DATEADD(d, - DATEPART(dw, GETDATE()), GETDATE()), 101) AS Saturday,.8 as Target,COUNT(AgencyCode) AS Value1, 15 as Value2, COUNT(*) / 15. AS MetricValue, GETDATE() AS EnteredFROM <FromDatabase>.dbo.ConfirmWHERE (Action = 'fy-10 Budget') AND (Confirmed = 1)hope it helps |
 |
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-01-28 : 19:13:30
|
quote: Originally posted by dlorenc I was using the sql server enterprise manager to create some data update jobs...now my IT guys have given me visual studio to do ssis scripts....I'm not familar with the tools box, so if someone can get me started?..I want to read data from one sql server database, massage it a bit, then write it out to a different sql server database....what tools in the toolbox should I look at? (control flow - data flow task?, execute sql tsk?; data flow - lookup? oledb destination?)...I was previously doing this work in one database, so the code looks like this:insert into scorecardmetrics(MetricID, ReportWeek, Target, Value1, Value2, MetricValue, Entered)SELECT 40 as MetricID ,CONVERT(char(10) , DATEADD(d, - DATEPART(dw, GETDATE()), GETDATE()), 101) AS Saturday ,.8 as Target ,COUNT(AgencyCode) AS Value1 , 15 as Value2 , COUNT(*) / 15. AS MetricValue , GETDATE() AS EnteredFROM ConfirmWHERE (Action = 'fy-10 Budget') AND (Confirmed = 1)
Can you explain what modifications are you trying to implement? |
 |
|
dlorenc
Posting Yak Master
172 Posts |
Posted - 2009-01-29 : 09:50:51
|
modifications?...the purpose of the effort is to produce scorecard metrics from a datamart that is populated from operational systems. the datamart is populated at 6am on monday mornings..once a week. the datamart will drive the microsoft performance point product, with the metrics appearing on a performance point driven dashboard on the executives sharepoint site.So, once a week I need to look at ...how many of this, or how many of that..usually divided by a total population to produce a percentage metric...the target is the metric goal...so, the modification is two things: first I am moving from enterprise manager to a visual studio tool; second the prototype was reading and saving the metrics from the same database, now I need to go across multiple sql servers and multiple databases to read the data, and write the resulting information back to the scorecard datamart...does that help? |
 |
|
dlorenc
Posting Yak Master
172 Posts |
Posted - 2009-01-29 : 09:59:00
|
Yosiasz...a bit more explicit with the tool bar, please?..I found the ole db Command tool in the dataflow tab toolbox, and I see the execute sql task in the control flow tab toolbox...do I just drag them on to the work area?...or are they connected somehow?*****create two ole db connections one for your <FromDatabase> and the other for you <toDatabase>then create a SQL Task that dumps data from <FromDatabase> to <toDatabase>. |
 |
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
Posted - 2009-01-29 : 11:49:22
|
in VStudio..can you see the toolbox? you can load it from View menu.For connection managers you can see it at bottom of the package it is a single tab. Right mouse click in the off white area and select New Ole DB connection.2 cents |
 |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-29 : 12:29:59
|
seems like what you need is a single data flow task inside which you need OLEDB source to connect to source server ,then some transformation tasks like dervied column,... depending on what manipulation you need to do on data and finally oledb destination to put data to destination table. |
 |
|
dlorenc
Posting Yak Master
172 Posts |
Posted - 2009-01-29 : 14:19:06
|
Thank you all for your advice. I was successful. Here's what I did:1. Open a new project in visual studio business intelligence.2. in the connection manager at the bottom, create two new connections. In my case it was the databaseServer/Database I was reading from, and the databaseServer/Database I was going to write to.3. From the flow control tab, Pull the data flow task from the tool bar to the work area. Double click the task which opens it in the data flow tab for this task.4. From the data flow sources toolbox, pullover a datareader source icon. and down at the bottom of the toolbox from the data flow destinations area pullover the ole db destination icon.5. Double click the reader icon, select the input database in the ole db connection manager pulldown. data access mode is sql command. then I pasted in my sql command text: SELECT 40 as MetricID -- ,DATEADD(d, - DATEPART(dw, GETDATE()), GETDATE()) AS ReportWeek ,.8 as Target ,COUNT(AgencyCode) AS Value1 ,15 as Value2 ,COUNT(*) / 15. AS MetricValue ,GETDATE() AS EnteredFROM epmportal.ConfirmWHERE (Action = 'fy-10 Budget') AND (Confirmed = 1)7. Click ok, then double click on the destination icon. ole db connection manager pulldown and select my target database. data access mode is table or view-fast load, select the target table, check constrainted is checked,and rows per batch is 1, max insert is 0.8. In mappings, validate that the data is going to the right places (as my case I used the same field names in my sql statement 'as' in the target data table, I was fine.9. I validated that the script works by hitting the 'play' green arrow at the top of the visual studio tool bar...then looked in my target database to see that the new records were added.voila!...*smile*...next I will work on some error logic, workflow, and notification on failure (hopefully I can email myself..)... |
 |
|
|
|
|
|
|