Max limit on size of query in SSIS OLEBD Source component

Who is online?  0 guests and 1 members
Home  »  Forums   »  microsoft business intelligence   »  integration services   » Max limit on size of query in SSIS OLEBD Source component

Max limit on size of query in SSIS OLEBD Source component

Topic RSS Feed

Posts under the topic: Max limit on size of query in SSIS OLEBD Source component

Posted: 10/8/2010

Jedi Youngling 16  points  Jedi Youngling
  • Joined on: 3/21/2010
  • Posts: 8

is there any max limit on size of query in SSIS OLEBD Source component?

size of my sql query is 84k. when I put in provided testbox it got tuncated. is ther any work arround to pass that limit?

 

Thanks

Pawan


Posted: 10/8/2010

Jedi Master 5963  points  Jedi Master
  • Joined on: 11/2/2009
  • Posts: 251

Have you tried putting your query in a stored procedure?


Posted: 10/8/2010

Jedi Master 5963  points  Jedi Master
  • Joined on: 11/2/2009
  • Posts: 251

Also, you could try putting your query in a variable (as long as its less than 8000 characters) and using the variable to hold the source query.


Posted: 10/8/2010

Jedi Youngling 16  points  Jedi Youngling
  • Joined on: 3/21/2010
  • Posts: 8

I tried using variabe but it didnt fit in there as query size is 84k ; I can try stored procedure but I also have a script task whcih modify SQL a bit based on diffrent conditions


Posted: 10/8/2010

Jedi Master 5646  points  Jedi Master
  • Joined on: 1/21/2010
  • Posts: 251

That is what I would recommend. Put it in a stored procedure or variable. Easy enough.

 

* UPDATE *

Use a stored procedure which will create a dynamic query to be returned to the package as a variable. I've used this before and it can handle quite a long script.


Posted: 10/8/2010

Jedi Master 5963  points  Jedi Master
  • Joined on: 11/2/2009
  • Posts: 251

You really should put it in a stored procedure. Remember, that 84k has to travel over the network each time the package runs when you could be just sending a handful of characters (exec some_proc). This is just one of the many reasons using procs is a best practice.


Posted: 10/8/2010

Jedi Youngling 16  points  Jedi Youngling
  • Joined on: 3/21/2010
  • Posts: 8

sending 84 k is fine as my data volume is very large approx 700-800 MB; if I wuse stored proc; SSIS packaeg has to execute to get column information / defination which will take a long time to execute and this might cause failiure in package validation at any point when it take longe then expected; especially in production enviroment;

I am thinking to put data in temp table using some stored proc and use that temp table for load process; but that will be my last option

 

 


Posted: 10/8/2010

Jedi Master 5963  points  Jedi Master
  • Joined on: 11/2/2009
  • Posts: 251

One way you can get around your SSIS package taking for ever to verify the meta data is to put a short circuited Select statement with the meta data defined. When you use a procedure, SSIS looks at the first select statement to try to get meta data. Add something like this to the top of the procedure to clearly lay out the meta data for SSIS so you can use the procedure.

 IF 1 = 0 
        BEGIN 

            SELECT CAST (NULL AS INT) AS ID_field, 
				CAST (NULL AS varchar(30)) AS Name_field,
				CAST (NULL AS varchar(30)) AS Other_name_field

        END 

 

Reading the data using your query, writing to the temp table, then reading the temp table into the package, and then writing where ever you are writing to will just increase the time it takes to execute the package.


Posted: 11/5/2010

Jedi Youngling 68  points  Jedi Youngling
  • Joined on: 12/21/2009
  • Posts: 4

One way around the 32K limit in the SSIS Text Editor is to simply not use it.  Put your query in a file, then load to the package with the Browse button in the OLE DB Source Editor.


Posted: 12/1/2010

Jedi Youngling 49  points  Jedi Youngling
  • Joined on: 5/14/2010
  • Posts: 1

The other way around is put your query in Script Component and assign that query to a variable and pass the variable as source in OLEDB Source Component.

 

Please let me know if you have any questions


Page 1 of 1 (10 items)