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
Olaf
import ascii file with ssis and script
Hot Topic
- SQL Server 2005 OLAP Cube on SQL Server 2000?
- Code error
- how to balance the database
- Failed to Open Rowset Error in Crystal Reports
- missing tables
- How do I report a bug in MDAC 2.8 to Microsoft ?
- Linked Server Access DB
- VARCHAR(MAX), NVARCHAR(MAX) and VARBINARY(MAX) support
- Can a non-admin user generate a create database script?
- Surface Area Configuration in a Clustered environment
SQL Server
- Newbie Basic Question - DISTINCT & PKs
- sql problem
- dynamic sql
- Cannot use host alias to access local Report Server
- SQlserver 2005 X64 EE extremely slow in a multiuser environment
- How to restrict user to tab (no mouse) selection
- Replacement of functions by procedures for SQL 7.0
- Verification origin
- Setup question: ASP.Net Version Registration Requirement warning message
- Sql Management Studio behaviour
import ascii file with ssis and script
musafir-a Voyager
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.
Hugor
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.
yxrkt
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
ehsan sadeghi
Again thanks.
Olaf
RajKumarD
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
Personal Information Withheld
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.
LAPM
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
EnterBS
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
InquiringMinds
Please make sure you are using a destination OLE connector and not a source connector.
enric vives
FF -> Data Conversion -> Script Component -> OLE DB Destination.
No union is necessary.
SainTLove
Thanks, Olaf
Hemant Hindlekar
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
Dietz
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
Dandy Weyn
Sure it's possible. You must be sure that when you dropped in the script component that you selected "Transformation," not "Source" or "Destination."