Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««1234»»»

Demanding and difficult logic qns. Sorry but i need help. Expand / Collapse
Author
Message
Posted Monday, December 10, 2012 7:20 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Saturday, February 2, 2013 11:09 AM
Points: 97, 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
Post #1394607
Posted Monday, December 10, 2012 7:32 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:51 PM
Points: 35,606, Visits: 32,190
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1394615
Posted Monday, December 10, 2012 7:33 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Saturday, February 2, 2013 11:09 AM
Points: 97, 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
Post #1394617
Posted Monday, December 10, 2012 7:41 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:51 PM
Points: 35,606, Visits: 32,190
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.


--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1394624
Posted Monday, December 10, 2012 8:41 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:45 AM
Points: 5,258, Visits: 12,190
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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1394661
Posted Monday, December 10, 2012 8:46 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Saturday, February 2, 2013 11:09 AM
Points: 97, 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.
Post #1394664
Posted Monday, December 10, 2012 8:57 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Saturday, February 2, 2013 11:09 AM
Points: 97, 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 guess
Post #1394668
Posted Monday, December 10, 2012 11:44 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:45 AM
Points: 5,258, Visits: 12,190
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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1394724
Posted Monday, December 10, 2012 11:51 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:51 PM
Points: 35,606, Visits: 32,190
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1394730
Posted Monday, December 10, 2012 3:24 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 9:51 AM
Points: 5,446, Visits: 7,616
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
Post #1394800
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse