February 21, 2012 at 1:10 pm
I'm trying to import an xml file that has a date in the filename. So I'm trying to use it as a connection variable to import then rename it back and move to archive folder. Any help is appreciated.
There is an exception to every rule, except this one...
February 21, 2012 at 1:51 pm
Is there the possiblity of multiple files? If so you can use a for each loop container which will automatically populate a variable with its current filename.
If its a "static" filename + date then you can use a file connection object which builds its connection using an expression. I would create a package variable called date which is populated by an expression (today, yesterday ...whatever).
I would then edit my file connection to concatenate this variable with the static part of the filename. If needed you can edit this component to delay validation until execution.
If it were me, I'd go for the for each loop configuration. Less problems if files are incorrectly named or if the process fails or gets a backlog to process.
February 21, 2012 at 2:06 pm
I appreciate your input. For now it's 1 file a day and I require a bit more hand holding and granular steps. The file name looks like 02212012_test.xml. How do I connect to the daily file, then after import, rename back to original name?
There is an exception to every rule, except this one...
February 21, 2012 at 2:31 pm
I'm not going to be able to do this for you. I can point you in the right direction of information where you can help yourself.
This article covers a number of the principles you will need to accomplish this.
As I said before I'd create a package variable called date of a string type. Edit its properties to evaluate as expression then edit the expression to use this. This will produce the 02212012 component of your file name.
RIGHT("00"+(DT_STR, 2, 1252) MONTH( GETDATE() ), 2)+
(DT_STR, 2, 1252) DAY(GETDATE())+
(DT_STR, 4, 1252) YEAR( GETDATE() )
You will then need a dataflow task to import the data. Drag this into you Control Flow dialog and go to the dataflow dialog. Guesswork here. Drag a Flat File Source or XML connection onto the dataflow and an OLEDB Destination onto the same and connect using the arrow. You will need to edit both of these to point to the source and the destination and map the columns as approrite. All these assumes its a straight import of a file.
Once you've done this you will have a flat file connection which you will be able to edit the expression of to concatenate the variable above to "_test.xml" thus making it dynamic. This "_test.xml" string could also be set as a variable so that if could be passed into the package for completeness
Once the data import is configured go back to the Control Flow and add a File system task which can also have its connection set as a variable.
February 21, 2012 at 2:39 pm
Create a variable of string type and set its value as the following:
(DT_STR, 4, 1252)DATEPART("yyyy", @[System::ContainerStartTime]) +
RIGHT("0" + (DT_STR, 2, 1252)DATEPART("mm", @[System::ContainerStartTime]), 2) +
RIGHT("0" + (DT_STR, 2, 1252)DATEPART("dd", @[System::ContainerStartTime]), 2) + "Filename.xml"
Right click on the connection manager and select properties.
Copy the ConnectionString value.
Scroll down to the "Expressions" and then in the "Property" select connection string. Modify the connection string with the variable that you created previously. I think this would help you.
You can always revert the connection string back to its old form using this method.
February 21, 2012 at 2:41 pm
rajiv.varma (2/21/2012)
Create a variable of string type and set its value as the following:(DT_STR, 4, 1252)DATEPART("yyyy", @[System::ContainerStartTime]) +
RIGHT("0" + (DT_STR, 2, 1252)DATEPART("mm", @[System::ContainerStartTime]), 2) +
RIGHT("0" + (DT_STR, 2, 1252)DATEPART("dd", @[System::ContainerStartTime]), 2) + "Filename.xml"
Right click on the connection manager and select properties.
Copy the ConnectionString value.
Scroll down to the "Expressions" and then in the "Property" select connection string. Modify the connection string with the variable that you created previously. I think this would help you.
You can always revert the connection string back to its old form using this method.
Wrong order for the example but you did correctly put in the additional RIGHT which I forgot
RIGHT("00"+(DT_STR, 2, 1252) MONTH( GETDATE() ), 2)+
RIGHT("00"+(DT_STR, 2, 1252) DAY(GETDATE()), 2)+
(DT_STR, 4, 1252) YEAR( GETDATE() )
May 23, 2016 at 2:11 am
The following will return a date/time when put in an expression in SSIS.
(DT_STR, 4, 1252)DATEPART("yyyy", @[System::ContainerStartTime]) +
RIGHT("0" + (DT_STR, 2, 1252)DATEPART("mm", @[System::ContainerStartTime]), 2) +
RIGHT("0" + (DT_STR, 2, 1252)DATEPART("dd", @[System::ContainerStartTime]), 2) +
RIGHT("0" + (DT_STR, 2, 1252)DATEPART("hh", @[System::ContainerStartTime]), 2) +
RIGHT("0" + (DT_STR, 2, 1252)DATEPART("mi", @[System::ContainerStartTime]), 2) +
RIGHT("0" + (DT_STR, 2, 1252)DATEPART("ss", @[System::ContainerStartTime]), 2)
How can I have the SSIS
Return yesterday in SSIS Expression? because my data is yesterday's data (-1)?
Here is the answer:
Right("0" + (DT_STR,4,1252) DatePart("YY",DATEADD("DD",-1,getdate() ) ),4) +
Right("0" + (DT_STR,4,1252) DatePart("MM",DATEADD("DD",-1,getdate() ) ),2) +
Right("0" + (DT_STR,4,1252) DatePart("DD",DATEADD("DD",-1,getdate() ) ),2)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply