Solved

SQL Insert Statement with SUBSTRING inside a SSIS package

Posted on 2013-05-22
6
544 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
  • 4
6 Comments
 
LVL 65

Assisted Solution

by:Jim Horn
Jim Horn earned 250 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

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

Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

679 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