Solved

SQL Source for SSIS

Posted on 2014-04-14
4
284 Views
Last Modified: 2016-02-10
Hello:

I'm planning on creating a TSQL view containing data that will be extracted by SSIS.  The first section of the view is to be a header section of about five fields.  The next four sections will be the body of the data containing anywhere from eight to thirty fields.  The final section will be a footer of about three fields.

First, is it possible to create a TSQL view containing sections with different numbers of fields like this?  If not, what other solutions are there?  I have created views many times in the past, with UNION ALL statements, but each statement separated sections that contained an equal number of fields.

Secondly, is it possible to tell SSIS to not pull column headings when extracting data from SQL?

Ultimately, I want to create a text file from SSIS that will be uploaded by a bank.  Banks have very strict requirements as to the format of the data in such files.

Thanks!

TBSupport
0
Comment
Question by:TBSupport
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
4 Comments
 
LVL 65

Accepted Solution

by:
Jim Horn earned 500 total points
ID: 39999071
>I'm planning on creating a TSQL view containing data that will be extracted by SSIS.
Ok.

>The first section of the view is to be a header section of about five fields.  The next four sections
Unless you have Super Ninja T-SQL skills, it's asking a lot of a view to contain multiple sections with varying fields in each section.  Better to create a view for each section, with a 'sort_order' column that goes something like 1 for the header, 2 for a group header, 3 for the detail, 4 for the footer, you get the idea.  

Then in your output write all columns as one, with an 'ORDER BY sort_order' to write the header, detail, etc. in the correct order.

>is it possible to tell SSIS to not pull column headings when extracting data from SQL?
Yes, eyeballeth the connection properties.
0
 
LVL 1

Author Comment

by:TBSupport
ID: 39999092
Thanks for the quick response, Jim!  Excellent!

Can you please elaborate on the following:

...in your output write all columns as one, with an 'ORDER BY sort_order' to write the header, detail, etc. in the correct order?

I don't understand how to get the TSQL syntax to accommodate this.  I thought that "ORDER BY" was forbidden in views.

Thanks, again!

TBSupport
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39999118
Trying to remember a previous project where I pulled this off, so bear with me.
The ORDER BY isn't in a view, it's in a SELECT that writes to the destination.

Source
Three views, essentially a header, detail, and footer view.
After the Source I had a merge join, that went something like this..

SELECT 1 as sort_order, column_1 + column_2 + column_n as the_row
FROM v_header
UNION ALL
SELECT 2, column_A + column_B + column_C + column_KIIJR
FROM v_detail
UNION ALL
SELECT 3, column_banana + column_grape
FROM v_footer

Destination
SELECT the_row
FROM {my table}
ORDER BY sort_order
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39999424
Thanks for the grade.  Good luck with your project.  -Jim
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
My client has a dictionary table. They're defining a list of standard naming convention. Now, they are requiring my team to provide us a mechanism how to match new incoming data with existing data in their system.
This tutorial explains how to use the VisualVM tool for the Java platform application. This video goes into detail on the Threads, Sampler, and Profiler tabs.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question