November 4, 2014 at 4:45 pm
I am trying to do a loop insert to a flat file destination that I have done with raw SQL before, (Old School) but not sure how to set up SSIS to do the same thing. In raw SQL I would first create a cursor to store my keys that would be used later to insert a record. The compressed code would be something like:
DECLARE @CompanyRecordAS VARCHAR(MAX)
, @CompanyCode AS VARCHAR(9)
, @CustomerID AS VARCHAR(9)
DECLARE curGetAKEYS CURSOR
READ_ONLY
FOR
SELECT DISTINCT CompanyCode
FROM dbo.Customer
ORDER BY CompanyCode
OPEN curGetAKEYS
FETCH NEXT FROM curGetAKEYS INTO @CompanyCode
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
DECLARE curGet130 CURSOR
READ_ONLY
FOR
SELECT
'Company Record'
FROM dbo.Customer
WHERE CompanyCode = @CompanyCode
OPEN curGet130
FETCH NEXT FROM curGet130 IN @CompnayRecord
Blah, blah
Then @CompanyRecord is inserted as the new record and loops back around to get the next record.
Now, to add to this complexity (haha) my finial file is a positional file that looks something like:
HEADERRECORD
CompanyRecord#1
CustomerRecord#1 of first customer in Company#1
CustomerRecord#2 of first customer in Company#1
.Blah
.Blah
CompanyRecord#2
CustomerRecord#1 of first customer in Company#2
CustomerRecord#2 of first customer in Company#2
.Blah
.Blah
FOOTERRECORD
How do I set something like this up in SSIS?
November 5, 2014 at 4:59 pm
OK, I have a start going in this and I can see an answer to this may very well be evolved. I got the header record to insert very well. I have a Execute SQL Task with my full result set to pull The Company Codes. I am just taking a small sampling.
SELECT DISTINCT TOP 3 CompanyCode
FROM dbo.Customer
WHERE CompanyCode NOT IN('99999')
ORDER BY CompanyCode
I am using a ForEach Loop. That works good. I tested the results by using a Script Task to pop a message with the value or the CompanyCode Variable. Then When I try to use that variable to pull the record for the Company it will not write to the flat file. I have an OLE DB Source that queries the record.
SELECT DISTINCT '130ERNOTETEMPLT' + LEFT(CompanyCode + SPACE(16), 16) + SPACE(75) + SPACE(40) + SPACE(2000) AS EXPORTROW
FROM dbo.Customer
WHERE (CompanyCode = ?)
and setup a parameter 0 that uses the CompanyCode Variable. I added a Data Viewer between the OLE DB Source and the flat file destination to see what I get there. When I run the package, I will get the Data Viewer to come up with everything needed, but once I close it, it doesn't go to the text file. The flat file destination fails even when string [DT_STR] is for both the input and the output. I think if I can get that, the rest is repeatable to some degree.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply