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

Question

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

Viewing 15 posts - 16 through 30 (of 31 total)

You must be logged in to reply to this topic. Login to reply

10e5x Default port Points: 1445 More actions · Answer 1

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 Default port Points: 1445 More actions · Answer 2

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:(

Phil Parkin SSC Guru Points: 246697 More actions · Answer 3

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.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Jeff Moden SSC Guru Points: 1003867 More actions · Answer 4

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 5

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[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

10e5x Default port Points: 1445 More actions · Answer 6

Hi Kraig,

So is a misunderstanding from my boss of SSIS? Oh no, i shall i report this to her.... i am v new to the department. this is my 3rd week. Let me try talk to other senior colleague first. Kraig since u are at it. Based on my problem, with my noobness level, what would u think be the best path to the solution. Lastly after reading ur post, i went on to research. Could u kindly please help me verify if this article is true? It is on using SSIS as source for SSRS. i want to be sure before approaching my boss. http://www.mssqltips.com/sqlservertip/2005/how-to-use-ssis-package-as-a-data-source-for-sql-server-reporting-services-2008-r2/

Thanks, i really like this forum. Alot of helpful folks around.

Regards,

10e5x

10e5x Default port Points: 1445 More actions · Answer 7

Jeff Moden (12/10/2012)
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.

Ok i shall work hard to read up more at the moment. will be burning midnight oil, i will be back in a day!;)

Jeff Moden SSC Guru Points: 1003867 More actions · Answer 8

10e5x (12/10/2012)
Ok i shall work hard to read up more at the moment. will be burning midnight oil, i will be back in a day!;)

With that kind of attitude, you will do very, very well in this profession. Well done.

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

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

10e5x Default port Points: 1445 More actions · Answer 9

Thanks jeff, Haha slowly getting the hang of it. I did a breakdown of your codes and realize it will return me:

the following error: "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value."

but nvm its ok, i am not asking for help yet. let me try see what i can do.

Jeff Moden SSC Guru Points: 1003867 More actions · Answer 10

10e5x (12/10/2012)
Thanks jeff, Haha slowly getting the hang of it. I did a breakdown of your codes and realize it will return me:
the following error: "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value."
but nvm its ok, i am not asking for help yet. let me try see what i can do.

K. Come back if you need a leg up.

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

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

10e5x Default port Points: 1445 More actions · Answer 11

Drowning....GPHM!!! Haiz i tried Execute SQL tasks, cant do it. I tried Execute T-SQL statement task cant do it either. I have read aot articles and examples but my requirement is so different. NVM i shall persevere.

10e5x Default port Points: 1445 More actions · Answer 12

Hi all,

I AM BACK. haha i did quite a number of article reading. I think i am slightly better now. I managed to get what i want by creating 6 'Execute SQL task' with 6 statements in my Control Flow. However the solution is not optimum and i do think will gets overhead if the data is huge. But at least i accomplished something after 5 hrs. For now i would like to link all these task tgt, as a process, is that possible? Meaning Execute SQL task 1 then 2 then 3 continuously till 6. Statement 6 will get me my target table. HAHA CRANKY me. i gg take a nap first.

Thanks all, for all your effort and support. I complete a lousier version as 'In case' solution first. Will try both of yuor solutions when i am b

Thanks,

10e5x

Jeff Moden SSC Guru Points: 1003867 More actions · Answer 13

10e5x (12/11/2012)
Statement 6 will get me my target table. HAHA CRANKY me.

Is all you want is to build a target table from the query I wrote?

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

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

10e5x Default port Points: 1445 More actions · Answer 14

Jeff Moden (12/11/2012)
10e5x (12/11/2012)
Statement 6 will get me my target table. HAHA CRANKY me.
Is all you want is to build a target table from the query I wrote?

Sort of. I am just instructed to derive the target table so i can make use of that in SSRS. Moreover, i do not have rights over at the db(enterprise security purposes) so i have to use SSIS? I am given a view table of the real db to query on. Then from that create my own table in my own development db. From there i do my report. So i though my source is the view....do something....then got the target table in my db?

I still haven got the chance to talk to my supervisor again. She will be back only tml. For now i just want to do whatever i can and as much to get myself prepared to meet her.

Any suggestion what i should do now?

Haha btw i have tried alot things:)

Thanks,

10e5x

10e5x Default port Points: 1445 More actions · Answer 15

@jeff,

Needs your help on SQL statements. Can u kindly refer to this thread?

I have tried your method but it is not logically right. I am kinda urgent for this part.

http://www.sqlservercentral.com/Forums/Topic1395412-149-1.aspx

Thanks