Solved

SQL Insert Statement with SUBSTRING inside a SSIS package

Posted on 2013-05-22
6
537 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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

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

Suggested Solutions

Title # Comments Views Activity
Get the latest status 8 32
Tsql query 6 22
Weighted Randomizing 6 16
syntax sql error 2 14
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
I have a large data set and a SSIS package. How can I load this file in multi threading?
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.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

821 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