June 12, 2008 at 10:54 pm
Hi,
I have a rquirement that I need to pull the data from different tables of one db and do some parse operation and then populate it in one destination table of other db.
small example....
stb1, stb2, stb3 - source tables
dtb1 - destination table.
I need to take some of the columns from stb1, stb2 and stb3 by joining them and I need to populate the data to dtb1.
Now my question is....
I am planning to write a parametarised stored procedure for this.
Which component I need to use in SSIS packge design?
How to pass the parameters to the SP?
Thank You
June 13, 2008 at 8:10 am
Question
Why a parameterised stored procedure? Yes you can, use the execute SQL task for this and pass the parameters in the same order.
But why not simply a merge or a merge join, or alternatively lookups?
~PD
June 19, 2008 at 8:49 am
Thanks for your response.
Why means I have written a query by joining two tables t1 and t2 and taken the necessary columns. The result set contains the needed columns for destination table.
Now I have to parse the column data and split the data and again need to insert into Destination column.
for example, I have column NAME. I have parsed it and I have taken the fristName and lastName. These parsing stuff has done in stored procedure.
How can I do it in single merge join?
If you have any solution please let me know.
I am new to this DTS packages also.
Thank You
June 20, 2008 at 12:29 am
You would select firstname and lastname from your respective tables, and write a derived column which now becomes NAME.
Merge join is all about knowing if you want to do the join as inner, left, right or full outer, and what your keys are that you are using in your join, but if you are writing a stored procedure, you should know this in any way.
Why dont you play a little bit with it, and if you get stuck, post what your problem is
~PD
June 20, 2008 at 3:45 am
pduplessis (6/20/2008)
You would select firstname and lastname from your respective tables, and write a derived column which now becomes NAME.Merge join is all about knowing if you want to do the join as inner, left, right or full outer, and what your keys are that you are using in your join, but if you are writing a stored procedure, you should know this in any way.
Why dont you play a little bit with it, and if you get stuck, post what your problem is
~PD
My problem is I don't know how to pass the parameters to the Stored Procedure in execute SQL task?
Can I use the Data flow task here?
In Dataflow task, can I use the OLEDB SOURCE for source query. It returns the columns and then same these coloumns I want to give it to Stored Procedure as a parameters. Is it possible?
Thank You
June 20, 2008 at 3:57 am
You misunderstood me.
I meant that you are currently using a stored procedure and deriving NAME from Firstname and Surname.
If you do a merge join you can achieve the same by simply using a column derivation after the join
Its possible to pass parameters to a stored proc for sure, but in this case there are other more appropriate ways of achieving the same thing
~PD
June 20, 2008 at 5:12 am
pduplessis (6/20/2008)
You misunderstood me.I meant that you are currently using a stored procedure and deriving NAME from Firstname and Surname.
If you do a merge join you can achieve the same by simply using a column derivation after the join
Its possible to pass parameters to a stored proc for sure, but in this case there are other more appropriate ways of achieving the same thing
~PD
I will tell you clear requirement.
I have 2 different tables t1 and t2. In this I have taken around 40 columns from both of them by written a query
select t1.c1,t1.Name,t2.c1,t2.Units.... from t1 Left Join t2 where t1.ID=t2.tID
From this, I got the data.
Now I have to parse/divide the column t1.Name as firstname and lastName.
And also t2.Units contains data u1,u2,u3
I need to parse it based on "," symbol and I have to take the no. of units. In this example no. of Units are 3.
Like this I have to do it and then I have to populate this data into destination table dt1.
For this parsing and inserting into dt1, I used a stored procedure SP1.
I told you only 2 example of parsing. Here I need to do many more.
Now tell me which one is better.
I planned like, I will use executeSQL command. But How can I use it for this source query and HOw can I give the source columns as input parameter to Stored Procedure SP1.
I think you understand my requirement. I think I used the Join here.
Thank You
June 20, 2008 at 5:18 am
In your case, I would still go for SSIS merge join and derived columns. But its gonna be quite some work changing it by the sounds of it.
Why? Because everything will be handled in memory, which should outperform the RDBMS.
In anyways, this will give you some practice on the SSIS side.
But as a short term, here is a workaround that may work for you:
a) In your stored procedure, make it insert into T3
b) In SSIS, simply use a preparation SQL task
Your call, but I would take the pain and get to developing the SSIS package.
Also, getting data results from a stored proc as a dataflow may prove some challenges for you, for example metadata not easily being referenced when you build your job
June 20, 2008 at 4:40 pm
pduplessis (6/20/2008)
In your case, I would still go for SSIS merge join and derived columns. But its gonna be quite some work changing it by the sounds of it.Why? Because everything will be handled in memory, which should outperform the RDBMS.
In anyways, this will give you some practice on the SSIS side.
But as a short term, here is a workaround that may work for you:
a) In your stored procedure, make it insert into T3
b) In SSIS, simply use a preparation SQL task
Your call, but I would take the pain and get to developing the SSIS package.
Also, getting data results from a stored proc as a dataflow may prove some challenges for you, for example metadata not easily being referenced when you build your job
Sorry pduplessis,
I am confusing with this Merge join. Merge join is some thing different or is it same like Inner or left or Right joins what I did for my source query?
If you explain it clearly then I can think about this.
Could you please let me know the procedure how to execute the stored procedure in Execute SQL task? I stucted up with how to pass the parameters and how to write the stored procedure in sql command tab.
Do I have to write like this
Exec SP1 0,1,2...
or exec SP1 ?,?,?....
or simply exec SP1
Thank You
June 22, 2008 at 5:12 am
Please help me out as dead line came nearer. I have submit my tasks.
I have created the executesql task and I set up the resultset property as FULLRESULTSET.
I mentioned the query. now it gives the result set as no of records. so I mapped the result set to a variable result.
Now I create another executesql task and I mentioned the procedue
as EXEC ?,?,?..
How can I give the column names of result as a parameters to the stored procedure.
Thank You
June 22, 2008 at 11:41 pm
hey venki,
Once more, I misunderstood you...
You have a SP that inserts data from one table to the other. I didnt realise that you are trying to pass parameters, and work with the outputs of the dataset.
Once more, I would suggest that you change this to Merge Joins. Yes, they are the same as left, right, inner and full outer joins.
From a stored proc, you specify a source OLEDB connection, and change this to stored procedure, and change the output to multiple rows.
Then for your SQL command, you specify exec sp_venkiSP ?, ?
In the parameters section, you tie parameter 1 to variable 1 etc.
Good luck
~PD
ps> You never responded on that Lotus Notes issue of yours. Did linked servers help you, or how did you get past the problem?
June 23, 2008 at 10:13 am
pduplessis (6/22/2008)
hey venki,Once more, I misunderstood you...
You have a SP that inserts data from one table to the other. I didnt realise that you are trying to pass parameters, and work with the outputs of the dataset.
Once more, I would suggest that you change this to Merge Joins. Yes, they are the same as left, right, inner and full outer joins.
From a stored proc, you specify a source OLEDB connection, and change this to stored procedure, and change the output to multiple rows.
Then for your SQL command, you specify exec sp_venkiSP ?, ?
In the parameters section, you tie parameter 1 to variable 1 etc.
Good luck
mmm, Every body suggesting that "map parameters in parameter mapping".
But I structed up here only.
I have created execute SQL task. I wrote a query in that. It gives no of columns as output. I mapped these output recordset to a variable result of type Object. result contain the columns c1,c2,c3.... and contins so many rows not the single row.
Now I have to give these recordset columns to Stored procedure. When I check in parameter mapping, I am getting result as parameter. But I need each column to pass to the stored procedure.
Is there any way to take the columns from the result by giving as result.c1,result.c2.... or any other way?
Thank You
June 23, 2008 at 11:42 pm
uhm,
I am misunderstanding you here.
For my very small brain (and I mean REALLY small brain), lemme try to recap.
You have stored procedure A, and you want to take the results of this stored procedure and map it into stored procedure B. You are having issues doing that.
Do I understand you correctly?
~PD
July 3, 2008 at 6:53 pm
Please check my blog article,
http://venkattechnicalblog.blogspot.com/2008/05/migrating-data-from-multiple-source-to.html
Tegards,
Venkatesan Prabu .J
Thanks and Regards,
Venkatesan Prabu, 😛
My Blog:
http://venkattechnicalblog.blogspot.com/
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply