SSIS Concatination
Nov 30, 2007
I have a query in SSIS which works fine.
SELECT SUBSTR(DOB_CHAR, 7, 2) AS EXP3, SUBSTR(DOB_CHAR, 5, 2) AS EXPR1, SUBSTR(DOB_CHAR, 1, 4) AS EXPR2
FROM WAITLIST
results
20
12
1948
07
09
1982
29
07
1960
However, when I try to concatinate, it falls over
SELECT SUBSTR(DOB_CHAR, 7, 2) + '/' + SUBSTR(DOB_CHAR, 5, 2) + '/' + SUBSTR(DOB_CHAR, 1, 4) AS EXPR2
FROM WAITLIST
The syntax works ok on SQL Server query, but not in SSIS.
(Reading from Oracle, number 19481220, an is varchar2)
Select will work with pipes (as in oracle) but it must be of the form | | '/' | |
that is
SUBSTR(DOB_CHAR, 7, 2) | | '/' | | SUBSTR(DOB_CHAR, 5, 2) | | '/' | | SUBSTR(DOB_CHAR, 1, 4) AS EXP2
I would like to know if this is correct, or is it because I am reading from Oracle?
I do get an error message in query builder, but the query then runs as required.
Error in list of function arguments: '|' not recognized.
Error in list of function arguments: ',' not recognized.
Error in list of function arguments: ')' not recognized.
Unable to parse query text.
Any views?
View 4 Replies
Mar 22, 2007
I have a need to query some data and string all my results by id. I am fairly close to the results but stuck on the final piece. Any help would be greatly appreciated.
Here's the scenario: My data looks as follows:
UserID
Results
1095
,,,,,,,
1095
,,,,,8,,
1095
,,,,,,,
1095
,,,,,8,,
1247
,2,3,,,6,,,
1247
,2,3,,,6,,,
1247
,2,3,,,6,,,
1247
,2,3,,,6,,,
4069
,,,,,,,
4069
,,,,,,,
4069
,,,,,,,
4069
,,,,,,,
4070
,,6,,,,,
4070
,,6,,,,,
4070
,,6,,,,,
4070
,,6,,,,,
I want to query it and end up with the results all strung together under each UserID as follows:
1095
,,,,,,,,,,,,8,,,,,,,,,,,,,,8,,
1247
,2,3,,,6,,,,2,3,,,6,,,,2,3,,,6,,,,2,3,,,6,,,
4069
,,,,,,,,,,,,,,,,,,,,,,,,,,,,
4070
,,6,,,,,,,6,,,,,,,6,,,,,,,6,,,,,
I know if I use the following code I can string all the results together
select *, Cast((SELECT Results + ',' FROM #temp1 FOR XML PATH('')) as varchar(max) ) as Results from #temp1
But I can't figure out how to break it down by individual UserID. Any help is greatly appreciated. Thanks in advance
View 11 Replies
View Related
Feb 20, 2008
Hello everyone!!
I am completely new to SSIS and have been given a large project (of course with a tight deadline) that has the absolute requirement of using SSIS. I am/was very, very good with DTS and could easily accomplish what I need to do with an ActiveX script task in DTS in no time, but as this is new development, we are not to use ActiveX script tasks within SSIS since it will not be supported in the next SQL Server release. I'm thinking script task, but please give some comments on how you would accomplish the following in SSIS (please remember I'm new to SSIS, so don't assume I know anything. )
I must accomplish this: in a nutshell, I need to create separate tab delimited text files of customer informaion. One for each region. Each region consists of X amount of states and we have X amount of regions. (Pseudo code followed by standard explanation)
Select a max value from region lookup table in SQL (this is the # of regions)
for N=1 to MyMaxValue
select states from region lookup table where region code = N (the current region we are on)
'this returns a list of states in a region, need these in array or recordset object or something
Open an output file which will be a tab delimited text file we will write results below in loop to (in DTS I would programatically kick off a transformation task in the package)
'loop thru states returned, so if in a rs object...
do while not rs.eof
execute customer stored procedure, passing as a variable the current state we are on
'this will return all customers within a state, this whole result set (approx 1 million) needs to go to the tab delimited file
'I have to execute this stored procedure for each state & then write results to the SAME file, until we are onto a different region
rs.movenext
close file
loop
next
OK, so basically, as you can see, Its sort of simple in a way what I need to do, i just have no idea how to go about doing this in SSIS. I can not hard code any state or region values. I MUST read them in from the lookup tables as region codes are constanatly changing and we are constantly adding in new states and new regions, so with above coding idea, it would always dynamically pick up any new states, new regions or changes.
So in a nutshell, I need to create separate tab delimited text files of customer informaion. One for each region. Each region consists of X amount of states and there are X amount of regions. Pretty strait forward, huh? The requirements are strait forward, but SSIS is throwing me for a loop... it does not seem flexible enough to be as dynamic as I need it to be to do this. I'm sure it is, just my understanding of it is very basic so far.
Please provide your suggestions! I think a lot of newbies would benefit from some SSIS design info... how to do common things in SSIS, but beyond just retrieving a recordset and writing it to a file... what do you do when you need to add just a few layers of decision processing, and retriving recordsets and writing files based on that decision processing?????
THANK YOU!!!!!!
Kathy
View 7 Replies
View Related
