Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SSIS- Importing Date/Time into SQL along with FilePath

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

886 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