Solved

SSIS- Importing Date/Time into SQL along with FilePath

Posted on 2012-03-27
3
409 Views
Last Modified: 2012-04-04
I have a DTSX file that imports excel files into SQL. Rather than have SQL timestamp the entries, I need to somehow import the time that either the excel file was modified, or have the dtsx package insert into SQL when the SSIS package was executed. My second part is I have a user variable CurrentExcelPath in my for/next loop. I would like to see if I can get that path imported into the SQL DB at runtime.

Any help would be much appreciated.
Thanx
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
  • 2
3 Comments
 
LVL 16

Expert Comment

by:vdr1620
ID: 37774641
I think..
1. you should be able to use derived column to input the date time or use StartTime system variable
2. you can get the sql path using the same variable that is being used in the for each loop

Hope it makes sense .. let me know if that's not you want
0
 

Author Comment

by:BKennedy2008
ID: 37776275
I used the Excel file sheet to create a date time for when it is changed and imported that.

The only thing I have left is to import the path. I have the path variable, but not sure how to import the ExcelCurrentPath into SQL. I cannot get it to show up anywhere in the data flow task to get it to map to a OLE Destiantion field.\

Do I need to write a custom SQL statement inside the OLE DB Destination task? It will only take a select statement and not an insert. Do I use an OLE DB  Command Transformation? Seems like I am missing the boat here.....
0
 
LVL 16

Accepted Solution

by:
vdr1620 earned 500 total points
ID: 37779711
Well, you can either write a SQL statement as an expression and used SQL command from variable as your source

"select column1, column2, "+VariableName+" from tablename"

Or use Derived column transformation in b/w source and destination
 -- add a new column with the variable as the value
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

738 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