SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Demanding and difficult logic qns. Sorry but i need help.


Demanding and difficult logic qns. Sorry but i need help.

Author
Message
10e5x
10e5x
SSC-Enthusiastic
SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)

Group: General Forum Members
Points: 139 Visits: 180
Hi Phil,
I shall try your approach first since u sounds very confident. And I believe u totally know your stuff. But sad to say, I am not up till the standard to achieve what I need just by looking at your two 1 line instruction. I shall start small. Just let me know if I am heading the right way. So now my first task will be go google research on how to write stored procedure in SSIS? Am I right? Btw I have wrote some script transformation to concat and convert my EVENT_D and EVENT_T to date time format.

Willing to learn but need slightly more guidance. I no I am not skillful enough but I have spent hrs on this proj.

Thanks
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)

Group: General Forum Members
Points: 84637 Visits: 41067
10e5x (12/10/2012)
So now my first task will be go google research on how to write stored procedure in SSIS? Am I right?


Oh my. As you said, you really are new to all of this. I have to hand it to your for taking on such a task with so little knowledge of SQL Server.

To begin learning about stored procedures, I guess I'd have you open SSMS (SQL Server Management Studio) and press the {f1} key to get into the free help system known as "Books Online". Once that shows up on the screen, paste the following lookup innto the "Look for:" box and press enter. Follow your nose from there. Go throught the links in the order given.

stored procedures [SQL Server]

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
10e5x
10e5x
SSC-Enthusiastic
SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)

Group: General Forum Members
Points: 139 Visits: 180
Hi Jeff,
Thank you for all te effort u have put in. I just felt very wasted not be able to implement what u taught. But I shall not give up. When I get better, I will find out ways to have ur code in my project. Just that I dk where to put it now.
Just to know so what u are teaching are using TSQL? I will research on that with SSIS.
Now I shall listen and try Phil method

With greatest thanks,
10e5x
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)

Group: General Forum Members
Points: 84637 Visits: 41067
The reason why I use T-SQL for this is because it's frequently much better to do things there. So much so that a lot of people will build "Execute T-SQL" tasks into their SSIS packages because it's either impossible to do in SSIS or a whole lot easier to do in T-SQL.

Since I work with some companies that don't even allow for an SSIS instance, I better know how to do it all in T-SQL. I've even setup systems that log onto FTP sites, down load information, import to stagig table(s), validate the data, move to the final tables, and report it all... and all if it done without ever going near either SSIS or SSRS.

It does take time to learn enough to get to that point, though. That's why they invented things like SSIS... some of the tasks I mentioned are canned and ready to use and you don't necessarily need much knowledge to do such things.

And, no... I don't believe that you could do what we've done in code in SSIS. I could be wrong, though because, like I said, I don't use SSIS. :-D

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Phil Parkin
Phil Parkin
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18165 Visits: 20392
10e5x (12/10/2012)
Hi Phil,
I shall try your approach first since u sounds very confident. And I believe u totally know your stuff. But sad to say, I am not up till the standard to achieve what I need just by looking at your two 1 line instruction. I shall start small. Just let me know if I am heading the right way. So now my first task will be go google research on how to write stored procedure in SSIS? Am I right? Btw I have wrote some script transformation to concat and convert my EVENT_D and EVENT_T to date time format.

Willing to learn but need slightly more guidance. I no I am not skillful enough but I have spent hrs on this proj.

Thanks


Not quite. Stored procedures are written in T-SQL (Transact SQL) and are stored as part of a database's definition.

SSIS is usually used to move data from one server, or platform (eg, a text file or a spreadsheet) to another. It can do a lot of other things too, but that's enough for now, as you have a lot to learn. Can you get help from someone more knowledgeable? The task that you have taken on is not what I would call a simple introduction :-)

In your case, as far as I can tell, SSIS will NOT be part of the solution at all. I suggest that you focus first on writing your T-SQL and building your stored procedure, then go on from there.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
10e5x
10e5x
SSC-Enthusiastic
SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)

Group: General Forum Members
Points: 139 Visits: 180
Ok Jeff I got it. So I can't use your method? Even if I create those task? So sad, so wasted anyway thanks for the help.

Btw if I am able to convince my supervisor not to use SSIS just use what u propose, are u confident that ur method can do reporting in SSRS

Btw the reason why I am willing to take up this proj becuz it's relatively meaningful, and it's my first proj in this department.
10e5x
10e5x
SSC-Enthusiastic
SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)

Group: General Forum Members
Points: 139 Visits: 180
Phil I also hope some1 in the office can guide me but they are all stuffed with projects. I am at my wits end. Now I shall do as u say. Prepare my TSQL first. But sir, may I know at where? I am not authorize to go into the db and do what I like. Ok ok I shall stop bugging u two, I need to hit the restart button an get everything in place first. Maybe it's due to the deadline I am being too rush I guessSad
Phil Parkin
Phil Parkin
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18165 Visits: 20392
Jeff's code is not wasted, do not worry.

But there is only so much that we can do to help - now you need to help yourself & do some study/learning.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)

Group: General Forum Members
Points: 84637 Visits: 41067
Phil Parkin (12/10/2012)
10e5x (12/10/2012)
Hi Phil,
I shall try your approach first since u sounds very confident. And I believe u totally know your stuff. But sad to say, I am not up till the standard to achieve what I need just by looking at your two 1 line instruction. I shall start small. Just let me know if I am heading the right way. So now my first task will be go google research on how to write stored procedure in SSIS? Am I right? Btw I have wrote some script transformation to concat and convert my EVENT_D and EVENT_T to date time format.

Willing to learn but need slightly more guidance. I no I am not skillful enough but I have spent hrs on this proj.

Thanks


Not quite. Stored procedures are written in T-SQL (Transact SQL) and are stored as part of a database's definition.

SSIS is usually used to move data from one server, or platform (eg, a text file or a spreadsheet) to another. It can do a lot of other things too, but that's enough for now, as you have a lot to learn. Can you get help from someone more knowledgeable? The task that you have taken on is not what I would call a simple introduction :-)

In your case, as far as I can tell, SSIS will NOT be part of the solution at all. I suggest that you focus first on writing your T-SQL and building your stored procedure, then go on from there.



Yes you can use my method. As I explained, you can use an "Execute T-SQL" task (or something like that) in SSIS.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Evil Kraig F
Evil Kraig F
SSCrazy Eights
SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)

Group: General Forum Members
Points: 8545 Visits: 7660
10e5x (12/10/2012)
Ok Jeff I got it. So I can't use your method? Even if I create those task? So sad, so wasted anyway thanks for the help.

Btw if I am able to convince my supervisor not to use SSIS just use what u propose, are u confident that ur method can do reporting in SSRS

Btw the reason why I am willing to take up this proj becuz it's relatively meaningful, and it's my first proj in this department.


I think there's a bit of a disconnect here with what you (or your boss) is expecting SSIS to do in regards to SSRS. I'll try and add another voice to the discussion, maybe slightly different wording will help you sort out what Phil and Jeff are trying explain.

There's four basic pieces to SQL Server these days. The SQL Engine (T-SQL, basically, from your perspective). Here's where you write procs and store/retreive data. Everything starts here.

SSRS hooks onto the Engine to provide web style reporting. Now, it can be an internal or external facing server (ie: company only or client facing) but it hooks off the engine on the Data Sources to either use direct select statements or procs to feed data to the report for human consumption.

SSIS hooks to the engine and lets you move data from point A to point B. In a reporting scenario, the only use for this would be to move data off the primary server to a reporting server that was dedicated to that so the read volume didn't interrupt regular usage. A reporting server is rarely 'real time' and usually has a delay, from 10 minutes to a week, depending on update scheduling.

SSAS (the last of the four pieces) is for building cubes. This is probably above where you need to be right now but SSRS can hook to this using the MDX language, but you REALLY don't want to go near this yet.

So, SSIS can run T-SQL statements, but an SSIS package can't be used as a data source for an SSRS report. Your boss seems to have either misstated what his intent was to you, or he's misinformed about what SSIS can do for you. You can automate reports to be shipped out and the like with SSIS, but you can do the same thing from directly within SSRS and the SQL Engine's Agent. The only type of 'report' that SSIS would help you with is if you were trying to produce CSV files or the like to be ftp'd to clients that their own data centers would be importing to some other system.

I hope that helps.


- Craig Farrell

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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search