April 7, 2010 at 5:31 am
Using prepopulated SSIS pkg variables, I need to perform an UPDATE of a single row (the only row) on a SQL table w/in my SSIS PKG.
The column name to update is TxtFileSource VARCHAR(200)
In my pkg, I have 4 pre-populated variables available:
@[User::BaseFolder] value = \\MyRootDIR
@[User::BaseTypeFolder] value = \MySubDir
@[User::BaseFile] value = \MyFilename
@[User::pActiveDate] value = 20100407
I think I should be using an EXEC SQL Task in the Pkg to perform the update w/ SQL as:
UPDATE MyTable SET TxtFileSource = ?
-- (there's only ever going to be 1 row on table so no predicate necessary)
In this example, the column value should look like: \\MyRootDIR\MySubDir\MyFilename20100407.txt
How can I accomplish this?
April 7, 2010 at 6:18 am
Use an Expression within an Execute SQL task to combine static text and variables for your UPDATE statement.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
April 7, 2010 at 6:31 am
I use an expression for my CONNECTION STRING in previous tasks that look like:
@[User::BaseFolder] + @[User::BaseTypeFolder] + @[User::BaseFile] + @[User::pActiveDate] + ".TXT"
Question is, how do I relay this expression to the EXEC SQL TASK?
Do I specify UPDATE MyTable SET my Column = ?
What PROPERTY should I select when defining the EXPRESSION on the EXEC SQL Task?
sorry, not familiar w/ this type of variable reference in the EXEC SQL TASK...
April 7, 2010 at 6:35 am
SQLStatementSource, I think.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
April 7, 2010 at 6:53 am
Construct your SQL Statement in a variable of type string.
Something in the like of:
REPLACE("
select * from <DB_Source_Name,,>.myTable where colA = 'Test'
",
"<DB_Source_Name,,>", (DT_WSTR,50) @DB_Source_Name)
In this code snippet, @DB_Source_Name is a string variable that contains the name of the database. I use <DB_Source_Name,,> in my code, because this is the template format of Management Studio (easy for debugging).
Thus, install BIDS-helper, create a string variable and open the Variable Expression window. Copy paste your code (like above). Evaluate it to see if the final SQL code is OK.
Then go to your Execute SQL Statement and select variable as your SQLSourceType. Choose the variable you just created.
There is only one caveat: variable expressions can only hold 4000 characters, so don't make your update statement too long 🙂
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 7, 2010 at 6:53 am
worked great. Thank you Phil.
April 7, 2010 at 7:20 am
Using Phil's suggestion to utilize an Expression, my solution was:
- created new variable at Pkg level; I named it: SQLStatement
- Highlighted the variable 'SQLStatement' and viewed it's Properties:
- Clicked 'Expression' and entered the following syntax:
"UPDATE MyAuditTable SET MyFileName = " + "'" + @[User::BaseFolder]
+ @[User::BaseTypeFolder] + @[User::BaseFile] + @[User::pActiveDate] + ".TXT"
+ "'" + " WHERE MyFileName IS NULL"
- then, in Pkg, added a new EXEC SQL Task
- On Exec SQL Task editor:
- selected General
- Connection Type: OLEDB
- Connection EIADB (a pre-existing connection in Conn Mgr)
- SQL Source Type: VARIABLE
- Source Variable: User::SQLStatement
- NOTE: on the SQL Task editor, left these blank:
- Parameter Mapping, Result Set, Expressions
April 7, 2010 at 9:18 am
I'm a little fuzzy why we are making this so tricky..
Why don't you just use parameter mapping in the Exec SQL Task
UPDATE dbo.TableName
SET Field1 = ?, Field2 = ?, Field3 = ?, Field4 = ?
And then map those variable in directly using the correct tab.. Nothing tricky..
CEWII
April 7, 2010 at 10:31 am
Elliott W (4/7/2010)
I'm a little fuzzy why we are making this so tricky..Why don't you just use parameter mapping in the Exec SQL Task
UPDATE dbo.TableName
SET Field1 = ?, Field2 = ?, Field3 = ?, Field4 = ?
And then map those variable in directly using the correct tab.. Nothing tricky..
CEWII
Well, it is kind of tricky in maintenance 🙂
I don't like the idea of mapping variables to ordinal positions, and perhaps mapping the same variable to multiple positions.
It's just not elegant in my opinion.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 7, 2010 at 10:42 am
da-zero (4/7/2010)
Elliott W (4/7/2010)
I'm a little fuzzy why we are making this so tricky..Why don't you just use parameter mapping in the Exec SQL Task
UPDATE dbo.TableName
SET Field1 = ?, Field2 = ?, Field3 = ?, Field4 = ?
And then map those variable in directly using the correct tab.. Nothing tricky..
CEWII
Well, it is kind of tricky in maintenance 🙂
I don't like the idea of mapping variables to ordinal positions, and perhaps mapping the same variable to multiple positions.
It's just not elegant in my opinion.
I don't like the REPLACE or expressions methods I saw, I don't see those as very elegent either. Readers may choose what they feel is most maintainable and elegent. As you can see opinions vary.
CEWII
April 7, 2010 at 1:03 pm
Elliott W (4/7/2010)
da-zero (4/7/2010)
Well, it is kind of tricky in maintenance 🙂
I don't like the idea of mapping variables to ordinal positions, and perhaps mapping the same variable to multiple positions.
It's just not elegant in my opinion.
I don't like the REPLACE or expressions methods I saw, I don't see those as very elegent either. Readers may choose what they feel is most maintainable and elegent. As you can see opinions vary.
CEWII
That's why I said: ...in my opinion 😉
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply