Script Transform

Hi,

My requirement is to check whether value of a particular column is null or not. if it is null I have to enter warning messages into the temp table I have created.

For this I am using Script Transform

Now I want to know how to write info from script transform to a table using SSIS.

Currently I am using the following code in script component

[Code]

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

Dim ConnString As String

ConnString = "Data Source=ABC;Initial Catalog=XXXX;Integrated Security=SSPI;"

Dim sqcn As New SqlConnection(ConnString)

''Dim sqlCmd As New SqlCommand(ConnString, sqcn)

'sqcn.Open()

if Row.Col1_IsNull Then

sqlCmd.CommandText = "Insert into AuditLog values('ERROR','Missing','" + Row.Col5 + "','" + Row.Col6 + "') "

sqlCmd.ExecuteNonQuery()

end if

End Sub

[/Code]

Without using SqlConnection and SqlCommand is thereany way I can get the Connection Obj and able to insert rec in the table.

Thanks

You can create ADO.NET Connection Manager object in the package, get it in script transfrom, and the AquireConnection method returns the managed connection object.

But why do you need script transform at all? You can use conditional split transform to find rows with missing column, and direct the output of conditional split transform to Sql Server or OLEDB Destination.

Script to change cluster index to non cluster

Hi all,
We have many tables which have cluster index on column with datatype 'Char(200)'.
Does anyone have script to change cluster index to noncluster for all user tables which have clustered index on a column with 'char(200)' datatype.
Thanks,
DeepakDon't know the direct way, but refer to this Devx article (http://www.dev-archive.com/codemag/Article/11516) to script the indexes and modify so on.

HTH|||Here's a link to the SQL Server system tables download:
http://www.microsoft.com/sql/techinfo/productdoc/2000/systables.asp

So, you would do something like this to get all the clustered indexes that includes a CHAR200 col:

SELECT i.name
FROM sysindexes i,
sysobjects o,
syscolumns c
WHERE o.id = i.id
AND o.type = 'U' -- user table
AND indid = 1 -- clustered index
AND o.id = c.id
AND c.type = ??
AND c.length = 200 ?

For c.type look through systypes and find the CHAR, I don't know offhand what the type ID for a char is. And I am not sure if c.length is 200 or what -- take a look in syscolumns and find a couple of the columns you are targeting and see if they have the same data.

Script to change a connection manager

I am looking for code or assistance on how to modify an existing conneciton manger via a script component. I want to have a script change the column sizes for an existing connection manager. How can I get a specific connection manger to change its values.

I have looked at the connection manager, connections, etc classes but I am not certain how to do this as .NET is not my cup of tea.

Thanks.

Once set up though, you can't change column sizes because it will break any associated metadata in the package.|||

Not what I wanted to hear....

Maybe I was thinking about this wrong. If I can determine the file type, I could just change the connection expression for the various components based on a variable.

|||

1Dave wrote:

Not what I wanted to hear....

Well, SSIS can't just dynamically adjust itself depending on any changes to the source. Just not going to happen. Too many components rely on knowing the metadata in advance.

1Dave wrote:

Maybe I was thinking about this wrong. If I can determine the file type, I could just change the connection expression for the various components based on a variable.

Package configurations can change connection strings at run time. You can use an XML file, SQL Server based configurations, etc... You can also override connection strings on the command line, I believe. If you choose to store a connection string in a variable, that variable can be overwritten via the command line as well.|||

Let me explain the issue a little more.

I get a flat file from a source that is not reliable. The file is fixed width style. Sometimes the file is 1200 chars and other times it is 1201. The column that is off is a filler column. But this one character causes wrapping issues that the data on each row is off by one if the file is 1201. So I wanted to just alter the connections filler column on the fly. How do I deal with this so that I dont duplicate code... I just want it to do all the same steps and mapp all the same fields regardless of file size.

|||

1Dave wrote:

Let me explain the issue a little more.

I get a flat file from a source that is not reliable. The file is fixed width style. Sometimes the file is 1200 chars and other times it is 1201. The column that is off is a filler column. But this one character causes wrapping issues that the data on each row is off by one if the file is 1201. So I wanted to just alter the connections filler column on the fly. How do I deal with this so that I dont duplicate code... I just want it to do all the same steps and mapp all the same fields regardless of file size.

You could treat it as a variable column file and parse it manually inside your data flow with either script or a complex derived column. The connection manager would read the whole row as a single column.

Or, following your original path, you could use code to modify *another* package and execute it. Your original approach won't work because you can't modify the current package. You can modify another package and run that one against your file.

Script component as Destination - HasSideEffects?

How to set 'HasSideEffects' property for any input column of a script component used as destination?

My Script component reads input, generates SQL update statements dynamically and fires them. There is no output. When executing, I get this error (which is self-explanatory).

"Script Component" (1573) has been removed from the Data Flow task because its output is not used and its inputs have no side effects. If the component is required, then the HasSideEffects property on at least one of its inputs should be set to true, or its output should be connected to something.

How do I get around this?

thanks,
Nitesh

Which build do you have and how do you create the components?

I've tested the recent build, when I create a script component
or destination, the input is marked as HasSideEffect = true
(it is property of the input, not particular column AFAIK).

You can check this in Input and Outputs page in component
editor. Select the input, it has property HasSideEffect = true.
It is read-only though, but should have the correct value.|||I had the same problem running on the september CTP.

The "HasSideEffects" property of the input to the script component was not enabled, so I couldn't change it.

A workaround is to create a dummy output for the script component. This output will automatically get HasSideEffects set to True and the component will not be removed.|||I have June CTP.
But, HasSideEffects is a readonly attribute of Input and it is set to false.
I can't change it.

Yeah, for now I am using the work around of using a dummy output.

thanks,
Nitesh|||I tested this in the current build and if you select that the script task is a destination then the input automatically gets HasSideEffects set to true. When you say you had the same problem on the Sept CTP did you create a new script task as a destination or did you open an already existing script task (it won't change since the property is persisted).

Two workarounds:
1. Set the RunInOptimizedMode property on the dataflow task to false. This should stop it from removing component but could impact performance if some other pruning made your package run faster. If nothing else was being pruned then you will notice no perf impact at all.
2. Give your input a strange name (e.g. Fix hassideefects on this input) and then edit the xml directly and search for the strange name and change the HasSideEffects on that input to true. I don't recommended this since editing the xml directly can have serious consequences if done incorrectly but it can be done as a last resort if using option 1 affects your perf too much.

HTH,
Matt

Script component - column iteration within

I have a synchronous script component and have added 5 fields to the output (field1, field2... field5). Can I iterate those fields?

In sudo language, I'd like to do:

Code Snippet

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
dim i as integer = 1
while i < 6
row.field[i] = "somevalue: " + i.ToString
end while
end sub

Somehow, I'd like to be able to do that without specifying each field individually:

row.field1 = "somevalue: " + "1"
row.field2 = "somevalue: " + "2"
...

Phil Brammer wrote:

I have a synchronous script component and have added 5 fields to the output (field1, field2... field5). Can I iterate those fields?

In sudo language, I'd like to do:

Code Snippet

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
dim i as integer = 1
while i < 6
row.field[i] = "somevalue: " + i.ToString
end while
end sub

Somehow, I'd like to be able to do that without specifying each field individually:

row.field1 = "somevalue: " + "1"
row.field2 = "somevalue: " + "2"
...

Phil,

This should put you on the right path:

Code Snippet

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

Dim col As IDTSOutputColumn90

For Each col In Me.ComponentMetaData.OutputCollection(0).OutputColumnCollection

MsgBox(col.Name)

Next

End Sub

-Jamie

P.S. How did you get all the code indented in your post?

|||

Question. Why on earth do you want to do this?

|||

Jamie Thomson wrote:

Question. Why on earth do you want to do this?

I'm trying to build an example for a poster from last night...

So the above works great for iterating the columns, now I need to assign a value to the output columns by iterating them.

Code Snippet

While counter < maxSegments
For Each col In Me.ComponentMetaData.OutputCollection(0).OutputColumnCollection

If col.Name.Contains("Segment" + (counter + 1).ToString) Then
' Below I need to take the current "column name" and assign a value to it in the output collection
Row.(col.Name) = myStringArray(counter)
Exit For
End If

Next
counter = counter + 1
End While

I just cut and paste with the indentions and it just works for me!|||

Jamie Thomson wrote:

Code Snippet

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

Dim col As IDTSOutputColumn90

For Each col In Me.ComponentMetaData.OutputCollection(0).OutputColumnCollection

MsgBox(col.Name)

Next

End Sub

-Jamie

P.S. How did you get all the code indented in your post?

How do you get color?

|||

JayH wrote:

How do you get color?

Let me guess, Jamie: Internet Explorer?|||

Phil Brammer wrote:

JayH wrote:

How do you get color?

Let me guess, Jamie: Internet Explorer?

I just copy and paste as well

I get colour but no indents. Phil gets indents but no colour. Weird.

I'm on Vista and IE7.

-Jamie

P.S. color not colour. Whoops - better not get into that!!!

|||

Phil Brammer wrote:

Let me guess, Jamie: Internet Explorer?

I think I tried IE, but it didn't do it either.

While I'm asking questions, how do users mark questions as "answered". I only see "Was this post helpful?" with Yes/No buttons. Is that it? I just assumed that only the OP had permission to mark the question as answered, and since I never start threads I just wasn't seeing it.
|||

JayH wrote:

Phil Brammer wrote:

Let me guess, Jamie: Internet Explorer?

I think I tried IE, but it didn't do it either.

While I'm asking questions, how do users mark questions as "answered". I only see "Was this post helpful?" with Yes/No buttons. Is that it? I just assumed that only the OP had permission to mark the question as answered, and since I never start threads I just wasn't seeing it.

That's correct. Only the OP and moderators can mark threads as answered.|||

Phil Brammer wrote:

JayH wrote:

Phil Brammer wrote:

Let me guess, Jamie: Internet Explorer?

I think I tried IE, but it didn't do it either.

While I'm asking questions, how do users mark questions as "answered". I only see "Was this post helpful?" with Yes/No buttons. Is that it? I just assumed that only the OP had permission to mark the question as answered, and since I never start threads I just wasn't seeing it.

That's correct. Only the OP and moderators can mark threads as answered.

really? dang. I didn't know that.

|||

Jamie Thomson wrote:

really? dang. I didn't know that.

What do you mean? aren't you a moderator? If so, you will see 'Mark as answer' buttom on each post

|||

Rafael Salas wrote:

Jamie Thomson wrote:

really? dang. I didn't know that.

What do you mean? aren't you a moderator? If so, you will see 'Mark as answer' buttom on each post

Yes, I'm a moderator. Hence I have the ability to 'mark as answered'. Hence I didn't know that none-moderators do not have the ability to do this.

-Jamie

|||jaegd has a wonderful example of a script component that does what I'm looking at:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=864401&SiteID=1

scptxfr.exe - generated script fails

Generated script fails because fucntions are added to this script after
tables in which this functions can be used for calculated column.
How can I resolve this?
Is there any usefull tool that can generate script with proper order of
dependence objects?
|||Download DataStudio from http://www.agileinfollc.com, it has very strong
scripting capability.
"Sergi Adamchuk" <adamchuk@.gmail.com> wrote in message
news:1124378725.452117.32190@.g47g2000cwa.googlegro ups.com...
> Is there any usefull tool that can generate script with proper order of
> dependence objects?
>
|||Can it do exactly that I need. It is 7 MB very big for me. I would like
to know weather this soft can do my work before downloading. Does
avaluatin versiong it?
|||DataStudio has evaluation version (http://www.agileinfollc.com/product.asp),
you can install and try it before you buy.
John King
http://www.agileinfollc.com
"Sergi Adamchuk" <adamchuk@.gmail.com> wrote in message
news:1124459262.366497.216500@.g47g2000cwa.googlegr oups.com...
> Can it do exactly that I need. It is 7 MB very big for me. I would like
> to know weather this soft can do my work before downloading. Does
> avaluatin versiong it?
>
|||I downloaded evaluation version. This software DOES NOT make working
SQL script for database creation. ((
The problem is still opened.
|||Can I temprally disable error rising if I'am creating a table that has
in its default value nonexisting function?

scopeing problem in an aggregate function

I have a matrix with two column groups. I need to be able have the user
select from my boolean parameter and change the scope on my aggregate
function. I already have my boolean parameter setup. the two groups are
called matrix1_Year and matrix1_Hub. i used the following expression in my
textbox
=Count(Fields!DeliveryNumber.Value)/Count(Fields!DeliveryNumber.Value,IIF(Parameters!Scope1.Value,"matrix1_Year","matrix1_hub"))
when i preview the report it gives me the following error
The Value expression for the textbox â'textbox5â' has a scope parameter that
is not valid for an aggregate function. The scope parameter must be set to a
string constant that is equal to either the name of a containing group, the
name of a containing data region, or the name of a data set.
If i take the IIF statement out and put in either of the group names, the
report works and i get the values i would expect, but i need to be able to do
this by passing in a parameter, i don't want to create two report just for
this one problem.Have you tried adding the IIF to the outside... ie
=IIf (Parameters@.Scope1.Value,Big Expression1, Big Expression2)
I do not know if this will help, but it is possible that the problem is
related to WHEN the expression is evaluated..
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"gacar101" wrote:
> I have a matrix with two column groups. I need to be able have the user
> select from my boolean parameter and change the scope on my aggregate
> function. I already have my boolean parameter setup. the two groups are
> called matrix1_Year and matrix1_Hub. i used the following expression in my
> textbox
> =Count(Fields!DeliveryNumber.Value)/Count(Fields!DeliveryNumber.Value,IIF(Parameters!Scope1.Value,"matrix1_Year","matrix1_hub"))
> when i preview the report it gives me the following error
> The Value expression for the textbox â'textbox5â' has a scope parameter that
> is not valid for an aggregate function. The scope parameter must be set to a
> string constant that is equal to either the name of a containing group, the
> name of a containing data region, or the name of a data set.
> If i take the IIF statement out and put in either of the group names, the
> report works and i get the values i would expect, but i need to be able to do
> this by passing in a parameter, i don't want to create two report just for
> this one problem.