Problems making a dynamic flat file connection string
-
Hello.
I have packages that must generate log errors dynamically including time of execution and the name of the task.
1.-I alter the File Usage Type to 1 to generate this files.
2.- I alter the connection string as: @[User::my_variable] +"constant_description"+ time description+".txt"
(DT_STR,40,1252) DAY (GETDATE())+"-"+(DT_STR,40,1252) MONTH( GETDATE())+"-"+(DT_STR,40,1252) YEAR( GETDATE())+" + REPLACE( (DT_STR,10,1252) (DT_DBTIME) GETDATE(),":","_")
But it is not the problem.
In those packages I have one connector for all the tasks and in execution time it creates one file for each of the tasks.
The problem is the way I insert in the filename the task name.
I have a pre-execute event handler in each task that modifies a string variable( my_variable) appending the task name. When I execute de package it works great but when I only execute a task, the program do not enter in the event and do not put the task’s name.
How can I put that name without using that handler? There is another handler can I use to do it that happens before the system generates the new file name and after pre-execute? Anyone knows another way to do this kind of things?
Thanks
-
Nobody has any idea?
-
Unfortunately, I haven't created a solution that way. I'll take a look at it and report back to you ASAP.
Lee
-
Has anyone solved this issue?
I am attempting to dump the data into a static file name and then use the File System Task to rename the file to use date values as part of the file name.
I have created two Task Variables ("ArchiveHome" and "TableName" with scope set to the File System Task). Simultaneously, I have initialized the values to be ArchiveHome=D:\Archive and TableName=ActionLog values. Second, from the File System Task Editor I have set the IsDestinationPathVariable and IsSourcePathVariable to true. Finally, I used the expressions to build the file names such as, Source Property to have the expression:
@[User::ArchiveHome] + "\\" + @[User::TableName] + ".csv"
and Destination Property to have the expression:
@[User::ArchiveHome] + "\\" + @[User::TableName] + (DT_WSTR, 4) YEAR(GETDATE()) + "_" + (DT_WSTR, 2)MONTH(GETDATE()) + "_" + (DT_WSTR, 2)DAY(GETDATE()) + "_" + ".csv"
Unfortunately, when I execute the package I get the following error:
Error at Rename ActionLog: Failed to lock variable "D:\Archive\ActionLog.csv" for read access with error 0xC0010001 "The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.".
Error at Rename ActionLog: Failed to lock variable "D:\Archive\ActionLog2007_5_29_.csv" for read access with error 0xC0010001 "The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.".
Error at Rename ActionLog: There were errors during task validation.
Does anyone have an idea or has performed this action before?
-
Can you verify the scope of your variable? i.e. ensure its on the package
Tommy
Follow @sqlscribe -
David - sent you an e-mail w/ example.
Tommy
Follow @sqlscribe -
Tommy,
Yes, the scope of the variables is on the File System Task level within the package and associated to namespace "User". Also, so far, I only have one package. Using Package Explorer I only see my variables associated to the File System Task and no where else.
Thanks,
Muhanned
-
That certainly doesn't help the rest of us... Please post the "e-mail w/ example" here
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Muhanned,
On the task that's giving you the error, try setting the DelayValidation property to True. It might be giving you the error because it's trying to validate something (pre-package run) that doesn't exist yet.
Let us know if that doesn't help.
-
Dear Brandie,
Thanks for your input but, this did not work either. Strangely enough, I found another posting with such a similar issue on this site from Steve Maas with the topic entitled "File System Task Variable" (http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=148&messageid=274245&edit=failed).
This is why it is strange, Steve Maas indicated that the problem was solved by setting the variable read only setting to 'true'. I have tried this and it did not work.
I think what I will do is to delete this project and start over. Perhaps there is something corrupted or a bug in the project that will not allow any actions except to return this error. Not sure I think I pretty much given up on this solution as there is no reasonable explanation to why it is not working. It should be a simple task to perform.
-
Hi Muhanned,
Read the following links, especially of CaptainMyCaptain's questions and answers. Hope this helps you.
https://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1520831&SiteID=1
http://www.ssistalk.com/file_system_task.jpg
http://rafael-salas.blogspot.com/2007/03/ssis-file-system-task-move-and-rename.html
Thirumalai
-
Hi Thirumalai,
Thank you very much!!! This was exactly what I was looking to do.
I just did not realize to first setup the environment variables with expressions and scope (to the File System Task and not the package) first then use these variables within the File System Task. I was trying to setup expressions within the File System Task itself which did not work.
Very nice links.
Thanks again,
Muhanned
-
I had a similar problem with dynamic naming. I got around it by using an execute process task to run a batch file that copies the fixed file and names it according to the date.
Google this if you need, a quick example is here http://www.computing.net/dos/wwwboard/forum/14569.html
Then in my "connection object" I point to the fixed file AND at the same time override that source property with the formula that you mention should have worked ...EG ... (DT_WSTR, 30)DAY(GETDATE()) + ".txt" .. etc,, I will find the renamed file at runtime
i hope this helps
----------------------------------------------------
-
Hello,
I have a similar problem where i am saving data in a csv file from a table. I need to upload the csv file to the ftp server everyday with the date and i have no idea where to start.
Setting up the FTP is not the difficult part but making the static file dynamic with date is the problem. Someone please let me know how to solve this problem!
-
chaudharyabhijit (5/20/2010)
I have a similar problem where i am saving data in a csv file from a table. I need to upload the csv file to the ftp server everyday with the date and i have no idea where to start.Setting up the FTP is not the difficult part but making the static file dynamic with date is the problem. Someone please let me know how to solve this problem!
There are a couple of possibilities in SSIS.
1) variables and expressions
2) variables and For Each Loops
The easiest way is the second one (IMHO) if you are using the CSV as a source. The first one is the easiest if you are using the CSV as a destination.
For # 2, You can use wildcards in the Collection section of the FELoop to search for hard file names without the date (example: MyFile*.*) and then use the Variable Mappings section to map the actual file name to a variable. Then use the variable as the connection source.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply