February 17, 2012 at 8:51 am
I have an OLEDB Connection configured in the connection manager and I want to use it in script component to call a store procedure
Can anyone please help me
February 17, 2012 at 8:54 am
subhaannai (2/17/2012)
I have an OLEDB Connection configured in the connection manager and I want to use it in script component to call a store procedureCan anyone please help me
The best help anyone can give you is to say 'no'. If you call a stored proc in a script component, it will be called once for every row of input data. Nasty. What are you trying to achieve? There must be a better way.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
February 17, 2012 at 9:01 am
Hi,
Thanks for your reply.Actually Inside my Script component ,i am creating a file and writing some data's into the file and in the mean time i have to update the corresponding data's flag in my table.
so i 'm trying to call my stored Procedure for that.
February 17, 2012 at 10:35 am
subhaannai (2/17/2012)
Hi,Thanks for your reply.Actually Inside my Script component ,i am creating a file and writing some data's into the file and in the mean time i have to update the corresponding data's flag in my table.
so i 'm trying to call my stored Procedure for that.
Really?
So if you have, say, 100,000 rows of data in your data source, you will create 100,000 files containing 'some data'? Sounds unlikely and slow. Perhaps you are processing only a few rows of data?
Or are you confusing a script component with a script task?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
February 17, 2012 at 10:04 pm
im using script component in my data Flow.
For Example :
If my data soruce returns 10,000 records,
inside my script component im creating a single text file which copies all these 10,000data and in the mean time i have to update the a flag bit in the corresponding table to indicate all these data's are entered in the text file.
February 18, 2012 at 5:22 am
subhaannai (2/17/2012)
im using script component in my data Flow.For Example :
If my data soruce returns 10,000 records,
inside my script component im creating a single text file which copies all these 10,000data and in the mean time i have to update the a flag bit in the corresponding table to indicate all these data's are entered in the text file.
OK, now I get it. What you're talking about is an RBAR approach that won't perform well, however.
Consider using a multicast to send the data to a temp/staging table too (truncate the staging table every time the process runs). The last step of your process would then be a single update
Update m
set flag = 1
from MainTable m join Staging s on m.Id = s.Id
That should be much faster.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
February 19, 2012 at 6:24 am
K Thanks Phil,I wll try this,and get back to u,if i have any doubt..
February 20, 2012 at 12:18 am
Phil Parkin (2/18/2012)
subhaannai (2/17/2012)
im using script component in my data Flow.For Example :
If my data soruce returns 10,000 records,
inside my script component im creating a single text file which copies all these 10,000data and in the mean time i have to update the a flag bit in the corresponding table to indicate all these data's are entered in the text file.
OK, now I get it. What you're talking about is an RBAR approach that won't perform well, however.
Consider using a multicast to send the data to a temp/staging table too (truncate the staging table every time the process runs). The last step of your process would then be a single update
Update mset flag = 1
from MainTable m join Staging s on m.Id = s.Id
That should be much faster.
And don't write to a flat file using a script component, use the Flat File Destination instead!
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 22, 2012 at 9:55 am
hi koen ,
I have to include a header and footer in my file, based on the number of records in the file.Thats y i opted Script component.
Can u pls tell me,how to handle the error,when it occurs while writing data in file.
February 22, 2012 at 11:01 am
subhaannai (2/22/2012)
hi koen ,I have to include a header and footer in my file, based on the number of records in the file.Thats y i opted Script component.
Can u pls tell me,how to handle the error,when it occurs while writing data in file.
Which error do you mean exactly?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 22, 2012 at 11:34 am
For example if some Db connection time out error occurs,when my data's are half written in my file.I want this to be handled.My package should not fail.It has be wait for few min and reconnect again to DB and continue to write the data again from the point where it stopped.
Is it possible?
February 22, 2012 at 1:13 pm
subhaannai (2/22/2012)
For example if some Db connection time out error occurs,when my data's are half written in my file.I want this to be handled.My package should not fail.It has be wait for few min and reconnect again to DB and continue to write the data again from the point where it stopped.Is it possible?
Not out of the box.
You could try with checkpoint files (a feature of SSIS) and with retry attempts in a SQL Agent job, but it will be very difficult to achieve what you want.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 23, 2012 at 8:27 pm
Are are these static or data based headers and footers?
To glue three files together, you just issue this command line:
COPY Header.TXT + YourOutputFile.TXT + Footer.TXT NewOutputFile.TXT
and it will create a new file called NewOutputFile.TXT
If the header and footer is static, then you just have to precreate them.
If the header and footer is data based and the columns are different to your main 10,000 row file, you can export the files first in SSIS then append it exactly as above.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply