November 13, 2009 at 8:39 am
Hi All,
I am creating SSIS packages to import files into SQL Staging Tables. Packages would be scheduled to run daily. The files are first downloaded daily from FTP Server and then using SSIS are imported into SQL Tables. There is no task for deleting the old files in the downloaded folder. So, I need to import only the current files daily(such as d:\FTPfiles\MyFile20091113.xls)
Searching google tells me that I have to use a variable and expression like this:
"MyFile" + RIGHT ( "0" + (DT_WSTR,4)YEAR(GETDATE()),4) + RIGHT("0" + (DT_WSTR,2)MONTH(GETDATE()), 2)
+ RIGHT("0" + (DT_WSTR,2)(DAY( GETDATE())), 2) + ".xls"
As I have not done this before I don't know how to set connection string properly and get this working.
Any help would be truly appreciated.
Thanks.
________________________________________________________________
"The greatest ignorance is being proud of your learning"
November 13, 2009 at 8:51 am
Single-click the Excel connection you're using, and press F4 to bring up the properties window, if it's not already open. Click the ellipsis button beside the Expressions item, and add a new expression for the Excel File Path property (Note that if you're using a simple flat file, you'd use the Connection String property instead). In the Expression Editor, configure your expression similar to what you've included below.
Shout if this doesn't help solve the problem...
hth,
Tim
Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices
November 13, 2009 at 11:00 am
Thank You for your Advice. It was useful.
It is working but the results aren't as expected.
Here's what I did.
1. I used this expression for the Excel File Path Property:
"D:\\MyFile" + RIGHT ( "0" + (DT_WSTR,4)YEAR(GETDATE()),4) + RIGHT("0" + (DT_WSTR,2)MONTH(GETDATE()), 2) + RIGHT("0" + (DT_WSTR,2)(DAY( GETDATE())), 2) + ".xls"
2. Secondly, I removed the previous excel file(which I was testing with before - some 500 rows-MyFile20091010) from its location and put a copy of it in its place and deleted all the rows except two rows. I gave it same name with current date(Today's). So, Filename looks like this: MyFile20091113.xls. This also shows up in excel connection manager.
3. I execute the package and everything turns GREEN. But on SQL side all the 500 rows shows up in SQL table. I did this numerous times but NO LUCK !. I tried by keeping both files(One with todays' date which has two rows and the other with different date which has 500 rows) at that location
Again, it inserts all the 500 rows !! I'm Sure that I'm missing something but don't know what.
Thanks,
S.S
________________________________________________________________
"The greatest ignorance is being proud of your learning"
November 13, 2009 at 11:49 am
Are you using any external configurations, or are there any other expressions in your package? If not, it sounds like everything may be configured properly.
If you want to attach your package to this thread (you'll have to Zip it up first), I'll be glad to take a look at it.
Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices
November 13, 2009 at 12:19 pm
Tim,
I have no external configuration and other expressions as per my knowledge in this particular package. I have other packages(which do have configurations and variables and expressions for using For Each Loop container) in the same solution.
It does loads the data but somehow keeps inserting all the data present in the previously used Excel file. I believe there's something to do with the Excel file here. I may be wrong. 🙂
Thanks.:-)
________________________________________________________________
"The greatest ignorance is being proud of your learning"
November 14, 2009 at 11:56 am
Hey Tim,
Did you get a chance to look at the package? Please let me know if anything else is needed for your testing.
Thanks. 🙂
________________________________________________________________
"The greatest ignorance is being proud of your learning"
November 16, 2009 at 10:56 am
No, I pulled up the package and it looks like you've got it configured correctly. Is it possible that there are multiple worksheets in your Excel document? SSIS treats each sheet as a table - maybe it's referring to a different worksheet in your Excel file?
Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices
November 16, 2009 at 12:25 pm
Tim,
I appreciate your time and effort.
The connection is configured to connect to the Excel table name with $ sign and not the Range. And there's only one table.
What I did was just copy the same Excel file and change the date stamp and then deleted the data inside except 2 rows to mark difference. But, somehow the SQL table is inserted with all of the records sitting in the previous file instead of those 2 rows. This is kinda strange !
I will do some more testing today and post the results if got lucky.
Thanks.
________________________________________________________________
"The greatest ignorance is being proud of your learning"
November 16, 2009 at 12:54 pm
Guess Today's lucky for me. Solved couple of problems which had me stumped.
Voila !!
Got this working.
I was checking the number of rows inserted which were 601 every-time instead of 2. When I checked the actual data inside the table there were those 2 records plus NULLS in all other rows up till 601.
So, I just selected and deleted the excel rows(using delete rows option) after first two rows although there was no data and saved the file and then executed the package and this time it worked without a flaw.
Thanks. :-):-)
________________________________________________________________
"The greatest ignorance is being proud of your learning"
November 16, 2009 at 9:11 pm
Excellent, glad it's working now. Let me know if I can help with anything else.
Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices
November 17, 2009 at 9:13 am
I appreciate that. Maybe I can use some more of your help.
Now, I'm trying to do the same thing within For Each Loop Container.
About the package that I am using:
The enumerator loops through multiple CSV files and loads them to SQL table. I have used a variable with value equal to that of source connection string and then assigned that variable as an expression under source connection properties.
On evaluation I get the desired connection string. Then the variable is mapped with index = 0 under For Each Loop editor window. Package works fine.
How to use that previous expression(that gets current date) in here? I tried by passing the whole expression as a value to the variable and then marking TRUE for evaluate as expression property but that did not work.
I also tried using the expression under FOR EACH LOOP Container but that didn't worked either. :unsure:
Thanks.:-)
________________________________________________________________
"The greatest ignorance is being proud of your learning"
December 9, 2009 at 2:29 pm
Hey Tim,
I was wondering if you could help me with the following.(re-post)
I need to use a For Each Loop Container to loop through and grab all the CSV files in a particular folder.
I have used this connection string expression in CSV Source connection properties.
@[User::uvFolderPath] + "\\"+ @[User::uvFilename] + RIGHT("0" + (DT_WSTR,2)MONTH(GETDATE()), 2)
+ RIGHT("0" + (DT_WSTR,2)(DAY( GETDATE())), 2) + RIGHT ( "0" + (DT_WSTR,4)YEAR(GETDATE()),2)
+ ".csv"
Where, uvFolderPath contains the path of the folder and
uvFileName contains the fielname such as File_TX_
The variable uvFileName is used in For Each Loop Container.
Under collection: Folder is correctly selected and *.CSV is used in the files field.
The Package fails with error like this:
ERROR:
[CSV Files folder 1 [5688]] Error: Cannot open the datafile "G:\DOWNLOADS\Project\Importfiles\G:\DOWNLOADS\Project\Importfiles\File_TX_100109.csv120909.csv".
I think as the folder contains files from other dates also, this could be the reason for this error.
(As it is looking for all CSV files but i need to loop through current files only)
Maybe I need an expression in For Each Loop Container but not sure.
I'll appreciate your help !
Thanks.
________________________________________________________________
"The greatest ignorance is being proud of your learning"
December 10, 2009 at 8:06 pm
I assume that you're mapping the variable [User::uvFilename] to the filename in the For Each loop? If so, when you append the current date to the filename, the expression adds on the current date to the existing file name, a combination which most likely doesn't exist.
Is there a reason you need to modify the filename with the current date? When using the For Each loop, you can map the current item being enumerated (in this case, the current file name) to a variable.
If you're trying to only access a single file, you can use a technique I demonstrated here to skip items that don't match a specified condition (for example, the current date in the filename).
hth,
Tim
Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices
December 10, 2009 at 10:09 pm
Hi,
Thanks for your reply !
There are multiple files that comes daily with current Date Stamp on them. For single file, I face no issue using expression and variables.
But, I'm trying to loop through the folder and grab only current files using the For Each Loop Container.
Regards,
________________________________________________________________
"The greatest ignorance is being proud of your learning"
December 10, 2009 at 10:27 pm
I see. In that case, visit my blog post that I linked in the previous entry - if I understand correctly, that brief post should give you what you need to accomplish this.
hth,
Tim
Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply