Need you help guys - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SSIS and Import/Export (2008)

Need you help guys

Author

Topic

LearningSQLKid
Yak Posting Veteran

51 Posts

Posted - 2011-11-30 : 06:33:41

Hi Guys

I'm tasked to create an ssis package to extract data from database. One of the table having 10 millions record in it. When i run ssis package it eats up all of the memory which apparently is not good.

Can anyone suggest any approach to help me to get out of this problem.

I would highly appreciate suggestion in this regard

Many Thanks

Select Knowledge from LearningProcess

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-30 : 06:42:51

Don't use SSIS?
Are you doing anything else in the package other than just exporting? What is the source and destination - sql server to files?
Is it ssis that's using up the memory or something else.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-30 : 11:20:07

quote:
Originally posted by LearningSQLKid

Hi Guys

I'm tasked to create an ssis package to extract data from database. One of the table having 10 millions record in it. When i run ssis package it eats up all of the memory which apparently is not good.

Can anyone suggest any approach to help me to get out of this problem.

I would highly appreciate suggestion in this regard

Many Thanks

Select Knowledge from LearningProcess

is it just extraction of data from table as it is or does it involve any complex calculations? also whats the destination? if its a simple pull you can even consider using bcping out data if to file.

also currently in ssis what steps are you using?are there too many lookups? are you using SCD wizard or something?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

LearningSQLKid
Yak Posting Veteran

51 Posts

Posted - 2011-12-02 : 19:37:37

Hi visakh16 and nigelrivett

First Thanks for your reply. There are no complex calculations involved at SSIS level.In fact there is no calculation at all. Actually we have different clients and the database schema is 100 percent identical. So what we are doing is pulling data from all the 10 clients into one new database which is being used for reporting.SSIS has a config file which has a client code. In dataflow task i used derived colum (client code) coming from config file and then being loaded into the database used for reporting.

So at SSIS i have an EXECUTE SQL task and a Dataflow task. The data flow task pull the data from all the tables into database being used for reporting

There are 10 clients and hence the same package is used but with different config values to extarct data from all the 10 database into one

Source is SQL Server 2005 and Destination is SQL Server 2008
Sorry for the long post but i hope i this will help u to get the good understanding of what i want to achieve

Select Knowledge from LearningProcess

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-03 : 00:24:53

quote:
Originally posted by LearningSQLKid

Hi visakh16 and nigelrivett

First Thanks for your reply. There are no complex calculations involved at SSIS level.In fact there is no calculation at all. Actually we have different clients and the database schema is 100 percent identical. So what we are doing is pulling data from all the 10 clients into one new database which is being used for reporting.SSIS has a config file which has a client code. In dataflow task i used derived colum (client code) coming from config file and then being loaded into the database used for reporting.

So at SSIS i have an EXECUTE SQL task and a Dataflow task. The data flow task pull the data from all the tables into database being used for reporting

There are 10 clients and hence the same package is used but with different config values to extarct data from all the 10 database into one

Source is SQL Server 2005 and Destination is SQL Server 2008
Sorry for the long post but i hope i this will help u to get the good understanding of what i want to achieve

Select Knowledge from LearningProcess

whats the execute sql task doing?
for big tables, try pulling the data using a execute sql task use INSERT...SELECT if its a direct pull and if the Data flow task is taking long

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

LearningSQLKid
Yak Posting Veteran

51 Posts

Posted - 2011-12-03 : 03:04:52

Hi visakh16

The EXECUTE SQL is recreating the table for fresh load. As the are big so for the fresh load tables are being dropped and recreated using EXECUTE SQL Task

Is it possible to use execute sql task to extract data from table that exists on some other server. For example

Select * INTO Server2.Database2.Schema2.Table2
FROM Server1.Database1.Schema1.Table1

i Can expose server name, database name, user name and password via config file but How would i pass destination connection to execute sql task.

Thanks

Select Knowledge from LearningProcess

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-03 : 03:09:16

quote:
Originally posted by LearningSQLKid

Hi visakh16

The EXECUTE SQL is recreating the table for fresh load. As the are big so for the fresh load tables are being dropped and recreated using EXECUTE SQL Task

Is it possible to use execute sql task to extract data from table that exists on some other server. For example

Select * INTO Server2.Database2.Schema2.Table2
FROM Server1.Database1.Schema1.Table1

i Can expose server name, database name, user name and password via config file but How would i pass destination connection to execute sql task.

Thanks

Select Knowledge from LearningProcess

its possible you should be using


INSERT  INTO Server2.Database2.Schema2.Table2
Select *
FROM Server1.Database1.Schema1.Table1

also you should be setting up linked server connection to server2 before hand.
Didnt understand what you meant by pass destination connection to execute sql task. if you're having linked server setup you dont need to send any connection details explicitly in ssis for connecting to server2

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

LearningSQLKid
Yak Posting Veteran

51 Posts

Posted - 2011-12-04 : 18:19:14

Thanks nigelrivett

Just a quick one .. if i use sql task and use insert into statement, will it be fast as compared to data flow task ? as i have more than 10 million records in tables.?

Thanks

Select Knowledge from LearningProcess

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-04 : 23:47:03

quote:
Originally posted by LearningSQLKid

Thanks nigelrivett

Just a quick one .. if i use sql task and use insert into statement, will it be fast as compared to data flow task ? as i have more than 10 million records in tables.?

Thanks

Select Knowledge from LearningProcess

insert into will be fast as it does set based operation. In data flow task, based on buffer size it does processing in batches which might take more time compared to sql task.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

LearningSQLKid
Yak Posting Veteran

51 Posts

Posted - 2011-12-09 : 22:45:44

quote:
Originally posted by visakh16

quote:
Originally posted by LearningSQLKid

Thanks nigelrivett

Just a quick one .. if i use sql task and use insert into statement, will it be fast as compared to data flow task ? as i have more than 10 million records in tables.?

Thanks

Select Knowledge from LearningProcess

insert into will be fast as it does set based operation. In data flow task, based on buffer size it does processing in batches which might take more time compared to sql task.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Hi visakh16

Thanks for you reply. I tried to accomplish the same thing using Execute SQL task which run in parallel. However with the large data three of them get fail.. why is that ? If i run them individually they run fine without any problem .. any idea ?

Select Knowledge from LearningProcess

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-10 : 07:52:47

whats the error message thrown when they fail? can you please post it?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

LearningSQLKid
Yak Posting Veteran

51 Posts

Posted - 2011-12-10 : 08:35:48

quote:
Originally posted by LearningSQLKid

quote:
Originally posted by visakh16

quote:
Originally posted by LearningSQLKid

Thanks nigelrivett

Just a quick one .. if i use sql task and use insert into statement, will it be fast as compared to data flow task ? as i have more than 10 million records in tables.?

Thanks

Select Knowledge from LearningProcess

insert into will be fast as it does set based operation. In data flow task, based on buffer size it does processing in batches which might take more time compared to sql task.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Hi visakh16

Thanks for you reply. I tried to accomplish the same thing using Execute SQL task which run in parallel. However with the large data three of them get fail.. why is that ? If i run them individually they run fine without any problem .. any idea ?

Select Knowledge from LearningProcess

Hi
Here is the error:

[Execute SQL Task] Error: Executing the query failed with the following error: "Communication link failure". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

What am i doing ?

1 ) I have one sequence container
2) This sequence container has 10 EXECUTE SQL TASKS
3) These EXECUTE SQL TASK are not connected with each others (Mean when the SSIS package run all of them runs in parallel not one by one)

I think the data in tables in very huge and Link Server is unable to handle them when run in parallel.

Please note when i execute one task at a time then i see no error however running all of them at the same time throws out the error stated above.

Is there a way to make a link server Multi-Threaded? or is there any property of sequnce container or EXECUTE SQL Task that allow them to run in parallel ?

Thanks a lot my friend

Select Knowledge from LearningProcess

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-10 : 11:32:34

what are these query? are there insert queries?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

LearningSQLKid
Yak Posting Veteran

51 Posts

Posted - 2011-12-10 : 18:44:15

quote:
Originally posted by visakh16

what are these query? are there insert queries?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Yup they are simply the insert quries

like

Insert into tableA (col1,col2,col7)
Select colA,ColB,Col7 from TableB

Select Knowledge from LearningProcess

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-11 : 01:10:35

what are values you've set for resultset queries and in variables mapping tab?
also is one sql task using value returned by another sql task?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

LearningSQLKid
Yak Posting Veteran

51 Posts

Posted - 2011-12-11 : 03:48:45

quote:
Originally posted by visakh16

what are values you've set for resultset queries and in variables mapping tab?
also is one sql task using value returned by another sql task?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Question:what are values you've set for resultset queries and in variables mapping tab?

Answer: Resultset=None; I'm not using any parameter at this time.

Question: Is one sql task using value returned by another sql task?
Answer: NO

Select Knowledge from LearningProcess

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-11 : 11:14:30

quote:
Originally posted by LearningSQLKid

quote:
Originally posted by visakh16

what are values you've set for resultset queries and in variables mapping tab?
also is one sql task using value returned by another sql task?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Question:what are values you've set for resultset queries and in variables mapping tab?

Answer: Resultset=None; I'm not using any parameter at this time.

Question: Is one sql task using value returned by another sql task?
Answer: NO

Select Knowledge from LearningProcess

Resultset has nothing to do with parameters. it just maps the columns in your resultset to variable you provide in variables mapping tab. so depending on your resultset it can be none, single row or full resultset.
Anyways for your scenario it would be None only

Also are any of tables used by more than one sql task?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

LearningSQLKid
Yak Posting Veteran

51 Posts

Posted - 2011-12-12 : 05:34:42

quote:
Originally posted by visakh16

quote:
Originally posted by LearningSQLKid

quote:
Originally posted by visakh16

what are values you've set for resultset queries and in variables mapping tab?
also is one sql task using value returned by another sql task?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Question:what are values you've set for resultset queries and in variables mapping tab?

Answer: Resultset=None; I'm not using any parameter at this time.

Question: Is one sql task using value returned by another sql task?
Answer: NO

Select Knowledge from LearningProcess

Resultset has nothing to do with parameters. it just maps the columns in your resultset to variable you provide in variables mapping tab. so depending on your resultset it can be none, single row or full resultset.
Anyways for your scenario it would be None only

Also are any of tables used by more than one sql task?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Hi Friend

Nope,each table is used once.

Select Knowledge from LearningProcess

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-12 : 10:31:25

hmm...I'm going out of ideas here
can you try running the same queries in sql management studio window and see if it gives some error?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

LearningSQLKid
Yak Posting Veteran

51 Posts

Posted - 2011-12-19 : 00:17:13

quote:
Originally posted by visakh16

hmm...I'm going out of ideas here
can you try running the same queries in sql management studio window and see if it gives some error?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Select Knowledge from LearningProcess

LearningSQLKid
Yak Posting Veteran

51 Posts

Posted - 2011-12-19 : 00:20:46

quote:
Originally posted by visakh16

hmm...I'm going out of ideas here
can you try running the same queries in sql management studio window and see if it gives some error?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Hi.

Well, i just got the solution.

First There is a connection property called "RetainSameConnection". Set it to TRUE

Second: Active Multiple Actibe Recordsets (MARS) by setting MARS Property of connection to true.

It worked great for me :)

Thanks for your support and help.

Appreciated

Select Knowledge from LearningProcess

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources