December 18, 2007 at 2:20 am
Hi.
I have a question:
I'm about to create a SSIS package that has to do the following:
- Copy an Excel file from one directory and to another.
- The file that has been copyed is to be filled with data from an SQL sourec.
- Everytime the SSIS package is executed a new Excel file has to be created and the one(ce) that are already in the directory must remain there.
- I would like to have the date for when the file has been created as part of the file name e.g. P404PTPDump20071212.xls and when the task is running the next times the filename(s) has to be e.g. P404PTPDump20071213.xl, P404PTPDump20071214.xls
- My current setup is like this:
- I have an filesystem task that copies the file and in the destination field a connection manager is set to create file.
- It calls a dataflow task that has an OleDB conneciton to the sql server with the query.
- The destination is an Excel destination where I use the Data access mode "Table or view", and creates an new sheet/table for the data. The mappings seems to be working fine.
When I run the job the data is filled in the file that is to be copied and they arent overwritten when the jobs runs the second,third times - what am I missing here?
I hope I'm making myself understandable, but I'm very new to creating SSIS packages.
Thanks for your help.
\Jan
December 18, 2007 at 9:07 am
Jan
try creating a global variable for the file name and then set the file name in the file. In my packages I run an SQL task of the following
SELECT 'C:\LOADFILES\999_XXXXXXX_' + CAST(DATEPART(YYYY, GETDATE()) AS VARCHAR (4))
+
CASE
WHEN LEN(DATEPART(MM, GETDATE())) = 1 THEN '0' + CAST(DATEPART(MM, GETDATE()) AS VARCHAR(2))
ELSE CAST(DATEPART(MM, GETDATE()) AS VARCHAR(2))
END
+
CASE
WHEN LEN(DATEPART(DD,GETDATE())) = 1 THEN '0' + CAST(DATEPART(DD, GETDATE()) AS VARCHAR(2))
ELSE CAST(DATEPART(DD, GETDATE()) AS VARCHAR(2))
END
+
CASE
WHEN LEN(DATEPART(HH,GETDATE())) = 1 THEN '0' + CAST(DATEPART(HH, GETDATE()) AS VARCHAR(2))
ELSE CAST(DATEPART(HH, GETDATE()) AS VARCHAR(2))
END
+
CASE
WHEN LEN(DATEPART(MI,GETDATE())) = 1 THEN '0' + CAST(DATEPART(MI, GETDATE()) AS VARCHAR(2))
ELSE CAST(DATEPART(MI, GETDATE()) AS VARCHAR(2))
END
+
'.XLS'
I then assign that result set to the variable. If you need more help email me and perhaps we can IM and walk you through it.
Marvin
Marvin Dillard
Senior Consultant
Claraview Inc
December 19, 2007 at 2:30 am
Jan;
You should use variables and expressions to achieve the task you mentioned.
I provided an example package (see attachment) that does the following:
- determine the startdate of the package and use that as part of the destinationfilename (variable with expression)
- copy the template from a given filelocation (variable)
- set all connections using variables (especially handy since the DestinationFullName needs to be used on two separate places).
Check the way the variables are build (View - Other Windows - Variables), and how they are used in the properties of the connections and the FileSystemTask.
This should help you out to complete the task you have at hand.
Marvin,
I wouldn't set up a SQL task just to get a string with the current date and time, expressions are the way to go for this in SSIS.
However, if you want to do this in T-SQL, may I suggest you take a closer look at the CONVERT function? It would take far less code, functions and logic that way:
SELECT'C:\LOADFILES\999_XXXXXXX_'
+ convert(varchar(8), getdate(), 112)
+ replace(convert(varchar(10), getdate(), 108), ':', '')
+ '.XLS'
Edited for typos
Peter Rijs
BI Consultant, The Netherlands
December 19, 2007 at 5:05 am
Peter
I agree with you about the convert function, that was a hack of some other code that I have that actually reads data stored in the SQL Server. I never use dates without the time piece for accountability purposes. There are many great things you can use the SQL task for (can't you tell it is my favorite) so I tend to use it quite often.
Thanks
Marvin
Marvin Dillard
Senior Consultant
Claraview Inc
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply