variable in SQL output of SSIS

Question

variable in SQL output of SSIS

cloprinzo

SSC Journeyman

Points: 96
More actions
September 12, 2011 at 12:34 pm

#257157

Basically I have an SSIS package that creates an FTP file based upon what is in a table. I can pass a variable to the SSIS to create the filename using the AutoID variable I am sending it.
I would like to pass it a second variable to use in the select statement two ways: one, to tell it which type to pull (2, 4,or 6) and two, to use that same variable in the select list as the output for the file.
example:
variable 1: autoid 416586
variable 2: type 2
SQL output would be SELECT TransferNum, {variable 2}, FromSt, ToSt, Qty ...
I do not know how to have the second variable display in the select output. :w00t:
Thank you for any help you can throw my way!
Charlie

Viewing 9 posts - 1 through 9 (of 9 total)

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

Jared SSC Guru Points: 61793 More actions · Answer 1

You can declare a variable in SQL by using DECLARE @variableName INT and then SET @variableName = SELECT 2 (or pass it in in a stored procedure. Then you can just SELECT columnA, columnB, @variableName, columnC FROM ...

Thanks,

Jared

Jared
CE - Microsoft

cloprinzo SSC Journeyman Points: 96 More actions · Answer 2

The problem is that it may be a 2, 4 or 6. I currently have 3 separate SSIS packages to handle each of the different types and I am trying to consolidate it into one but I need to pass two variables each time. One for the record ID and one for the type.

The type variable is giving me problems because it is used in the SELECT output and in the WHERE clause. I am not sure how to put into in the SELECT section.

Jared SSC Guru Points: 61793 More actions · Answer 3

To put it into the select, you just have to qualify it like SELECT 2, SELECT 'What I want to show up', SELECT @variableName

Maybe I am missing something?

Thanks,

Jared

Jared
CE - Microsoft

cloprinzo SSC Journeyman Points: 96 More actions · Answer 4

In the OLD DB Source Editor in SSIS the SQL command text looks like this:

SELECT

' ' AS WTIPOST

,Transfer_Num

,? AS WTTYP

,FrStore

,ToStore

,ItemNum

,Qty

,' ' AS WTINEG

,'00000000' AS WTIDATE

FROM Shuttle.dbo.tbl_TMax_Transfers

WHERE (AutoID = ?)

The question marks are indicators of variables. It works with one (the WHERE clause) without a problem, but when I try to add it to the SELECT it will not let me pass the variable as data.

I was trying to avoid having to create a stored procedure which I would call from the SSIS but I guess that would be the easiest way around this.

Thanks!

Jared SSC Guru Points: 61793 More actions · Answer 5

If you say WHERE AutoId = ?, then SELECT AutoID AS WTTYP should yield the same results, yes?

Thanks,

Jared

Jared
CE - Microsoft

cloprinzo SSC Journeyman Points: 96 More actions · Answer 6

yes, it would but I don't need the AutoID in the results just the type. There are times when I need to send AutoID 55 in as a type 2 and then depending upon certain events I may later send AutoID in as a type 4.

Jared SSC Guru Points: 61793 More actions · Answer 7

Ok, I see what you are saying now. So, what if you pass the parameter as a comma delimited string or something similar, and then split it in your SQL and store it as 2 variables?

DECLARE @string varchar(10)

DECLARE @TypeID int

DECLARE @AutoID int

SET @string = ?

--some function or code to split depending on knowns and unknowns that sets @TypeID and @AutoID

SELECT

' ' AS WTIPOST

, Transfer_Num

, @TypeID AS WTTYP

, FrStore

, ToStore

, ItemNum

, Qty

, ' ' AS WTINEG

, '00000000' AS WTIDATE

FROM Shuttle.dbo.tbl_TMax_Transfers

WHERE (AutoID = @AutoID)

Thanks,

Jared

Jared
CE - Microsoft

Jared SSC Guru Points: 61793 More actions · Answer 8

Also, I just found this link: http://weblogs.asp.net/rrobbins/archive/2009/01/15/ssis-package-user-variables.aspx It seems as though you CAN have more than 1 parameter, and that they will both be represented as ? until the proper parameter is selected from the drop-doen.

Thanks,

Jared

Jared
CE - Microsoft