Hi,

Does anyone know how to make this simple SQL query using a SSIS Data
Flow Destination Task?

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

I have tried both the OLE DB Destination and SQL Server destination.
The latter does not even let you specify a SQL query and the OLE DB
task does not allow me to specify a SQL statement with parameters.

Please help. I am stuck in quicksand and there are very limited
resources available out there.

Thanks

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


"Nightcrawler" <thomas.zaleski (AT) gmail (DOT) com> wrote

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?

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

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



"Nightcrawler" <thomas.zaleski (AT) gmail (DOT) com> wrote

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

"Nightcrawler" <thomas.zaleski (AT) gmail (DOT) com> wrote