SSIS Dynamic Columns
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty height:90px;width:728px;box-sizing:border-box;
I created variables that are populated from rows from another table. It should loop through each row to generate my next select statement. However, SSIS isn't accepting '+ @[User::ThisField]+'
as a variable it expects AS MyColName
after. I don't want this because it will return the column name and not the actual value it contains.
This is my code.
SELECT
ISNULL('+ (DT_I4)@[User::ThisID]+', 0) AS EEDFID,
'+ @[User::ThisField]+',
EeID
FROM
EeExtraDetails
Has anyone had luck with this?
sql-server ssis dynamic-sql
add a comment |
I created variables that are populated from rows from another table. It should loop through each row to generate my next select statement. However, SSIS isn't accepting '+ @[User::ThisField]+'
as a variable it expects AS MyColName
after. I don't want this because it will return the column name and not the actual value it contains.
This is my code.
SELECT
ISNULL('+ (DT_I4)@[User::ThisID]+', 0) AS EEDFID,
'+ @[User::ThisField]+',
EeID
FROM
EeExtraDetails
Has anyone had luck with this?
sql-server ssis dynamic-sql
add a comment |
I created variables that are populated from rows from another table. It should loop through each row to generate my next select statement. However, SSIS isn't accepting '+ @[User::ThisField]+'
as a variable it expects AS MyColName
after. I don't want this because it will return the column name and not the actual value it contains.
This is my code.
SELECT
ISNULL('+ (DT_I4)@[User::ThisID]+', 0) AS EEDFID,
'+ @[User::ThisField]+',
EeID
FROM
EeExtraDetails
Has anyone had luck with this?
sql-server ssis dynamic-sql
I created variables that are populated from rows from another table. It should loop through each row to generate my next select statement. However, SSIS isn't accepting '+ @[User::ThisField]+'
as a variable it expects AS MyColName
after. I don't want this because it will return the column name and not the actual value it contains.
This is my code.
SELECT
ISNULL('+ (DT_I4)@[User::ThisID]+', 0) AS EEDFID,
'+ @[User::ThisField]+',
EeID
FROM
EeExtraDetails
Has anyone had luck with this?
sql-server ssis dynamic-sql
sql-server ssis dynamic-sql
edited Nov 15 '18 at 16:41
marc_s
586k13011281272
586k13011281272
asked Nov 15 '18 at 16:16
theJtheJ
14116
14116
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
That's not going to work that way. Assuming all the @[User::ThisField] data types are the same, then your problem is just how to use expressions. If the value of @ThisField is Col1 and it's numeric and then @ThisField becomes Col2 which is text, the data flow will fails as the metadata cannot change at runtime.
My metadata doesn't change, explain the expressions part
Variables don't work into queries the way you have it now, but you're on the correct path. Instead, define a new variable @[User::QuerySource]
, data type of String and specify that the value is an Expression. Your expression then becomes much as you have it now.
"SELECT ISNULL("
+ (DT_WSTR, 20)@[User::ThisID]
+ ", 0) AS EEDFID, "
+ @[User::ThisField]
+ " AS MyFieldAlias "
+ ", EeID FROM EeExtraDetails "
The string concatenation within the SSIS expression language only works with strings so I have explicitly cast your @User::ThisID as string. If I misinterpreted that and you were intending to use the SSIS Expression language's ISNULL function, then that's a different syntax. At any rate, click evaluate and you should see whether your query looks approximately correct for the design-time values.
My metadata does change
The best you can do in this situation is cast the results of @[User::ThisField] to string within the SQL Statement like
+ "CAST(", @[User::ThisField, " AS nvarchar(4000))"
add a comment |
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53323644%2fssis-dynamic-columns%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
That's not going to work that way. Assuming all the @[User::ThisField] data types are the same, then your problem is just how to use expressions. If the value of @ThisField is Col1 and it's numeric and then @ThisField becomes Col2 which is text, the data flow will fails as the metadata cannot change at runtime.
My metadata doesn't change, explain the expressions part
Variables don't work into queries the way you have it now, but you're on the correct path. Instead, define a new variable @[User::QuerySource]
, data type of String and specify that the value is an Expression. Your expression then becomes much as you have it now.
"SELECT ISNULL("
+ (DT_WSTR, 20)@[User::ThisID]
+ ", 0) AS EEDFID, "
+ @[User::ThisField]
+ " AS MyFieldAlias "
+ ", EeID FROM EeExtraDetails "
The string concatenation within the SSIS expression language only works with strings so I have explicitly cast your @User::ThisID as string. If I misinterpreted that and you were intending to use the SSIS Expression language's ISNULL function, then that's a different syntax. At any rate, click evaluate and you should see whether your query looks approximately correct for the design-time values.
My metadata does change
The best you can do in this situation is cast the results of @[User::ThisField] to string within the SQL Statement like
+ "CAST(", @[User::ThisField, " AS nvarchar(4000))"
add a comment |
That's not going to work that way. Assuming all the @[User::ThisField] data types are the same, then your problem is just how to use expressions. If the value of @ThisField is Col1 and it's numeric and then @ThisField becomes Col2 which is text, the data flow will fails as the metadata cannot change at runtime.
My metadata doesn't change, explain the expressions part
Variables don't work into queries the way you have it now, but you're on the correct path. Instead, define a new variable @[User::QuerySource]
, data type of String and specify that the value is an Expression. Your expression then becomes much as you have it now.
"SELECT ISNULL("
+ (DT_WSTR, 20)@[User::ThisID]
+ ", 0) AS EEDFID, "
+ @[User::ThisField]
+ " AS MyFieldAlias "
+ ", EeID FROM EeExtraDetails "
The string concatenation within the SSIS expression language only works with strings so I have explicitly cast your @User::ThisID as string. If I misinterpreted that and you were intending to use the SSIS Expression language's ISNULL function, then that's a different syntax. At any rate, click evaluate and you should see whether your query looks approximately correct for the design-time values.
My metadata does change
The best you can do in this situation is cast the results of @[User::ThisField] to string within the SQL Statement like
+ "CAST(", @[User::ThisField, " AS nvarchar(4000))"
add a comment |
That's not going to work that way. Assuming all the @[User::ThisField] data types are the same, then your problem is just how to use expressions. If the value of @ThisField is Col1 and it's numeric and then @ThisField becomes Col2 which is text, the data flow will fails as the metadata cannot change at runtime.
My metadata doesn't change, explain the expressions part
Variables don't work into queries the way you have it now, but you're on the correct path. Instead, define a new variable @[User::QuerySource]
, data type of String and specify that the value is an Expression. Your expression then becomes much as you have it now.
"SELECT ISNULL("
+ (DT_WSTR, 20)@[User::ThisID]
+ ", 0) AS EEDFID, "
+ @[User::ThisField]
+ " AS MyFieldAlias "
+ ", EeID FROM EeExtraDetails "
The string concatenation within the SSIS expression language only works with strings so I have explicitly cast your @User::ThisID as string. If I misinterpreted that and you were intending to use the SSIS Expression language's ISNULL function, then that's a different syntax. At any rate, click evaluate and you should see whether your query looks approximately correct for the design-time values.
My metadata does change
The best you can do in this situation is cast the results of @[User::ThisField] to string within the SQL Statement like
+ "CAST(", @[User::ThisField, " AS nvarchar(4000))"
That's not going to work that way. Assuming all the @[User::ThisField] data types are the same, then your problem is just how to use expressions. If the value of @ThisField is Col1 and it's numeric and then @ThisField becomes Col2 which is text, the data flow will fails as the metadata cannot change at runtime.
My metadata doesn't change, explain the expressions part
Variables don't work into queries the way you have it now, but you're on the correct path. Instead, define a new variable @[User::QuerySource]
, data type of String and specify that the value is an Expression. Your expression then becomes much as you have it now.
"SELECT ISNULL("
+ (DT_WSTR, 20)@[User::ThisID]
+ ", 0) AS EEDFID, "
+ @[User::ThisField]
+ " AS MyFieldAlias "
+ ", EeID FROM EeExtraDetails "
The string concatenation within the SSIS expression language only works with strings so I have explicitly cast your @User::ThisID as string. If I misinterpreted that and you were intending to use the SSIS Expression language's ISNULL function, then that's a different syntax. At any rate, click evaluate and you should see whether your query looks approximately correct for the design-time values.
My metadata does change
The best you can do in this situation is cast the results of @[User::ThisField] to string within the SQL Statement like
+ "CAST(", @[User::ThisField, " AS nvarchar(4000))"
answered Nov 15 '18 at 16:30
billinkcbillinkc
46.7k982124
46.7k982124
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53323644%2fssis-dynamic-columns%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown