November 6, 2014 at 8:05 am
I posted my issue on a differant forum and didn't get any response. Where it is an SSIS issue, maybe this is a better forum to get an answer.
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 @CompanyRecord AS 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?
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.
November 6, 2014 at 8:38 am
Nasty.
I think I'd do it like this.
As your OLEDB source, have a query which gives you an ordered resultset
CompanyInfo1, CompanyInfo2, CustomerInfo1, CustomerInfo2 etc
order by Company, Customer
Of course, this is heavily denormalised, but it's worth it.
Feed this into an asynchronous script component with a single, one column (string) output to your flat file destination.
Your script component then handles everything ... the headers, different row types, footers. There are plenty of examples out there, but if you are stuck, post back.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
November 6, 2014 at 4:37 pm
I agree with Phil on this one, but I'd take it one step further. You're building what I usually nickname a "Mainframe File", where row ordering is important to the receiver because it's expecting row type switching.
The new version of this is XML, which is a bit more robust and less painful.
Anyways, as mentioned above, build out your queries and lines in SQL and dump them all into a single #table with whatever ordering keys you need (in your case, Client and Company). Make sure Client has a 0 for Company. The third column here is a necessary tragedy of VARCHAR(MAX), which is your converted concatonations into a single line for the output.
Once you've inserted everything into the organizer table, SELECT Client, Company, VCMaxColumn FROM #Organizer ORDER BY Client, Company.
Dump that to a direct to file output from SSIS and make sure you turn on the presorted settings in your OLEDB source (for both reference and sanity) for your key columns, and dump your VCMaxColumn direct to target.
Any other way hurts more. I've tried coding it a few different ways, this has always been the easiest, and most obvious, to maintain for me.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply