Data Dump Schedule & Formatting 1

[mbusa]

I am reporting off from a SQL server. A user wants a data dump of the all the records and it takes about 2-3 minites for the query to run in query analyzer. I have been asked to create a 'job' which can be scheduled to run at certain intervals and dump all the required output to an excel spreadsheet.

I am novice so need some direction here. I have googled around and found that a DTS package can be made which can run the query and output to the excel. How do i format the excel output like column spacing etc.

Lastly what is that "job" that I was told about ?


[highlight]M.Bajwa

http://www.nextgenlogic.com/bajwa

[/highlight]

 

[JayKusch]

You will create a DTS package that will have your Source connection set as the SQL Server, a Destination connection which will be your Excel worksheet and a Transformation task as your data pump that will pull the data out of the database and into your worksheet.

Once you have saved the DTS "package", right click on the new package and you will be able to schedule it. Don't worry if you are not provided all the scheduling items you need, you can tweak them in a second.

Now in Enterprise Manager, drill down to Management..SQL Server Agent..Jobs. In there you will now see your DTS Package job. Double click on the job and schedule away!

As for the formatting of the Excel worksheet for column spacing and such, I can only say that when the package runs, it will put each field that you defined to pull into its own column.

As to how to format it out right ... I cannot help you there but I am sure there is another person who can!

Enjoy!

Thanks

J. Kusch

 

[mbusa]

thanx Jay..

As to how to format it out right ... I cannot help you there but I am sure there is another person who can!

Ok other person.. ur turn now !




[highlight]M.Bajwa

http://www.nextgenlogic.com/bajwa

[/highlight]

 

[JayKusch]

What kind of formatting are you looking for?

Thanks

J. Kusch

 

[mbusa]

well.. nothing special. all I want is the columns to be neatly spaced out. Right now when I export it using the DTS package long fields like address and cut and dont show up unless i increase the width of the column.hope you understand what i am asking for

[highlight]M.Bajwa

http://www.nextgenlogic.com/bajwa

[/highlight]

 

[JayKusch]

Try formatting your worksheet first then run the DTS job.

Thanks

J. Kusch

 

[mbusa]

Reverse Engineering.. great Idea.. but we have another problem. The query is now a stored procedure using temp tables and I have been told that it can not be used in DTS . Is that True ?

2ndly what if i format the excel file first and then the end user "Cuts" it from the taregt location , the DTS package will just go ahead a make a new excel file without the formatting .. right ?



[highlight]M.Bajwa

http://www.nextgenlogic.com/bajwa

[/highlight]

 

[JayKusch]

Question 1 ... even though it may grumble a bit, you can use DTS and temp tables. It just does not resolve the tables in the designer.

And yes, darn it, I believe if the package has to create the worksheet from scratch, it will loose its formatting.

A way around that is to create the excel worksheet formatted all pretty and such and name it something like GenericTemplate.xls

Then as an initial step in your package, create and Execute SQL Command Task and in it put something like ...

EXEC Master..xpCmdShell 'Copy C:\GenericTemplate.xls LiveWorksheet.xls'

Then have your destination connection point to the LiveWorksheet.xls. Now even if they grab/delete/remove the LiveWorksheet.xls ... your package will create a new one.

Thanks

J. Kusch

 

[mbusa]

hmm.. u seem to have all the answers which is great. I think I have sufficient answers to go ahead and start on this. I will get bakc to you and update soon. My only concern right now is :

1. How much grumbling will it take on temp tables in DTS

2. Where exactly would i insert the EXEC Master..xpCmdShell 'Copy C:\GenericTemplate.xls LiveWorksheet.xls'

I mean is there some script editor or DTS editor in sql server ?

[highlight]M.Bajwa

http://www.nextgenlogic.com/bajwa

[/highlight]

 

[JayKusch]

1. How much grumbling will it take on temp tables in DTS

'It only grumbles if you choose to parse the query in the designer'
[\b]
2. Where exactly would i insert the EXEC Master..xpCmdShell 'Copy C:\GenericTemplate.xls LiveWorksheet.xls'

You would create an "Execute SQL Task" from the tasks list in the DTS Designer (explained next in question 3)

I mean is there some script editor or DTS editor in sql server ?

Open Enterprise Manager. Go down to Data Transformation Services tab. Then right-click on Local Packages and choose "New Package". You are now in the DTS designer app.


Also be sure to create BOTH worksheets before you get into DTS. Create your formatted GenericTemplate and then manually copy it to the LiveWorksheet. This way all the pieces are in place to start the DTS design process.

Thanks

J. Kusch

 

[mbusa]

I think I have it all set. When i tried to run it I throws back an erro saying "could not find stored procedure ..xpCmdShell " I gues its not there or soemthing . is the code somewhere out there where i could get it from ?


Secondly the fact that you say that i should have both spreadsheets before to be there before i play with the DTS. Why is that . Even if I only make the template.xls wont the copy statement then make the live worksheet if the xls isnt there already ?

[highlight]M.Bajwa

http://www.nextgenlogic.com/bajwa

[/highlight]

 

[JayKusch]

Master..xp_CmdShell

Thanks

J. Kusch

 

[mbusa]

hey Jay. Update time.. Well I could never get the cmdshell to work. Instead I was given the idea of making a batch file which is another great way to do it. So my batch file runs first and then makes the copy of the worksheet and then my Job runs.
I now have another problem. When I click on the execute DTS package , it runs fine, but when I make a job of the same DTS package the job fails to run !
what is going on. Someone said it could be somethign with the permissions. So I made a shared dir on my local pc , gave "everyone" full rights to it and then had the DTS package dump to \\my_local_pc\my_shared_dir

the same thing happens. When I run the DTS package , it runs fine, but when I make a job of the same DTS , it fails !


Any ideas?

[highlight]M.Bajwa

http://www.nextgenlogic.com/bajwa

[/highlight]

 

[Catadmin]

Whoever told you this was a permissions thing is probably correct. When you created the job, who was the job owner?

Did the job owner have read/write permissions to the sharefile?

Did you try changing the job owner to SA and verifying the Domain account that SQL is using (or SA is using) has read/write permissions on the sharefile?

Does the domain account have "Log on Locally" permissions on the box you're saving the file to?

Just a few things to test. It shouldn't take you long to find out if any of these options work or not.



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[mbusa]

thanx for the response. I checked and made sure i was the job owner. The login I use on the box is the same as the one i specified for the owner of the job. And I mysel fhave full permissions on the share and the whole box itself too.

When I run the job i just get the error email saying
MESSAGES: The job failed. The Job was invoked by User "MyDomain\MyUserName".


Then I changes the owner to SA and tried to run the job and it still says that the job was invoked by me ! ( which is true i guess )
I will schedule and then wait for it to run on its own and see if that works.

[highlight]M.Bajwa

http://www.nextgenlogic.com/bajwa

[/highlight]