Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Insert Statement with SUBSTRING inside a SSIS package

Posted on 2013-05-22
6
Medium Priority
?
576 Views
Last Modified: 2016-02-10
Below is the diagram that takes an excel file and imports into a SQL Database. Along with that is a filepath from a derived column. Inside that filepath contains the jobnumber. Instead of importing the whole path, I just want to extract the Job number wich SUBSTRING (Filepath, 51,12) will work.


I have an OLEDB command that contains the SQL statement to insert (Colum1, column2) values (?,?) into the SQL DB

I understand the SQL Statment using SUBString:

Insert into tableA (Column1, Filepath)

Select Column1, SUBSTRING (Filepath, 51,12) as FilePath from ......

What would be the name of the table that I am selecting from inside the OLEDB Command?

2013-05-22-10-46-53-Clipboard.gif
0
Comment
Question by:BKennedy2008
[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
  • 4
6 Comments
 
LVL 66

Assisted Solution

by:Jim Horn
Jim Horn earned 1000 total points
ID: 39187902
>What would be the name of the table that I am selecting from inside the OLEDB Command?
Since your source is Excel, it will be either the tab name that contains the data, or an Excel named range that contains the data.
0
 

Author Comment

by:BKennedy2008
ID: 39187917
if that was it, then shoot me..
0
 

Author Comment

by:BKennedy2008
ID: 39187958
The OLE DB doesn't like the statement:

  INSERT INTO Imports (RosterName,  FilePath1)
  Select ?,SUBSTRING(?,51,12) from  ['Raw Data$']

Does this look correct? the error is unspecified, pretty sure it has to do with the statement
0
TCP/IP Network Protocol Cheat Sheet

TCP/IP is a set of network protocols which is best known for connecting the machines that make up the Internet. The truth is that TCP/IP is one of the oldest network protocols and its survival is mainly based on its simplicity and universality.

 

Accepted Solution

by:
BKennedy2008 earned 0 total points
ID: 39188507
I made it easy, and just did a SUBSTRING function on the Derived column
0
 
LVL 37

Expert Comment

by:ValentinoV
ID: 39190048
Just noticed this one: you'd get much better performance if you'd use the OLE DB Destination instead of Command (which works row per row).
0
 

Author Closing Comment

by:BKennedy2008
ID: 39198770
Performing a substring closest to the source saves memory space, and is cleaner
0

Featured Post

Enroll in September's Course of the Month

This month’s featured course covers 16 hours of training in installation, management, and deployment of VMware vSphere virtualization environments. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

A couple of weeks ago, my client requested me to implement a SSIS package that allows them to download their files from a FTP server and archives them. Microsoft SSIS is the powerful tool which allows us to proceed multiple files at same time even w…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

715 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