Help with CustomComponent in SSIS-DataFlow

Hello Trying to figure out a clever solution for splitting multivalued columns out into n-columns. For that I've build a custom component in SSIS using ms-help://MS.VSCC.v80/MS.VSIPCC.v80/MS.SQLSVR.v9.en/dtsref9/html/4dc0f631-8fd6-4007-b573-ca67f58ca068.htm as an example. I need to be able to add columns to the OutputCollection in designtime, but the designer returns an error: Error at Data Flow Task [Uppercase [5910]]: The component "Uppercase" (5910) does not allow setting output column datatype properties. How do I enable the designer to accept designtime changes in the columncollection?  Kind regards

You have to override the method SetOutputColumnDataTypeProperties in your component and implement it like this:

public override void SetOutputColumnDataTypeProperties(int outputID, int outputColumnID, Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType dataType, int length, int precision, int scale, int codePage)

{

IDTSOutputCollection90 outputColl = this.ComponentMetaData.OutputCollection;

IDTSOutput90 output = outputColl.GetObjectByID(outputID);

IDTSOutputColumnCollection90 columnColl = output.OutputColumnCollection;

IDTSOutputColumn90 column = columnColl.GetObjectByID(outputColumnID);

column.SetDataTypeProperties(dataType, length, precision, scale, codePage);

}

Help with converting code: VB code in SQL Server 2000->Visual Studio BI 2005

Hi all--I'm trying to convert a function which I inherited from a SQL Server 2000 DTS package to something usable in an SSIS package in SQL Server 2005. Given the original code here:

Function Main()

on error resume next

dim cn, i, rs, sSQL

Set cn = CreateObject("ADODB.Connection")

cn.Open "Provider=sqloledb;Server=<server_name>;Database=<db_name>;User ID=<sysadmin_user>;Password=<password>"

set rs = CreateObject("ADODB.Recordset")

set rs = DTSGlobalVariables("SQLstring").value

for i = 1 to rs.RecordCount

sSQL = rs.Fields(0).value

cn.Execute sSQL, , 128'adExecuteNoRecords option for faster execution

rs.MoveNext

Next

Main = DTSTaskExecResult_Success

End Function

This code was originally programmed in the SQL Server ActiveX Task type in a DTS package designed to take an open-ended number of SQL statements generated by another task as input, then execute each SQL statement sequentially. Upon this code's success, move on to the next step. (Of course, there was no additional documentation with this code. :-)

Based on other postings, I attempted to push this code into a Visual Studio BI 2005 Script Task with the following change:

public Sub Main()

...

Dts.TaskResult = Dts.Results.Success

End Class

I get the following error when I attempt to compile this:

Error 30209: Option Strict On requires all variable declarations to have an 'As' clause.

I am new to Visual Basic, so I'm on a learning curve here. From what I know of this script:
- The variables here violate the new Option Strict On requirement in VS 2005 to declare what type of object your variable is supposed to use.

- I need to explicitly declare each object, unless I turn off the Option Strict On (which didn't seem recommended, based on what I read).

Given this statement:

dim cn, i, rs, sSQL

I'm looking at "i" as type Integer; rs and sSQL are open-ended arrays, but can't quite figure out how to read the code here:

Set cn = CreateObject("ADODB.Connection")

cn.Open "Provider=sqloledb;Server=<server_name>;Database=<db_name>;User ID=<sysadmin_user>;Password=<password>"

set rs = CreateObject("ADODB.Recordset")

This code seems to create an instance of a COM component, then pass provider information and create the recordset being passed in by the previous task, but am not sure whether this syntax is correct for VS 2005 or what data type declaration to make here. Any ideas/help on how to rewrite this code would be greatly appreciated!

Option Strict controls what we call "late binding." If option strict is ON, then what happens is that the compiler will generate code that takes full advantage of the type system in the CLR. This means that each variable has a compile-time type (hence you need to specify the "As" clause), and because each variable has a compile-time type, the IL that the compiler generates is fast (for example, method calls are translated to a "call" or a "callvirt" IL instruction).

If you turn option strict OFF, this is what is known as the "late binding" mode. There is no equivalent in C#. What the late binding mode allows you to do is to leave variables specified as "object" (and if you omit the "As" clause, the type is assumed to be "object"). The compiler, because it does not know what the type is, needs to generate code that calls the Visual Basic Runtime helpers to execute method calls.

Because of this, the compiler cannot provide checks for you during compile time. For example, you can do something like this:

dim o = CreateObject("ADODB.Connection")
o.foo()

The compiler will not check whether the "foo" method exists, because it doesn't know the type of o. And if "foo" indeed does not exist, you will get a runtime exception. Runtime exceptions are much harder to debug and diagnose; this is probably why people advise against turning option strict off.

However, in certain scenarios, such as yours, I believe that turning option strict off will make life easier. The compiler may not be as helpful, but it will make your code easier to understand.

The CreateObject calls are indeed creating COM objects. Your analysis of what the code is doing is correct. The .NET equivalent of the ADODB.Connection and ADODB.Recordset classes live in the "System.Data" namespace. Take a look here for some information: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfSystemData.asp

I hope this helps - feel free to post any more questions or issues that you run into here :)|||

Hi Timothy--Thanks for the analysis, and the suggestion of turning this option off. How do I do that for just this package? I read somewhere (don't remember the URL at the moment) that I had to turn this option off through a configuration parameter in a file ending in .vbproj. However, the ultimate goal is store this package in the SQL Server database and so give it the ability to schedule it through the SQL Server Agent. That doesn't seem feasible if I have to store connection information externally, which is a security risk...and I can't find any files with that name anyway.

I tried to add the following into the function:

Option Strict Off

That caused another compilation error, unless I'm missing something else. Any ideas on how to turn the Option Strict off for just this task?

|||

Better to leave Option Strict On.

Dim i as Integer

Dim cn as ADODB.Connection

Dim rs as ADODB.Recordset

These declarations will require a reference in the Script task project to the ADODB primary interop assembly.

-Doug

|||What was the error you get with Option Strict Off?

The finest level of granularity is to turn option strict off at the file level - put this at the top of the file:
Option Strict Off

If you pursue any solution where you need to turn on/off option strict, it's best to at the very least put the code you want option strict off for in another file.

I agree too that Option Strict should typically be left On, but there are situations where turning it Off can make it easier to code, at the expenses that I mentioned above.|||

Thanks all--These prior posts helped me work around the problem. It's appreciated--problem solved!

- Jonathan

Help with converting code: VB code in SQL Server 2000->Visual Studio BI 2005

Hi all--I'm trying to convert a function which I inherited from a SQL Server 2000 DTS package to something usable in an SSIS package in SQL Server 2005. Given the original code here:

Function Main()

on error resume next

dim cn, i, rs, sSQL

Set cn = CreateObject("ADODB.Connection")

cn.Open "Provider=sqloledb;Server=<server_name>;Database=<db_name>;User ID=<sysadmin_user>;Password=<password>"

set rs = CreateObject("ADODB.Recordset")

set rs = DTSGlobalVariables("SQLstring").value

for i = 1 to rs.RecordCount

sSQL = rs.Fields(0).value

cn.Execute sSQL, , 128'adExecuteNoRecords option for faster execution

rs.MoveNext

Next

Main = DTSTaskExecResult_Success

End Function

This code was originally programmed in the SQL Server ActiveX Task type in a DTS package designed to take an open-ended number of SQL statements generated by another task as input, then execute each SQL statement sequentially. Upon this code's success, move on to the next step. (Of course, there was no additional documentation with this code. :-)

Based on other postings, I attempted to push this code into a Visual Studio BI 2005 Script Task with the following change:

public Sub Main()

...

Dts.TaskResult = Dts.Results.Success

End Class

I get the following error when I attempt to compile this:

Error 30209: Option Strict On requires all variable declarations to have an 'As' clause.

I am new to Visual Basic, so I'm on a learning curve here. From what I know of this script:
- The variables here violate the new Option Strict On requirement in VS 2005 to declare what type of object your variable is supposed to use.

- I need to explicitly declare each object, unless I turn off the Option Strict On (which didn't seem recommended, based on what I read).

Given this statement:

dim cn, i, rs, sSQL

I'm looking at "i" as type Integer; rs and sSQL are open-ended arrays, but can't quite figure out how to read the code here:

Set cn = CreateObject("ADODB.Connection")

cn.Open "Provider=sqloledb;Server=<server_name>;Database=<db_name>;User ID=<sysadmin_user>;Password=<password>"

set rs = CreateObject("ADODB.Recordset")

This code seems to create an instance of a COM component, then pass provider information and create the recordset being passed in by the previous task, but am not sure whether this syntax is correct for VS 2005 or what data type declaration to make here. Any ideas/help on how to rewrite this code would be greatly appreciated!

Option Strict controls what we call "late binding." If option strict is ON, then what happens is that the compiler will generate code that takes full advantage of the type system in the CLR. This means that each variable has a compile-time type (hence you need to specify the "As" clause), and because each variable has a compile-time type, the IL that the compiler generates is fast (for example, method calls are translated to a "call" or a "callvirt" IL instruction).

If you turn option strict OFF, this is what is known as the "late binding" mode. There is no equivalent in C#. What the late binding mode allows you to do is to leave variables specified as "object" (and if you omit the "As" clause, the type is assumed to be "object"). The compiler, because it does not know what the type is, needs to generate code that calls the Visual Basic Runtime helpers to execute method calls.

Because of this, the compiler cannot provide checks for you during compile time. For example, you can do something like this:

dim o = CreateObject("ADODB.Connection")
o.foo()

The compiler will not check whether the "foo" method exists, because it doesn't know the type of o. And if "foo" indeed does not exist, you will get a runtime exception. Runtime exceptions are much harder to debug and diagnose; this is probably why people advise against turning option strict off.

However, in certain scenarios, such as yours, I believe that turning option strict off will make life easier. The compiler may not be as helpful, but it will make your code easier to understand.

The CreateObject calls are indeed creating COM objects. Your analysis of what the code is doing is correct. The .NET equivalent of the ADODB.Connection and ADODB.Recordset classes live in the "System.Data" namespace. Take a look here for some information: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfSystemData.asp

I hope this helps - feel free to post any more questions or issues that you run into here :)|||

Hi Timothy--Thanks for the analysis, and the suggestion of turning this option off. How do I do that for just this package? I read somewhere (don't remember the URL at the moment) that I had to turn this option off through a configuration parameter in a file ending in .vbproj. However, the ultimate goal is store this package in the SQL Server database and so give it the ability to schedule it through the SQL Server Agent. That doesn't seem feasible if I have to store connection information externally, which is a security risk...and I can't find any files with that name anyway.

I tried to add the following into the function:

Option Strict Off

That caused another compilation error, unless I'm missing something else. Any ideas on how to turn the Option Strict off for just this task?

|||

Better to leave Option Strict On.

Dim i as Integer

Dim cn as ADODB.Connection

Dim rs as ADODB.Recordset

These declarations will require a reference in the Script task project to the ADODB primary interop assembly.

-Doug

|||What was the error you get with Option Strict Off?

The finest level of granularity is to turn option strict off at the file level - put this at the top of the file:
Option Strict Off

If you pursue any solution where you need to turn on/off option strict, it's best to at the very least put the code you want option strict off for in another file.

I agree too that Option Strict should typically be left On, but there are situations where turning it Off can make it easier to code, at the expenses that I mentioned above.|||

Thanks all--These prior posts helped me work around the problem. It's appreciated--problem solved!

- Jonathan

Help with agent job to import from ODBC?

SQL Server 2005 Standard Edition.
Using wizard, no problem to use SQL to import from ODBC data source. Saved
the DTS job to an SSIS package, unable to use with agent scheduler.
Error is DTS_E_PRODUCTLEVELTOLOW.
Please help why is this happening?Frank
Do you want to move jobs to SQL Server 2005?
"Frank Ricciardi" <FrankRicciardi@.discussions.microsoft.com> wrote in
message news:0EE47C4D-589D-4B95-81EB-7F1DE5988A2E@.microsoft.com...
> SQL Server 2005 Standard Edition.
> Using wizard, no problem to use SQL to import from ODBC data source. Saved
> the DTS job to an SSIS package, unable to use with agent scheduler.
> Error is DTS_E_PRODUCTLEVELTOLOW.
> Please help why is this happening?|||I just want them to run as scheduled without operator intervention. I don't
really care how it do it.
Does that make sense?
thanks in advance..
"Uri Dimant" wrote:
> Frank
> Do you want to move jobs to SQL Server 2005?
>
> "Frank Ricciardi" <FrankRicciardi@.discussions.microsoft.com> wrote in
> message news:0EE47C4D-589D-4B95-81EB-7F1DE5988A2E@.microsoft.com...
> > SQL Server 2005 Standard Edition.
> >
> > Using wizard, no problem to use SQL to import from ODBC data source. Saved
> > the DTS job to an SSIS package, unable to use with agent scheduler.
> >
> > Error is DTS_E_PRODUCTLEVELTOLOW.
> >
> > Please help why is this happening?
>
>

Help with agent job to import from ODBC?

SQL Server 2005 Standard Edition.
Using wizard, no problem to use SQL to import from ODBC data source. Saved
the DTS job to an SSIS package, unable to use with agent scheduler.
Error is DTS_E_PRODUCTLEVELTOLOW.
Please help why is this happening?
Frank
Do you want to move jobs to SQL Server 2005?
"Frank Ricciardi" <FrankRicciardi@.discussions.microsoft.com> wrote in
message news:0EE47C4D-589D-4B95-81EB-7F1DE5988A2E@.microsoft.com...
> SQL Server 2005 Standard Edition.
> Using wizard, no problem to use SQL to import from ODBC data source. Saved
> the DTS job to an SSIS package, unable to use with agent scheduler.
> Error is DTS_E_PRODUCTLEVELTOLOW.
> Please help why is this happening?
|||I just want them to run as scheduled without operator intervention. I don't
really care how it do it.
Does that make sense?
thanks in advance..
"Uri Dimant" wrote:

> Frank
> Do you want to move jobs to SQL Server 2005?
>
> "Frank Ricciardi" <FrankRicciardi@.discussions.microsoft.com> wrote in
> message news:0EE47C4D-589D-4B95-81EB-7F1DE5988A2E@.microsoft.com...
>
>

Help W/Disabling FK Constraints for Batch Operations

HI all,

I'm trying to have a SProc that will initialize a database for me. This db is in development (I'm primarily writing SSIS packages, atm), and I constantly need to truncate the tables, then re-add a dummy/unknown row (PK/Identity value = 1). Of course, I need triggers not to fire (got that part working), and FK constraints to be bypassed temporarily -- that's the problem.

Here's where I'm at:

-
CREATE PROCEDURE [dbo].[_InitializeDB]
AS

SET NOCOUNT ON

DECLARE @.name varchar(255)
DECLARE @.sql nvarchar(255)

DECLARE tables CURSOR FOR SELECT [name] FROM [sysobjects] WHERE [type]='U' AND [name]<>'sysdiagrams'

OPEN tables
FETCH NEXT FROM tables INTO @.name
WHILE @.@.FETCH_STATUS=0
BEGIN
SET @.sql = 'ALTER TABLE ['+ @.name + '] NOCHECK CONSTRAINT ALL'
EXEC sp_executeSQL @.sql
SET @.sql = 'DISABLE TRIGGER ALL ON [' + @.name + ']'
EXEC sp_executeSQL @.sql
SET @.sql = 'TRUNCATE TABLE [' + @.name + ']'
EXEC sp_executesql @.sql
BEGIN TRY
SET @.sql = 'INSERT INTO [' + @.name + '] (Active) VALUES (0)'
EXEC sp_executeSQL @.sql
END TRY
BEGIN CATCH
PRINT @.sql + ':'
PRINT ERROR_MESSAGE()
END CATCH
SET @.sql = 'ENABLE TRIGGER ALL ON [' + @.name + ']'
EXEC sp_executeSQL @.sql
SET @.sql = 'ALTER TABLE ['+ @.name + '] CHECK CONSTRAINT ALL'
EXEC sp_executeSQL @.sql
FETCH NEXT FROM tables INTO @.name
END

CLOSE tables
DEALLOCATE tables
-

Running this Sproc produces (for the first ref'd table):

Msg 4712, Level 16, State 1, Line 1
Cannot truncate table 'Person' because it is being referenced by a FOREIGN KEY constraint.

If you dont wanna be bothered by the FK Constraints you have to drop them do your work and recreate them after you did your work.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||TRUNCATE TABLE doesn't check the state of the FK constraint so this method will not work. You will have to drop and recreate the FK constraint for the truncate table to work.

Help to encrypt a flat file destination

I am using SSIS to create a weekly data extraction that will be emailed to an external agency. I can extract the data, create the file and email it without any problems but I want to compress and encrypt it before I send to give some measure of protection to sensitive data it would contain.

If I did this manually, I would simply use Winzip to compress and encrypt the file to be sent. How can I achieve a similar result programtically?

Set a step to run a command, and use the command line functionality of Winzip.

Help --SSIS Dataflow Task

Need help regarding ssis dataflow task
I need to create a ssis package. I want to import the data from a flat
file to a table.
Lets say, the table has 5 columns -- col1, col2, col3, col4 ,
col5.(Assume that all columns can be NULLABLE) The datafile contains
the data related to only three columns say col1, col2, col3. So when I
use dataflow task to import the data from the file to the table, I
will only get three columns, col1, col2, col3. Columns col4, col5 will
be NULL.
However, I want to populate columns col4, col5 with some values which
are stored in the variable.
IS there any way to do this?
Any help would be appreciated.
Thanks
Vishal wrote:
> Need help regarding ssis dataflow task
> I need to create a ssis package. I want to import the data from a flat
> file to a table.
> Lets say, the table has 5 columns -- col1, col2, col3, col4 ,
> col5.(Assume that all columns can be NULLABLE) The datafile contains
> the data related to only three columns say col1, col2, col3. So when I
> use dataflow task to import the data from the file to the table, I
> will only get three columns, col1, col2, col3. Columns col4, col5 will
> be NULL.
> However, I want to populate columns col4, col5 with some values which
> are stored in the variable.
> IS there any way to do this?
> Any help would be appreciated.
> Thanks
Suppose I have one variable which contains some value. @.[User::exp1]
Now create one more variable which contains source query.
set evaluateasexpression as true for this variable.
set expression like this
"select col1,col2,col3,'"+@.[User::exp1] + "' as col4 from a1"
In Data flow task in source select sql command from variable and select
this variable.
Regards
Amish Shah
http://shahamishm.tripod.com

Help --SSIS Dataflow Task

Need help regarding ssis dataflow task
I need to create a ssis package. I want to import the data from a flat
file to a table.
Lets say, the table has 5 columns -- col1, col2, col3, col4 ,
col5.(Assume that all columns can be NULLABLE) The datafile contains
the data related to only three columns say col1, col2, col3. So when I
use dataflow task to import the data from the file to the table, I
will only get three columns, col1, col2, col3. Columns col4, col5 will
be NULL.
However, I want to populate columns col4, col5 with some values which
are stored in the variable.
IS there any way to do this'
Any help would be appreciated.
ThanksVishal wrote:
> Need help regarding ssis dataflow task
> I need to create a ssis package. I want to import the data from a flat
> file to a table.
> Lets say, the table has 5 columns -- col1, col2, col3, col4 ,
> col5.(Assume that all columns can be NULLABLE) The datafile contains
> the data related to only three columns say col1, col2, col3. So when I
> use dataflow task to import the data from the file to the table, I
> will only get three columns, col1, col2, col3. Columns col4, col5 will
> be NULL.
> However, I want to populate columns col4, col5 with some values which
> are stored in the variable.
> IS there any way to do this'
> Any help would be appreciated.
> Thanks
Suppose I have one variable which contains some value. @.[User::exp1]
Now create one more variable which contains source query.
set evaluateasexpression as true for this variable.
set expression like this
"select col1,col2,col3,'"+@.[User::exp1] + "' as col4 from a1"
In Data flow task in source select sql command from variable and select
this variable.
Regards
Amish Shah
http://shahamishm.tripod.com

Help --SSIS Dataflow Task

Need help regarding ssis dataflow task
I need to create a ssis package. I want to import the data from a flat
file to a table.
Lets say, the table has 5 columns -- col1, col2, col3, col4 ,
col5.(Assume that all columns can be NULLABLE) The datafile contains
the data related to only three columns say col1, col2, col3. So when I
use dataflow task to import the data from the file to the table, I
will only get three columns, col1, col2, col3. Columns col4, col5 will
be NULL.
However, I want to populate columns col4, col5 with some values which
are stored in the variable.
IS there any way to do this'
Any help would be appreciated.
ThanksVishal wrote:
> Need help regarding ssis dataflow task
> I need to create a ssis package. I want to import the data from a flat
> file to a table.
> Lets say, the table has 5 columns -- col1, col2, col3, col4 ,
> col5.(Assume that all columns can be NULLABLE) The datafile contains
> the data related to only three columns say col1, col2, col3. So when I
> use dataflow task to import the data from the file to the table, I
> will only get three columns, col1, col2, col3. Columns col4, col5 will
> be NULL.
> However, I want to populate columns col4, col5 with some values which
> are stored in the variable.
> IS there any way to do this'
> Any help would be appreciated.
> Thanks
Suppose I have one variable which contains some value. @.[User::exp1]
Now create one more variable which contains source query.
set evaluateasexpression as true for this variable.
set expression like this
"select col1,col2,col3,'"+@.[User::exp1] + "' as col4 from a1"
In Data flow task in source select sql command from variable and select
this variable.
Regards
Amish Shah
http://shahamishm.tripod.com

Help required using Variables and Comparing them in SSIS

Hi i am New to ssis and Scripting in ssis what i was trying to do was to compare few variables which i populate using execute sql tasks and determine which process needs to kick off based on Task success or Failure

are the variables i am populating Mapping them to result set in execute sql task.

This is the criteria for script task to Fail or success

I am not able to populate the varibales with the Values from Execute Sql and also i am encountering errors in the script task.

I am Declaring ex: myvar11 as variables and then using it , please help me as i am new and guide me for the right approach. and can this logic be implemented in ssis for example like && operator used in script.
End IF

I've done something similar to this, albeit much simpler.

I created an Execute SQL task to select a single row from a table.

In the SQL Task Editor, General, I specified ResultSet = Single row, SQLSourceType = Direct Input, and entered the Select SQL in the SQLStatement field.

In the Result Set pane, I mapped the Result Names to the Variable Names using 0 for column1, 1 for columnn 2 etc.

Then in the Precedence Constraint Editor, I selected Evaluation Operation = Expression and Constraint, Value = Success, and entered my expression using the Variable Names.

The result of the expression controls the conditional execution of one of the two following data flows

|||

We have done something similar in a script task, you should be able to call the required variables using something similar to below

If Dts.Variables("gvMedSumFileMode".ToString() = "P" && Dts.Variables("gvMedSumFileType").ToString() = "U" then

if cint(Dts.Variables("gvMedSumFileVolume").ToString()) = cint(dts.Variables("gvMedCtrlFileVolume").ToString()) + 1 then

Dts.Task.Result = success

....

....

....

|||

Dev2624 wrote:

Hi i am New to ssis and Scripting in ssis what i was trying to do was to compare few variables which i populate using execute sql tasks and determine which process needs to kick off based on Task success or Failure

gvMedSumFileMode

gvMedSumFileType

gvMedSumFileVolume

gvMedCtrlFileVolume

gvMedSumSupplementNumber

gvMedCtrlSupplementNumber

are the variables i am populating Mapping them to result set in execute sql task.

This is the criteria for script task to Fail or success

If myvar11("gvMedSumFileMode").ToString = "P" && myVar12("gvMedSumFileType").ToString = "U" Then

If CType(myVar13("gvMedSumFileVolume").Value, Integer) = CType(myVar14("gvMedCtrlFileVolume").Value, Integer) + 1 Then

DTS.Task .Result = success

else

if CType(myVar13("gvMedSumFileVolume").Value, Integer) = CType(myVar14("gvMedCtrlFileVolume").Value, Integer) && CType(myVar15("gvMedSumSupplementNumber").Value, Integer) = CType(myVar16("gvMedCtrlSupplementNumber").Value, Integer) + 1 then

DTS.Task .Result = success

else
DTS.Task .Result = Failure

End if

End if

else

DTS.Task .Result = failure

I am not able to populate the varibales with the Values from Execute Sql and also i am encountering errors in the script task.

I am Declaring ex: myvar11 as variables and then using it , please help me as i am new and guide me for the right approach. and can this logic be implemented in ssis for example like && operator used in script.
End IF

I agree with the first answer; using expressions in the precedence constratint is the best way to 'direct' the traffic in your control flow, as they are easier to maintain and implement. Make sure you research on 'Precedence constraints'.|||

When you are using DTS.Variables are you writing them in the main page of script task under readonly Variables or declaring the variables as MyVariables is the right approach, also the Previous reply to this that i have followed the same method and trying to populate the variables , Can you please guide me as to how can we use these as expressions(in my case)

where conditions are for ex if varx="p" && vary="u"

then if varz= varc+1 success

or other case is if varx="p" && vary="u"

then if varz=varc

if varg=varh+1

success

how do we acheive these conditions in expressions.

|||

Also when i am trying to use && to evaluate both the variables at once it is not allowing me to do it , its throwing an error as expression expected .The questions i might ask might be trivial to you but please help me as i am a newbie and learning the steps ..

also is cint is the right word since the variable gvmedsumfilevolume is int in type. and i am trying to compare two integer values in the expression.

|||

Sorry I was getting confised between c# and vb.net. Below is the correct code, the variables that you are using within the script must also be placed in the "ReadOnlyVariables" property on the "Script Task Editor" > "Script" page. They should be in the format

gvMedSumFileMode,gvMedSumFileType,gvMedSumFileVolume",gvMedCtrlFileVolume

Note that there are no gaps between the commas and the next variable. Not sure if this is a bug but we found that it would ignore the remaining variables if spaces were included.

If Dts.Variables("gvMedSumFileMode").Value.ToString() = "P" And Dts.Variables("gvMedSumFileType").Value.ToString = "U" Then

If CInt(Dts.Variables("gvMedSumFileVolume").Value.ToString()) = CInt(Dts.Variables("gvMedCtrlFileVolume").Value.ToString()) + 1 Then

Dts.TaskResult = Dts.Results.Success

Else

If CInt(Dts.Variables("gvMedSumFileVolume").Value.ToString()) = CInt(Dts.Variables("gvMedCtrlFileVolume").Value.ToString()) And _

CInt(Dts.Variables("gvMedSumSupplementNumber").Value.ToString()) = CInt(Dts.Variables("gvMedCtrlSupplementNumber").Value.ToString()) + 1 Then

Dts.TaskResult = Dts.Results.Success

Else

Dts.TaskResult = Dts.Results.Failure

End If

End If

Else

Dts.TaskResult = Dts.Results.Failure

End If

|||

You should be using the variabledispenser... BEWARE :-)

http://blogs.conchango.com/jamiethomson/archive/2007/08/28/Beware-of-variable-usage-in-script-tasks.aspx

|||

This is the code that i am using and the task is failing is this the right way to do this . I am populating the variables using sql direct input and mapping them into the result set.

Variable names have changed and made these changes in the code. Please tell me how to resolve this

MedSumFileMode String

MedSumFileType String

MedSumFileVolume int32 rest all variables are type int32.

|||Dev, please share the error message. That would help, don't you think?

Also, You don't want LockOneForWrite unless you're only working with one variable. In this case you're not, so you'll want to use LockForWrite instead. If you're not writing to the variables, use LockForRead. Search for examples, but for starters:

Code Snippet

...

Dim vars As Variables
Dts.VariableDispenser.LockForRead("MedSumFileMode",vars)
Dts.VariableDispenser.LockForRead("MedSumFileType",vars)
Dts.VariableDispenser.LockForRead("MedSumFileVolume",vars)
Dts.VariableDispenser.LockForRead("MedCtrlFileVolume",vars)
Dts.VariableDispenser.LockForRead("MedSumSupplementNumber",vars)
Dts.VariableDispenser.LockForRead("MedCtrlSupplementNumber",vars)
Dts.VariableDispenser.GetVariables(vars)

If vars("MedSumFileMode").Value.ToString = "P" And vars("MedSumFileType").Value.ToString = "U" Then.......

|||

Dim vars As Variables
Dts.VariableDispenser.LockForRead("MedSumFileMode",vars)
Dts.VariableDispenser.LockForRead("MedSumFileType",vars)
Dts.VariableDispenser.LockForRead("MedSumFileVolume",vars)
Dts.VariableDispenser.LockForRead("MedCtrlFileVolume",vars)
Dts.VariableDispenser.LockForRead("MedSumSupplementNumber",vars)
Dts.VariableDispenser.LockForRead("MedCtrlSupplementNumber",vars)
Dts.VariableDispenser.GetVariables(vars)

If vars("MedSumFileMode").Value.ToString = "P" And vars("MedSumFileType").Value.ToString = "U" Then.......

when i use this it highlights an error saying too many arguments Public SubLock For Read(variable As String)

|||

If i use Dim vars As Variables
Dts.VariableDispenser.LockOneForRead("MedSumFileMode",vars)
the error goes away. is this the right approach.

ill post back with the error details

|||

Dev2624 wrote:

Dim vars As Variables
Dts.VariableDispenser.LockForRead("MedSumFileMode",vars)
Dts.VariableDispenser.LockForRead("MedSumFileType",vars)
Dts.VariableDispenser.LockForRead("MedSumFileVolume",vars)
Dts.VariableDispenser.LockForRead("MedCtrlFileVolume",vars)
Dts.VariableDispenser.LockForRead("MedSumSupplementNumber",vars)
Dts.VariableDispenser.LockForRead("MedCtrlSupplementNumber",vars)
Dts.VariableDispenser.GetVariables(vars)

If vars("MedSumFileMode").Value.ToString = "P" And vars("MedSumFileType").Value.ToString = "U" Then.......

when i use this it highlights an error saying too many arguments Public SubLock For Read(variable As String)

Yep, I'm not quite awake yet this morning.

Code Snippet

Dim vars As Variables
Dts.VariableDispenser.LockForRead("MedSumFileMode")
Dts.VariableDispenser.LockForRead("MedSumFileType")
Dts.VariableDispenser.LockForRead("MedSumFileVolume")
Dts.VariableDispenser.LockForRead("MedCtrlFileVolume")
Dts.VariableDispenser.LockForRead("MedSumSupplementNumber")
Dts.VariableDispenser.LockForRead("MedCtrlSupplementNumber")
Dts.VariableDispenser.GetVariables(vars)

If vars("MedSumFileMode").Value.ToString = "P" And vars("MedSumFileType").Value.ToString = "U" Then.......

|||

The element cannot be found in a collection. This error happens when you try to retrieve an element from a collection on a container during execution of the package and the element is not there.

at Microsoft.SqlServer.Dts.Runtime.Variables.get_Item(Object index)

at ScriptTask_08359122705d4affbf377ddc743eea59.ScriptMain.Main() in dts://Scripts/ScriptTask_08359122705d4affbf377ddc743eea59/ScriptMain:line 30

this is the error .

|||

Dim vars As Variables
Dts.VariableDispenser.LockForRead("MedSumFileMode")
Dts.VariableDispenser.LockForRead("MedSumFileType")
Dts.VariableDispenser.LockForRead("MedSumFileVolume")
Dts.VariableDispenser.LockForRead("MedCtrlFileVolume")
Dts.VariableDispenser.LockForRead("MedSumSupplementNumber")
Dts.VariableDispenser.LockForRead("MedCtrlSupplementNumber")
Dts.VariableDispenser.GetVariables(vars)

If vars("MedSumFileMode").Value.ToString = "P" And vars("MedSumFileType").Value.ToString = "U" Then.......

The problem with this method is that the vars collection does not take the string name of the variable it will only take the index of the variable, hence the code becomes difficult to read

If vars(0).Value.ToString = "P" And vars(1).Value.ToString = "U" Then.......