Home » Microsoft TechnologiesRSS

How to modify OLEDB SQLCommand from Script Task in SSIS 2008

I have to upgrade from SQL Server 2000 DTs to SSIS 2008. The DTS modify the dtspumptask dinamically. I dont now hot to do the same. I tried to do the same with variables but queries have more than 4000 characters.

Help please.

 

Regards

 

 

 

 

5 Answers Found

 

Answer 1

In case the queries have more than 4000 characters, you can set the variable's value inside a script  task instead of setting an expression for those variables. Check this link: http://consultingblogs.emc.com/jamiethomson/archive/2005/02/09/SSIS_3A00_-Writing-to-a-variable-from-a-script-task.aspx
 

Answer 2

I am agree with Nitesh,

you can use variables but not as expression.

this post from Jamie talks clearly about when you can save more than 4000 characters in ssis  variables and when you can not:

http://consultingblogs.emc.com/jamiethomson/archive/2009/05/27/4000-character-limit-in-ssis.aspx

 

Answer 3

Thanks Nitesh and Reza, but the main problem is not the 4000 caharacteres, The problem is how can I do the following code in SSIS 2008???

Regards

               VBScript Task

………………..

 

……

                Set pkg = DTSGlobalVariables.Parent"

                ' MODIFICACIÓN DATOS TRANSFORMACIÓN: CARGA FLUJO CAJA"

                Set tsk = pkg.Tasks( ""DTSTask_DTSDataPumpTask_9"" )"

                Set cus = tsk.CustomTask"

                sqlOriginal = cus.SourceSQLStatement"

                sqlNueva = sqlOriginal"

                sqlNueva = Replace ( sqlNueva , ""GBLFECHAINICIO"",GblMesAnt )"

                sqlNueva = Replace ( sqlNueva , ""GBLFECHAFIN"", FechaFin )"

                cus.SourceSQLStatement = sqlNueva   ' Para modificar querys cuando están en una transformación."

 

……..

……….

Data PumP Task (Source Ole DB Oracle)

 

 

                SELECT *, ...............................

                'GBLFECHAINICIO' Fec1, "

                'GBLFECHAFIN' Fec2,"......................................where

                ....AND   A<= TO_DATE('GBLFECHAFIN','YYYYMMDD')"

                .......

 

 

 

 

Answer 4

I'm working with "Tomito" and this code is very easy in DTS 2000.
I have tried to do the same in SSIS 2008 but it is impossible with Scipt Task .NET change the SqlCommand property at runtime.
Regards
 

Answer 5

I think you'd want to use an OLEDB Source and select SQL Command from the drop down and place question marks where you want to modify  the criteria from a variable.  Then set up parameters to populate the question marks with the required values from a variable at run time. You'll also need to set up the variables and use an Execute SQL Task on the Control Pane to populate them with the needed data.

I hope I've understood, what you're trying to do correctly.

 

SELECT *, ...............................

                ? Fec1, "

                ? Fec2,"......................................where

                ....AND   A<= TO_DATE(? ,'YYYYMMDD')"

 
 
 

<< Previous      Next >>


Microsoft   |   Windows   |   Visual Studio   |   Follow us on Twitter