SSIS LINKS

Question

Post reply

SSIS LINKS

AIRWALKER-375999

Hall of Fame

Points: 3040
More actions
July 22, 2008 at 3:08 am

#130169

Hi, I want to insert data from a .csv file to a sql 2005 database using SSIS, before an insetion takes place I would like a check to be done to see if a update or insert is required. Can anybody give me any tips or point me to a good website that explains how this can be done?
Many thanks

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

steveb. SSC-Forever Points: 46733 More actions · Answer 1

http://www.sqlis.com/default.aspx

is an excellent site, it should have what you are after..

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 2

Since what you are doing is pretty much a type-1 SCD, this site: http://www.sqlbi.com/[/url] will have some tips and some tools to help.

AIRWALKER-375999 Hall of Fame Points: 3040 More actions · Answer 3

Thanks for the replies guys, but I'm not seeing any clear easy to follow examples. I'm completly new to SSIS and I want to update our old dts packages to sql2005.

We have several .csv files generated by different departments during the night. In the early morningn our dts package reads the data from these csv's and then decides either inserting or updating the relevant data into a sql database. it uses a vb script and execte sql tasks.

In the vb script i mapped the csv to the destination table and then had some logic which decides if an update or insert is needed. Here is the vb script:

'**********************************************************************

' Visual Basic Transformation Script

'************************************************************************

' Copy each source column to the destination column

Function Main()

DTSDestination("ACC_ID") = DTSSource("Col001")

DTSDestination("ACC_NAME") = DTSSource("Col002")

Dim isRecExist

isRecExist = ExecQuery("Select count(*) as cnt from ACCOUNTS WHERE ACC_ID = '" & DTSDestination("ACC_ID") & "'")

If isRecExist = true Then

Main = DTSTransformstat_UpdateQuery

Else

Main = DTSTransformstat_InsertQuery

End If

End Function

Function ExecQuery(Query)

Dim myConn

Dim myRecordset

Dim mySQLCmdText

Dim thisConnStr

set myConn = CreateObject("ADODB.Connection")

set myRecordset = CreateObject("ADODB.Recordset")

thisConnStr = "Provider=SQLOLEDB.1;Data Source=" & DTSGlobalVariables("gServerName").Value & "; Initial Catalog=" & DTSGlobalVariables("gDBName").Value & ";user id = '" & DTSGlobalVariables("gUserName").Value & "';password='" & DTSGlobalVariables("gPassword").Value & "'"

myConn.Open = thisConnStr

mySQLCmdText = Query

myRecordset.Open mySQLCmdText, myConn

If myRecordset("cnt").value <> "0" AND myRecordset("cnt").value <> "" Then

ExecQuery = true

Else

ExecQuery = false

End If

MyConn.Close

Set myConn = Nothing

Set myRecordset = Nothing

End Function

I'm looking for a step by step example that shows how to do the same thing in SSIS. I can set up all the neccessary componants and tasks and connect them all, but I'm not sure how convert the script ie, refer the correct syntax, how to refer to then source rows and destination rows and weather at all it is really neccssary to do so.

Thanks