import ascii file with ssis and script

Hi, i've question about how to import an ascii-file in a sql 2005 table.
I want to import this file also with an unique key. There i first have to get the last key form the table and then raise this key. Next step is to use this key during the import.

How do i have to do this in ssis?
Thanks in advance

OlafNo problem.

While in your control flow, add a variable named MaxKey of integer type. Then in your control flow, right before the data flow task, add an Execute SQL task. Set its ResultSet to Single row. Setup the connection in it to point to your database and the for the SQLStatement, use: "select max(keyfield) from your_table". Click on the Result Set option on the left-hand side of the editor. Set the result name to 0 and chose User::MaxKey as the Variable Name. Click OK.

In your data flow right before the OLE destination, add a Script Component. Chose to use it as a transformation. Edit it. On the left, select "Inputs and Outputs". Expand Output 0 and then "Add Column" to the Output Columns folder and call it "NewKey. Make sure it is an integer data type big enough to hold a key big enough for your table. Then click on "Script" on the left to bring up the script parameters. Add "MaxKey" to the ReadOnlyVariables box. Then, click on the Design Script... button. Here's your script:

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain
Inherits UserComponent
Private NextKey As Int32 = 0

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

Dim MaximumKey As Int32 = Me.Variables.MaxKey ' Grab value of MaxKey which was passed in

' NextKey will always be zero when we start the package.
' This will set up the counter accordingly
If (NextKey = 0) Then
' Use MaximumKey +1 here because we already have data in the table, or we'll start with 0+1=1 if we don't
' and we need to start with the next available key
NextKey = MaximumKey + 1
Else
' Use NextKey +1 here because we are now relying on
' our counter within this script task.
NextKey = NextKey + 1
End If

Row.NewKey = NextKey ' Assign NextKey to our AdFormKey field on our data row
'
End Sub

End Class|||Phil,
Thansks for your answer, and i'm doing wel till step 'Script Component'. After i've clicked on 'script' and add 'MaxKey', i get an error message after i've clicked on button 'Design Script'. The message is: 'The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container...' What do i wrong?
Thanks.

Olaf|||See the following post which explains the solution: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=956181&SiteID=1

In short, save a copy of the SSIS variable (e.g. Me.Variables.Whatever ) in the OnPreExecute subroutine. Use the copy in lieu of Me.Variables.Whatever in you ProcessInputRow() subroutine.|||thanks jaegd, problem is solved. My i ask you an other question?
I get an error message 'Cannot create connector. the destination component does not hav any available inputs for use in creating a path' after i've connect the script component at the destination ole. What goes wrong? When i look to the properties of the OLE, i see some unuased input colummns.
Thanks in advance|||

jaegd wrote:

See the following post which explains the solution: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=956181&SiteID=1

In short, save a copy of the SSIS variable (e.g. Me.Variables.Whatever ) in the OnPreExecute subroutine. Use the copy in lieu of Me.Variables.Whatever in you ProcessInputRow() subroutine.

I don't think this is the problem. I detailed the steps exactly as I use it. Either the OP has a typo, or he didn't define the variable in the right scope. The variable must be scoped to the package. The solution above is too much work.|||

Olaf vd Sanden wrote:

Phil,
Thansks for your answer, and i'm doing wel till step 'Script Component'. After i've clicked on 'script' and add 'MaxKey', i get an error message after i've clicked on button 'Design Script'. The message is: 'The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container...' What do i wrong?
Thanks.

Olaf

You must define the variable when in the control flow. Make sure that the variable's scope is set to the package.

Then make sure you type the variable name in the ReadOnlyVariables section on the script parameters page, as I have indicated in my instructions. Do not include any extra spaces when typing this variable name in the ReadOnlyVariables box.|||

Olaf vd Sanden wrote:

thanks jaegd, problem is solved. My i ask you an other question?
I get an error message 'Cannot create connector. the destination component does not hav any available inputs for use in creating a path' after i've connect the script component at the destination ole. What goes wrong? When i look to the properties of the OLE, i see some unuased input colummns.
Thanks in advance

Please make sure you are using a destination OLE connector and not a source connector.|||Phil, First i had a data conversion before my destionation ole. After i've removed this is was possible to connect the script component. Sorry for my questions, it's new for me. But, isn't possible to use a data conversion and a script component?
Thanks, Olaf|||

Olaf vd Sanden wrote:

Phil, First i had a data conversion before my destionation ole. After i've removed this is was possible to connect the script component. Sorry for my questions, it's new for me. But, isn't possible to use a data conversion and a script component?
Thanks, Olaf

Sure it's possible. You must be sure that when you dropped in the script component that you selected "Transformation," not "Source" or "Destination."|||Phil, the script component propertie is 'Transformation', but still i can't connect a data covnversion and a script component add an Ole DB Destination
The situation that i want in the data flow is:
a 'Flate File source editor' > 'Data Conversion Tranformation editor' > 'OLE DB destination Editor' and also the 'Script Component' > 'OLE DB destination Editor'

Sorry again, but i'm new with this.
Thanks in advance.

Olaf|||

Olaf vd Sanden wrote:

Phil, the script component propertie is 'Transformation', but still i can't connect a data covnversion and a script component add an Ole DB Destination
The situation that i want in the data flow is:
a 'Flate File source editor' > 'Data Conversion Tranformation editor' > 'OLE DB destination Editor' and also the 'Script Component' > 'OLE DB destination Editor'

Sorry again, but i'm new with this.
Thanks in advance.

Olaf

Your OLE DB destination has to be at the END of the dataflow. You cannot have two in the same dataflow, unless you've split the dataflow with a multicast, conditional statement, etc...

Why do you have the OLE DB destination between the Data Conversion transformation and the Script component?|||Phil, Thanks for your reply. I think i've solved the problem. I've used an 'Union All' where i connected the 'Flate File source editor' and 'Script Component'. After the 'Union' comes a 'Data conversion' and then the 'OLE DB Destination'. I like to hear from you if this the right way.

Now i get an error message in the 'Execution Results': [DTS.Pipeline] Error: component "Union All" (14793) failed the pre-execute phase and returned error code 0x80070057.

What do I do wrong now?
Thanks.

Olaf|||Your flat file should hook into the data conversion transformation and then to the script component and then to the OLE DB destination.

FF -> Data Conversion -> Script Component -> OLE DB Destination.

No union is necessary.|||Phil, Thanks again for your fast reply. And it works.
Again thanks.
Olaf