IF EXISTS in SSIS Data Flow Destination Task? -- sql server dts

Allan Mitchell
11/29/2005 12:00:00 AM

I will presume @Username, @Firstname, @Lastname are actually in the
package right?

You can use an ExecuteSQL task. You can build the SQL Statement using
an Expression i.e. you can build up this statement at runtime

We do something very similar here.

Shredding a Recordset
(http://www.sqlis.com/default.aspx?59)

Also look here

The ExecuteSQL Task
(http://www.sqlis.com/default.aspx?58)

You need to find the right property on the ExecuteSQL task on which to
create this expression (SQLStatementSource)

Allan

[quoted text, click to view]

Nightcrawler
11/29/2005 7:45:57 AM

Allan,

Thanks for you answer. I looked over the articles. Since my source is a
flat file I assume I would have to read that into a recordset then do a
ForEach loop through the rows in that recordset and run a OLE DB
Command with my sql statement?

Nightcrawler
11/29/2005 9:12:14 AM

Allan,

I am having a bit of an issue setting up the parameters for the
following query:

IF NOT EXISTS (SELECT * FROM Table WHERE Username = ?)
BEGIN
INSERT table(Username, Firstname, Lastname)
VALUES (?, ?, ?)
END

In the ForLoop the variable mapping are User::Username 0,
User::Firstname 1, User::LastName 2
In the Execute SQL Task the parameter mappings are:

User::Username, Input, VARCHAR, 0
User::Username, Input, VARCHAR, 1
User::Firstname, Input, VARCHAR, 2
User::LastName, Input, VARCHAR, 3

This does not work. I believe its because I use Username twice. If I
remove the outer IF NOT EXISTS and only do the INSERT statement and
modify the Execute SQL Task to the following:

User::Username, Input, VARCHAR, 0
User::Firstname, Input, VARCHAR, 1
User::LastName, Input, VARCHAR, 2

It works fine. Not the fastest solution though, it took a few seconds
to do a thousand rows.

Please let me know what I am doing wrong.

Thanks

Allan Mitchell
11/29/2005 9:35:26 PM

Little lost

So your source is a flat file and you want to check to see if the
UserName from the file exists in the destintion before inserting?

You can use the OLE DB Command transform. You execute a proc and pass
in the values of each row (row*row) to the proc and decide what to do in
the proc.

You can also land the data and then use TSQL to do the inserts and
checking

Allan

[quoted text, click to view]

Allan Mitchell
11/29/2005 9:40:07 PM

OK so you are looping now through a recordset. A recordset will return
to you the columns which you assign to variables. Inside the loop you
have an executeSQL Task. You need in this task to build the SQL
Statement through an expression not do the assigning thing as you are
now.

Allan

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server dts

sql server dts : IF EXISTS in SSIS Data Flow Destination Task?