?
Solved

SSIS- Importing Date/Time into SQL along with FilePath

Posted on 2012-03-27
3
Medium Priority
?
417 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 2000 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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

719 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