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?
Have you tried putting your query in a stored procedure?
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.
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
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.
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.
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
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
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
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.
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.
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