• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 424
  • Last Modified:

SSIS- Importing Date/Time into SQL along with FilePath

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
BKennedy2008
Asked:
BKennedy2008
  • 2
1 Solution
 
vdr1620Commented:
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
 
BKennedy2008Author Commented:
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
 
vdr1620Commented:
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now