Solved

SQL Insert Statement with SUBSTRING inside a SSIS package

Posted on 2013-05-22
6
534 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how the fundamental information of how to create a table.

911 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now