How to Manage Errors with one file

Hi,

I am pretty new in SSIS 2005, and I have some problems... I want to add logging and error management in my package. I found how to made logging. But for errors managing i have some difficulties.

In my package I have only a flat file source and an ole db destination. I want add errors management for both of them. So I create a connection manager for errors on a file. For both element i add redirect row for all available error type and then i add 2 flat file destination. I branch red arrows of flat file source and ole db destination to the flat file destination.

When i run packge i have an error which indicate me that file error is already take by another process... I don't understand why. And i don't want to create on file for each element on package. Have you any idea on why i have this error? Or how can i made what i want do?

Krest

Before going too far - could you checj if your redirect destination do not point to the same file as primary error file? What happens if you turn off package logging?

|||

Hi,

Thanks for you help.

So, i use the same file for the two flat file destination, because i want all my error in the same file. I i turn off logging (SSISmenu->logging and all checkboxes are not checked.

I have the same error as before. here is the exact error message [Flat File Destination 1 [806]] Warning: The process cannot access the file because it is being used by another process.

krest

How to manage different input (Excel files) format

Hi all,

I have created a package which import data from excel file and do some technical & business validation on the data. My package has about 20 control flow items. Now I'm asked to handle a second (and probably more in the future) excel file format (columns name are different, some fields are murged in one single column...).

I definitely don't want to create a different package for each excel file format. But I can't find a way in the control flow to execute a particular DataFlow in one case and another DataFlow in other cases. Typically I would like to evaluate an expression an depending on the result execute a DataFlow or another one. Even in a given DataFlow I cant find a way to have a condition and process different Excel Source depending on an expression result. Or it would be good if I could say to my Excel Source to discover the columns name and types at runtime and let me manage the columns manually in the data flow. Is that possible ? I know SSIS manage metadata on the columns based on the data source is there any way to manage the metadata manually ? I coulnd't find anything about that in BOL.

I guess an easy workaround is to have a different package just to import the different excel files in a common staging table and each package calls a single package which contains all technical & business validation.

Any help will be appreciated.

Kind regards,

Sbastien.

Have you discovered the expressions on precedence constraints? They seems like ideal fit for your requirements.

Double click a precedence constraint line, select a condition and an expression.|||

Right! That's what I needed.

Thanks for your answer.

How to make input columns unavailable for downstream components?

Hi,

In a SSIS Data flow task, whatever you are doing with the input columns that come from a Data source like a Flat File Source, these columns are always visible and available as input columns for all Transformation and Destination components in the Data flow.

Our Custom Component is a column mapping component that transforms many input columns into many oputput columns and we would like that the used input columns are not available anymore to the downstream components...

Is that possible?

I saw that with the Unpivot component it is possible to make some input columns unavailable to downstream components...So I think there is a way to do the same in a custom component...

Thanks for any help,

David

David-Paris wrote:

Hi,

In a SSIS Data flow task, whatever you are doing with the input columns that come from a Data source like a Flat File Source, these columns are always visible and available as input columns for all Transformation and Destination components in the Data flow.

Our Custom Component is a column mapping component that transforms many input columns into many oputput columns and we would like that the used input columns are not available anymore to the downstream components...

Is that possible?

I saw that with the Unpivot component it is possible to make some input columns unavailable to downstream components...So I think there is a way to do the same in a custom component...

Thanks for any help,

David

If the component you are building is a synchronous component then the rows will be available downstream. That is simply inherent in the nature of the data-flow.

Unpivot is an asynchronous component (just like Merge Join, Union All, Sort etc...). You can think of asynchronous as meaning that the "shape" of the data changes when it goes through the component (that's not really what it means but for simplicity - it works).

You can make your custom component asynchronous if you want but there isn't much point - it will degrade performance.

-Jamie

how to make export file name dynamic

Is it possible to specify the file name of exported file? We like to have
certain naming convention, including timestamp.I believe you can change the DisplayName property to get what you want. I
have noticed the exported file name is based on the DisplayName attribute of
the report, but I have only done this with ReportViewer control. Lookup this
property under the reporting service documentation.
Good luck,
Jeff
"Jason Wang" wrote:
> Is it possible to specify the file name of exported file? We like to have
> certain naming convention, including timestamp.|||On Apr 30, 5:50 pm, Jeff Tu <Jef...@.discussions.microsoft.com> wrote:
> I believe you can change the DisplayName property to get what you want. I
> have noticed the exported file name is based on the DisplayName attribute of
> the report, but I have only done this with ReportViewer control. Lookup this
> property under the reporting service documentation.
> Good luck,
> Jeff
> "Jason Wang" wrote:
> > Is it possible to specify the file name of exported file? We like to have
> > certain naming convention, including timestamp.
As far as I know, there's not really any control over this. I would
suggest using a follow behind stored procedure that uses:
EXEC xp_cmdshell 'rename OriginalReportName.rdl
NewReportName043007_0930.rdl'
Ofcourse, this would need to be called after the report was saved.
Sorry I could not be of greater assistance.
Regards,
Enrique Martinez
Sr. Software Consultant|||Thank you for your replies.
Is it possible to do it in Delivery Extension? If possible, how hard will it
be?
"EMartinez" wrote:
> On Apr 30, 5:50 pm, Jeff Tu <Jef...@.discussions.microsoft.com> wrote:
> > I believe you can change the DisplayName property to get what you want. I
> > have noticed the exported file name is based on the DisplayName attribute of
> > the report, but I have only done this with ReportViewer control. Lookup this
> > property under the reporting service documentation.
> >
> > Good luck,
> > Jeff
> >
> > "Jason Wang" wrote:
> > > Is it possible to specify the file name of exported file? We like to have
> > > certain naming convention, including timestamp.
>
> As far as I know, there's not really any control over this. I would
> suggest using a follow behind stored procedure that uses:
> EXEC xp_cmdshell 'rename OriginalReportName.rdl
> NewReportName043007_0930.rdl'
> Ofcourse, this would need to be called after the report was saved.
> Sorry I could not be of greater assistance.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>

How to make Application.LoadPackage() and Package.Execute() to run asynchroniously?

Hi,

I am trying to execute a SSIS package programmatically. When a user drops a file in a shared folder, we execute the package based on that file. I am using SqlServer.DTs.Runtime.Application.LoadPackage() and SqlServer.DTs.Runtime.Package.Execute() functions each time to do this.

The problem is, when, say two people drop a file, the second one will not execute untill the first one is completed. I also tried only calling LoadPackage() a single time, and then storing the instance and calling Execute() in a different thread on each file drop; although the blocking still occurs. I assume the Package object is the one doing the blocking then behind the scenes.

Is there any built in functionality to make these functions (Application.LoadPackage() and Package.Execute()) run async? If not, has anyone had much success sticking these calls into a thread? I tried sticking these calls into a thread using System.Threading.ThreadPool.QueueUserWorkItem(), and this appears to work, however it randomly crashes the program when I drop multiple files one after the other. The exception also isn't too helpfull (pasted below):

"The package failed to load due to error 0xC0011008 "Error loading from XML. No further detailed error information can be specified for this problem because no Events object was passed where detailed error information can be stored.". This occurs when CPackage::LoadFromXML fails."

So, is what I am trying to do possible? I know it must be, because when I was using .NET 1.X, I was just calling dtexec via the command line, and dtexec was able to execute many packages simultaneously....

Thanks for any help,

DrewThe calls are synchronous, but each package object is independent - so if you create a thread per incoming file, create a new package object and do LoadPackage/Execute you should get the behavior you need.

The random errors you see are probably caused by your code trying to load the DTSX file before it was fully copied. So SSIS tries to read partial file and throws the error reporting it is not a valid XML file. You need some way to ensure you only load a file when it is ready, e.g. try opening it exclusively until you succeed (it will fail if someone is still writing to the file).

How to make an if in the dataflow ?

I have a dataflow where i import 2 files. The one is the file containing a couple of million records. The other file contains rows with summed values on a specific key.

The file with the millions of records is aggregated on the key, sorted, so that the 2 collums from the files can be compared. I then do a mergejoin on the key and now i have temptable with the (key,sum1,sum2). Now there must not be a difference between sum1 and sum2.
I can make a conditional split where i say ([sum1] - [sum2]) > 0.1 so that i get an output with rows where the diffence is more than 0.1.

My question is now, how do i make an action on that. If that task put out a row or more then do something (send mail task, stop further processing) ?

CgplJust add a "RecordCount" transform to your pipeline. So you count "Error Records". In the Control Flow you can change the "Link" between the task and change the "constraint options" to "Expression". There you can check if the value of the variable you used for the RecordCount is greater then 0. If so you can link to a send mail task or whatever you want...

HTH
Thomas|||Thanks but can you point that out in detail ?|||Ahhh Found out! Thanks|||This may help: http://blogs.conchango.com/jamiethomson/archive/2005/07/25/1843.aspx

-Jamie
EDIT: Ahh, except that you already worked it out while I was posting this. Never mind :)

how to make a text file

I have a table(tblUser) on SQL Server. I like to create a text file somewhere on the SQL Server after a new record is inserted into the table(tblUser). The content of the text file should be the new record. Is there any way to do that?

Thanks!

Regards,

Kevin Jinyou could have a trigger which fires off a script with xp_cmdshell, but that's probably not quite ideal..

How to make a File Share Subscription running on Vista?

I recently migrate to Vista. I had a bunch of reports with file share subscriptions that were running fine on XP.

After installing reporting services on vista (that part only was a challenge), I re-created my subscriptions using the report manager. As expected, when a subscription is executed, the ‘last run’ column shows the last time the report output has been delivered to the file share and the ‘status’ column shows ‘New Subscription’. I thought that this was the signature of a successfully configured subscription.

But surprisingly, there is nothing in the file share. The directory is empty. Anybody has an idea why? Anybody knows how I could possibly find information on my system to find the cause of the problem?

Also, in report manager – after spending hours to figure that I had to run IE in admin mode to see the content of the web app - I cannot delete subscriptions. After selecting the subscription and pressing the delete button I get a JavaScript error and nothing happens. Any trick I could use to get rid of the unused subscriptions (other than using the Reporting Services API).

Thanks,

Dom.

Hi Dom,

There are a few things here.

1. You mention that the subscription executed successfully as the "Last Run" column is updated. In that event the "Status" column should also be updated to something like "The file was successfully saved...." or "Failure writing file....", etc and not remain as "New Subscritpion".

2. Can you check if the fileshare has adequate permissions for the owner of the sunscription?

3. I'm not aware of the JavaScript error and using SOAP API(DeleteSubscription) would be the best way to go about it.

Thanks,
Sharmila

|||

Hi Sharmila,

Thanks for your reply. You are absolutely right; the status column shouldn't say 'new subscription' but 'the file was successfully ...'. Good call!

I checked the file share permissions and unless I am missing something I think the permissions are ok. Everything is running on the same machine (reporting services, sql server 2005 enterprise edition trial and the shared folder).

Let’s say my machine name is ‘ka’ and the user name is ‘boom’.Boom is an admin on the machine (remember I am running Vista).The owner of the subscription is ‘ka\boom’ and the file share delivery extension is set with ‘ka\boom’ and his password.

On the same machine, I have a shared folder and the owner permissions are given to ‘ka\boom’.

I have another machine running XP on my local network with reporting services.I tried to configure a subscription on the XP machine with a file share delivery pointing to the shared folder on my machine running Vista.The result is the same, the status column keep saying ‘new subscription’ while the last run column gets updated correctly.

Finally, yes I did use the soap api to get rid of the undesired subscriptions.

Thanks again,

Dom.

|||

Can you include the logs specifically around the time the subscription fired?

Thanks,
Sharmila

|||

I re-configured the whole thing today in order to get good log files for you and this time I got a different error message in the ‘status’ column of the report manager. I got: “Failure writing file Test.pdf : An impersonation error occurred using the security context of the current user.”

I zipped all the log files I could think might help to solve the problem. You can get the package at: http://dchoquette.aaafly.com/logs/log_files.zip

Let me know if you need more logs to diagnose the problem.

I created the subscription at 11:03 am today (February 22^nd, 2007) and the subscription fired at 11:10 am.

I am going to start looking on my side for answers related to that new error message that I got.

Thanks for you help.

|||

I was puzzled before that the "Status" column was not updated even though the subscription executed successfully. It makes sense now that both the "Last Run" and "Status" columns are updated. It looks like a permission issue to me. I do not have enough info from the logs. You need to send the report server service log of this format(Eg: ReportServerService__02_13_2007_14_38_32.log).

Thanks,

Sharmila

|||

I just updated the zip file, you can get it at the same location. It now contains the log file you requested and I briefly saw permission problems around 11:10 am. Not sure what is the cause though. I didn’t know there was a ReportServerService AND a ReportServerServiceMain in the logs directory :)

Let me know if you find out why I have that permission issue.

|||

Talking to the developer here, it looks like it could be two things:

1. RS Windows service account does not have enough rights.

2. Are you seeing this only on your Vista machine? Have you tried on a different machine?

Thanks,
Sharmila

|||

The RS Windows service is running under the ‘Local System account’ and the option ‘Allow service to interact with desktop’ is unchecked.This must be the default configuration because I do not remember changing the log on settings.

Yes I only experience this problem on Vista.The exact same subscription configuration is working fine on another machine running XP.

Thanks for helping me, I really appreciate it.

|||

To narrow down and track the real issue, can you try using different accounts for the RS Windows service to see if that works?

Thanks,
Sharmila

|||

I couldn't repro this in house and so was wondering if you have the latest released SP2 installed(not CTP3)? See if that makes a difference.

Thanks,

Sharmila

|||

I tried to change the log on account of the reporting services window service. I configured it with an administrator account on my machine. When I tried to create a file share subscription to test I got the following error message:

A subscription delivery error has occurred. (rsDeliveryError) Get Online Help

	The report server cannot decrypt the symmetric key used to access sensitive or encrypted data in a report server database. You must either restore a backup key or delete all encrypted content. Check the documentation for more information. (rsReportServerDisabled) (rsRPCError) Get Online Help
	Bad Data. (Exception from HRESULT: 0x80090005)

Not sure what to do to get that other problem out of the way. I changed the log on account of reporting services back to ‘local system account’ and now I can create the file share subscription but it still gives me the same error:

“Failure writing file Test.pdf : An impersonation error occurred using the security context of the current user.”

Concerning which service pack I have installed, I checked the digital signature of the executable I installed on my system and the Singing time is ‘Saturday, December 09, 2006 4:12:36 AM’. Hopefully you can tell if it is the latest release of SP2 and not CTP3.

Thanks,

Dom.

|||

It looks like you have CTP3. Check out the post about the release of SP2.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1247133&SiteID=1

Thanks,

Sharmila

|||

I installed the new release of SP2 and I still get the same error. That's it, I give up :) ... I wrote a custom delivery extension that is writing the report output in a directory and it is working fine for my needs.

I had a question concerning the delivery extensions if you don't mind. Do you know if it is possible to reuse the fonctionality of the built-in email delivery extension from the custom delivery extension I wrote? If so, do you have a link to a page explaining how to do so?

Thanks a lot for your help, you have been very helpful.

Dom

How to make a File Share Subscription running on Vista?

I recently migrate to Vista. I had a bunch of reports with file share subscriptions that were running fine on XP.

After installing reporting services on vista (that part only was a challenge), I re-created my subscriptions using the report manager. As expected, when a subscription is executed, the ‘last run’ column shows the last time the report output has been delivered to the file share and the ‘status’ column shows ‘New Subscription’. I thought that this was the signature of a successfully configured subscription.

But surprisingly, there is nothing in the file share. The directory is empty. Anybody has an idea why? Anybody knows how I could possibly find information on my system to find the cause of the problem?

Also, in report manager – after spending hours to figure that I had to run IE in admin mode to see the content of the web app - I cannot delete subscriptions. After selecting the subscription and pressing the delete button I get a JavaScript error and nothing happens. Any trick I could use to get rid of the unused subscriptions (other than using the Reporting Services API).

Thanks,

Dom.

Hi Dom,

There are a few things here.

1. You mention that the subscription executed successfully as the "Last Run" column is updated. In that event the "Status" column should also be updated to something like "The file was successfully saved...." or "Failure writing file....", etc and not remain as "New Subscritpion".

2. Can you check if the fileshare has adequate permissions for the owner of the sunscription?

3. I'm not aware of the JavaScript error and using SOAP API(DeleteSubscription) would be the best way to go about it.

Thanks,
Sharmila

|||

Hi Sharmila,

Thanks for your reply. You are absolutely right; the status column shouldn't say 'new subscription' but 'the file was successfully ...'. Good call!

I checked the file share permissions and unless I am missing something I think the permissions are ok. Everything is running on the same machine (reporting services, sql server 2005 enterprise edition trial and the shared folder).

Let’s say my machine name is ‘ka’ and the user name is ‘boom’. Boom is an admin on the machine (remember I am running Vista).The owner of the subscription is ‘ka\boom’ and the file share delivery extension is set with ‘ka\boom’ and his password.

On the same machine, I have a shared folder and the owner permissions are given to ‘ka\boom’.

I have another machine running XP on my local network with reporting services.I tried to configure a subscription on the XP machine with a file share delivery pointing to the shared folder on my machine running Vista.The result is the same, the status column keep saying ‘new subscription’ while the last run column gets updated correctly.

Finally, yes I did use the soap api to get rid of the undesired subscriptions.

Thanks again,

Dom.

|||

Can you include the logs specifically around the time the subscription fired?

Thanks,
Sharmila

|||

I re-configured the whole thing today in order to get good log files for you and this time I got a different error message in the ‘status’ column of the report manager. I got: “Failure writing file Test.pdf : An impersonation error occurred using the security context of the current user.”

I zipped all the log files I could think might help to solve the problem. You can get the package at: http://dchoquette.aaafly.com/logs/log_files.zip

Let me know if you need more logs to diagnose the problem.

I created the subscription at 11:03 am today (February 22^nd, 2007) and the subscription fired at 11:10 am.

I am going to start looking on my side for answers related to that new error message that I got.

Thanks for you help.

|||

I was puzzled before that the "Status" column was not updated even though the subscription executed successfully. It makes sense now that both the "Last Run" and "Status" columns are updated. It looks like a permission issue to me. I do not have enough info from the logs. You need to send the report server service log of this format(Eg: ReportServerService__02_13_2007_14_38_32.log).

Thanks,

Sharmila

|||

I just updated the zip file, you can get it at the same location. It now contains the log file you requested and I briefly saw permission problems around 11:10 am. Not sure what is the cause though. I didn’t know there was a ReportServerService AND a ReportServerServiceMain in the logs directory :)

Let me know if you find out why I have that permission issue.

|||

Talking to the developer here, it looks like it could be two things:

1. RS Windows service account does not have enough rights.

2. Are you seeing this only on your Vista machine? Have you tried on a different machine?

Thanks,
Sharmila

|||

The RS Windows service is running under the ‘Local System account’ and the option ‘Allow service to interact with desktop’ is unchecked. This must be the default configuration because I do not remember changing the log on settings.

Yes I only experience this problem on Vista.The exact same subscription configuration is working fine on another machine running XP.

Thanks for helping me, I really appreciate it.

|||

To narrow down and track the real issue, can you try using different accounts for the RS Windows service to see if that works?

Thanks,
Sharmila

|||

I couldn't repro this in house and so was wondering if you have the latest released SP2 installed(not CTP3)? See if that makes a difference.

Thanks,

Sharmila

|||

I tried to change the log on account of the reporting services window service. I configured it with an administrator account on my machine. When I tried to create a file share subscription to test I got the following error message:

A subscription delivery error has occurred. (rsDeliveryError) Get Online Help

	The report server cannot decrypt the symmetric key used to access sensitive or encrypted data in a report server database. You must either restore a backup key or delete all encrypted content. Check the documentation for more information. (rsReportServerDisabled) (rsRPCError) Get Online Help
	Bad Data. (Exception from HRESULT: 0x80090005)

Not sure what to do to get that other problem out of the way. I changed the log on account of reporting services back to ‘local system account’ and now I can create the file share subscription but it still gives me the same error:

“Failure writing file Test.pdf : An impersonation error occurred using the security context of the current user.”

Concerning which service pack I have installed, I checked the digital signature of the executable I installed on my system and the Singing time is ‘Saturday, December 09, 2006 4:12:36 AM’. Hopefully you can tell if it is the latest release of SP2 and not CTP3.

Thanks,

Dom.

|||

It looks like you have CTP3. Check out the post about the release of SP2.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1247133&SiteID=1

Thanks,

Sharmila

|||

I installed the new release of SP2 and I still get the same error. That's it, I give up :) ... I wrote a custom delivery extension that is writing the report output in a directory and it is working fine for my needs.

I had a question concerning the delivery extensions if you don't mind. Do you know if it is possible to reuse the fonctionality of the built-in email delivery extension from the custom delivery extension I wrote? If so, do you have a link to a page explaining how to do so?

Thanks a lot for your help, you have been very helpful.

Dom

sql

how to loop through several excel sheets in a file in integration services

Hello, I'm new at Integration services and I have an excel file with information in several worksheets. I want to loop through some specific sheets to retrieve the data and save it in a database table. I know how to retrieve the data from one sheet, but I don't know how to do it for several sheets. Any ideas?...I would appreciate any help.

Moving thread to the Intergration Services forum. They may be able to help out.

|||

Trisha1802 wrote:

Hello, I'm new at Integration services and I have an excel file with information in several worksheets. I want to loop through some specific sheets to retrieve the data and save it in a database table. I know how to retrieve the data from one sheet, but I don't know how to do it for several sheets. Any ideas?...I would appreciate any help.

Unfortunately its not possible to loop through sheets unless you know the names of them and you know that they won't change. If those conditions are satisfied then you can use the ForEach item enumerator in the Foreach Loop.

An Excel Sheet enumerator would be a nice addition. Perhaps you could request it at Microsoft Connect?

-Jamie

|||

Interesting situation. In SSIS Excel Source BOL section (http://msdn2.microsoft.com/en-us/library/ms141683.aspx) there is a link "HOWTO: Use ADO with Excel Data from Visual Basic or VBA" that has some sample code to "Enumerate Tables and Fields and Their Properties". I wonder if you could use some of that code inside of a script task to loop through all the sheet in your file and write their name in a flat file. Then it would be pretty easy to use a ForEach loop container with a data flow that connects to the excel file and runs a query like 'Select * from <SSIS variable with the sheet name>'

I have no idea how to write such script component and don't know how feasible that could be; so you do the research.

Rafael Salas

|||

Thank you very much for your reply Jamie.

I haven't used the Foreach item enumerator...could you please explain to me a little bit more how to pass the sheet name to the container?. I saw that I have to add some columns and I have the ones I need...but I don't know how to do the next part.

|||Is there a link where I can see the use of the Foreach item enumerator?|||

Trisha1802 wrote:

Thank you very much for your reply Jamie.

I haven't used the Foreach item enumerator...could you please explain to me a little bit more how to pass the sheet name to the container?. I saw that I have to add some columns and I have the ones I need...but I don't know how to do the next part.

You have to type them in at design-time basically. That's why I said you need to know them. Its not particularly clever or dynamic but it'll work.

-Jamie

|||

Just to add my 2cents to the request list -- I am trying to export from SQL Server to a single workbook with about 100 worksheets. I was hoping to be able to loop through a two-column recordset from a static SQL Server table with the names of the worksheets and the SQL command to extract/export data into each worksheet. The name and path of the workbook remain constant. However, it appears that the "Excel Destination" Data Flow does not accept variables for either the name of the worksheet (or workbook). That means I have to hard-code everything related to the export to 100 worksheets/workbooks. I was hoping to avoid needing to create 100 little export file tasks in the package, but that appears to be my only recourse. Any other ideas?

-Andre Chan

|||

You don't need to create 100 litle exports; you need to change the data access mode of your Excel Destination component to 'Table Name or View Name variable". Then you need to use a SSIS variable to hold the Excel sheet name you want to write into. You can use a forErachLoop container in the control flow to go over the 100 sheet names.

Rafael Salas

|||

To clarify a bit more -- within my For Each loop container, I could always use Excel Destination task to write to a single "dummy" filename, and then use the File System task within the same For Each loop container to re-name the file to whatever I want (using my variable?). In this case, I end up with 100 workbooks rather than 1 workbook with 100 worksheets, but that is better than nothing. Note that I need these worksheets because they are referenced via VLOOKUP from another well-formatted workbook.

The bigger problem I am having now is trying to populate these workbook/worksheets using a dynamic variable SQL command. I want to use a single stored procedure with specified parameters to fill each workbook/worksheet, to make it easier to manage in the database. These SQL commands are loaded into a SQL Server table. It seems built-in options in SSIS are to use a specific pre-defined, pre-created view or table name. But in this case then I need to create 100 different tables or views in the database, and that is what I am trying to avoid, because editing the logic would require updating all 100 views (and perhaps creating/updating all 100 tasks in the package if the view/table names change).

-Andre Chan

|||

Andre Chan wrote:

To clarify a bit more -- within my For Each loop container, I could always use Excel Destination task to write to a single "dummy" filename, and then use the File System task within the same For Each loop container to re-name the file to whatever I want (using my variable?). In this case, I end up with 100 workbooks rather than 1 workbook with 100 worksheets, but that is better than nothing. Note that I need these worksheets because they are referenced via VLOOKUP from another well-formatted workbook.

The bigger problem I am having now is trying to populate these workbook/worksheets using a dynamic variable SQL command. I want to use a single stored procedure with specified parameters to fill each workbook/worksheet, to make it easier to manage in the database. These SQL commands are loaded into a SQL Server table. It seems built-in options in SSIS are to use a specific pre-defined, pre-created view or table name. But in this case then I need to create 100 different tables or views in the database, and that is what I am trying to avoid, because editing the logic would require updating all 100 views (and perhaps creating/updating all 100 tasks in the package if the view/table names change).

-Andre Chan

I see the difference now. I still think you can write all inside of a single workbook, and you are very close to make it. So, if you already succeed looping through the 100 data sets; then trick I think is to make the package to write into the same file but different sheet each time. For that, try to create the 100 sheets in your excel destination file; add a variable to the package called, let's say DestinationSheet, and for each iteration change the value of that variable to one of the 100 sheet your destination file has (according with the set of data you are processing of course).You will not use the FileSystemTask to rename any files; because you are writing into a single file.

Rafael Salas

|||

Thanks, I did get my package finally to run, but I do have one last problem specified below in the last paragraph. As you might expect, my problems were somewhat "trivial" and/or only remotely related. First, I had to specify the worksheet name with $ attached at the end. Perhaps because I had renamed the worksheets within the workbook along the way? That's a software artifact that would be nice to clear up, as there was no helpful error message provided by SSIS, only that the "database" could not be found. That's where I got confused by the wording of the error message -- it never said "worksheet". Second, my source table data had columns specified as "varchar" but Excel Destination task widget only accepts "nvarchar". Changing the datatypes in the source columns to nvarchar fixed that problem. At least SSIS did give an obliquely useful error message in this case (but didn't explicitly tell me to go change the source table datatypes, only that varchar and nvarchar did not "match").

There was also a bit of necessary work-around because, within the ForEach container, in order to specify the source column mappings returned via my stored procedure from the "SQLCommand" variable, or for Excel to recognize its worksheet name/column mappings via its "WorksheetName" variable, I had to pre-define default values for the variables. It would have been nice (but quite advanced) for SSIS to anticipate the first row from the initial SQL recordset that fills the ForEach command.

I have one last problem. If the Excel worksheet already contains data, SSIS appears to be insert new rows for the data (or append it to the existing data -- I haven't figured that out yet). Is there any way to get SSIS to clear the worksheet before dumping data into the worksheet. For example, when setting up a PivotTable or other query from within Excel itself, there are multiple options for completely clearing the existing data first, inserting new rows and clearing rows, or just inserting new rows. I would just like the data cleared, because I don't want to have any VLOOKUP references to this spreadsheet to get corrupted. Rright now, each worksheet has a few hundred rows, but the VLOOKUP references rows 2 through 10000 (ignoring header row), just to ensure it gets the whole data range.

Thanks,

Andre

|||

The last bit of pre-processing I want to do in Excel, I know how to do in an Excel macro, but not in SSIS. Can anyone adapt this into an SSIS Script task? Here is the macro code snippet:

Sub ClearWorksheets()

With Application

.Calculation = xlManual

.EnableEvents = False

Worksheets("MyData").Range("A:H").Clear

.EnableEvents = True

.Calculation = xlAutomatic

End With

End Sub

After the range is cleared, then I can fill that same range. Ideally, the worksheet name and workbook name would both come from a variable.

Thanks,

Andre

|||

Andre,

I actually don't know if SSIS can 'delete' or 'truncate' an excel sheet; What i know though, is SSIS is accessing the excel file using and OLE DB provider for Microsoft Jet; so each sheet is treated as table. With this said; do yourself a favor, research arround Microsoft Jet 4.0, its OLE DB provider and may be you will see your options around what you want to acomplish. that may help you also to understand the limitations of the data types.

If truncating or deleting the existing rows before loading the new data is not possible; you could have an empty excel file with the required structure in a specifc location and then you just create a new copy to be used as the target every time the package runs.

I hope this takes you one step further on all this.

Rafael Salas

|||

Hi Rafael,

Trying to use a DELETE statement against the Excel file yields an error message (the syntax varies based on where I click OK), but generally all say something to the effect of "Deleting data in a linked table is not supported by this ISAM. (Microsoft JET Database Engine)".

So my only recourse seems to be, as you suggested, to (1) create a "blank" Excel template with all the worksheets I need, (2) when my package runs, create a working copy of the template, (3) fill the copy with data on each worksheet, (4) archive the "published" Excel file, if it exists, and (5) copy/rename the working copy to the published filename.

Thanks for your comments, your support and encouragement are appreciated; and if any Microsoft people are following this, it would be nice to address all of the SSIS to Excel issues raised in this thread.

Regards,

Andre Chan

how to loop through several excel sheets in a file in integration services

Hello, I'm new at Integration services and I have an excel file with information in several worksheets. I want to loop through some specific sheets to retrieve the data and save it in a database table. I know how to retrieve the data from one sheet, but I don't know how to do it for several sheets. Any ideas?...I would appreciate any help.

Moving thread to the Intergration Services forum. They may be able to help out.

|||

Trisha1802 wrote:

Hello, I'm new at Integration services and I have an excel file with information in several worksheets. I want to loop through some specific sheets to retrieve the data and save it in a database table. I know how to retrieve the data from one sheet, but I don't know how to do it for several sheets. Any ideas?...I would appreciate any help.

Unfortunately its not possible to loop through sheets unless you know the names of them and you know that they won't change. If those conditions are satisfied then you can use the ForEach item enumerator in the Foreach Loop.

An Excel Sheet enumerator would be a nice addition. Perhaps you could request it at Microsoft Connect?

-Jamie

|||

Interesting situation. In SSIS Excel Source BOL section (http://msdn2.microsoft.com/en-us/library/ms141683.aspx) there is a link "HOWTO: Use ADO with Excel Data from Visual Basic or VBA" that has some sample code to "Enumerate Tables and Fields and Their Properties". I wonder if you could use some of that code inside of a script task to loop through all the sheet in your file and write their name in a flat file. Then it would be pretty easy to use a ForEach loop container with a data flow that connects to the excel file and runs a query like 'Select * from <SSIS variable with the sheet name>'

I have no idea how to write such script component and don't know how feasible that could be; so you do the research.

Rafael Salas

|||

Thank you very much for your reply Jamie.

I haven't used the Foreach item enumerator...could you please explain to me a little bit more how to pass the sheet name to the container?. I saw that I have to add some columns and I have the ones I need...but I don't know how to do the next part.

|||Is there a link where I can see the use of the Foreach item enumerator?|||

Trisha1802 wrote:

Thank you very much for your reply Jamie.

I haven't used the Foreach item enumerator...could you please explain to me a little bit more how to pass the sheet name to the container?. I saw that I have to add some columns and I have the ones I need...but I don't know how to do the next part.

You have to type them in at design-time basically. That's why I said you need to know them. Its not particularly clever or dynamic but it'll work.

-Jamie

|||

Just to add my 2cents to the request list -- I am trying to export from SQL Server to a single workbook with about 100 worksheets. I was hoping to be able to loop through a two-column recordset from a static SQL Server table with the names of the worksheets and the SQL command to extract/export data into each worksheet. The name and path of the workbook remain constant. However, it appears that the "Excel Destination" Data Flow does not accept variables for either the name of the worksheet (or workbook). That means I have to hard-code everything related to the export to 100 worksheets/workbooks. I was hoping to avoid needing to create 100 little export file tasks in the package, but that appears to be my only recourse. Any other ideas?

-Andre Chan

|||

You don't need to create 100 litle exports; you need to change the data access mode of your Excel Destination component to 'Table Name or View Name variable". Then you need to use a SSIS variable to hold the Excel sheet name you want to write into. You can use a forErachLoop container in the control flow to go over the 100 sheet names.

Rafael Salas

|||

To clarify a bit more -- within my For Each loop container, I could always use Excel Destination task to write to a single "dummy" filename, and then use the File System task within the same For Each loop container to re-name the file to whatever I want (using my variable?). In this case, I end up with 100 workbooks rather than 1 workbook with 100 worksheets, but that is better than nothing. Note that I need these worksheets because they are referenced via VLOOKUP from another well-formatted workbook.

The bigger problem I am having now is trying to populate these workbook/worksheets using a dynamic variable SQL command. I want to use a single stored procedure with specified parameters to fill each workbook/worksheet, to make it easier to manage in the database. These SQL commands are loaded into a SQL Server table. It seems built-in options in SSIS are to use a specific pre-defined, pre-created view or table name. But in this case then I need to create 100 different tables or views in the database, and that is what I am trying to avoid, because editing the logic would require updating all 100 views (and perhaps creating/updating all 100 tasks in the package if the view/table names change).

-Andre Chan

|||

Andre Chan wrote:

To clarify a bit more -- within my For Each loop container, I could always use Excel Destination task to write to a single "dummy" filename, and then use the File System task within the same For Each loop container to re-name the file to whatever I want (using my variable?). In this case, I end up with 100 workbooks rather than 1 workbook with 100 worksheets, but that is better than nothing. Note that I need these worksheets because they are referenced via VLOOKUP from another well-formatted workbook.

The bigger problem I am having now is trying to populate these workbook/worksheets using a dynamic variable SQL command. I want to use a single stored procedure with specified parameters to fill each workbook/worksheet, to make it easier to manage in the database. These SQL commands are loaded into a SQL Server table. It seems built-in options in SSIS are to use a specific pre-defined, pre-created view or table name. But in this case then I need to create 100 different tables or views in the database, and that is what I am trying to avoid, because editing the logic would require updating all 100 views (and perhaps creating/updating all 100 tasks in the package if the view/table names change).

-Andre Chan

I see the difference now. I still think you can write all inside of a single workbook, and you are very close to make it. So, if you already succeed looping through the 100 data sets; then trick I think is to make the package to write into the same file but different sheet each time. For that, try to create the 100 sheets in your excel destination file; add a variable to the package called, let's say DestinationSheet, and for each iteration change the value of that variable to one of the 100 sheet your destination file has (according with the set of data you are processing of course).You will not use the FileSystemTask to rename any files; because you are writing into a single file.

Rafael Salas

|||

Thanks, I did get my package finally to run, but I do have one last problem specified below in the last paragraph. As you might expect, my problems were somewhat "trivial" and/or only remotely related. First, I had to specify the worksheet name with $ attached at the end. Perhaps because I had renamed the worksheets within the workbook along the way? That's a software artifact that would be nice to clear up, as there was no helpful error message provided by SSIS, only that the "database" could not be found. That's where I got confused by the wording of the error message -- it never said "worksheet". Second, my source table data had columns specified as "varchar" but Excel Destination task widget only accepts "nvarchar". Changing the datatypes in the source columns to nvarchar fixed that problem. At least SSIS did give an obliquely useful error message in this case (but didn't explicitly tell me to go change the source table datatypes, only that varchar and nvarchar did not "match").

There was also a bit of necessary work-around because, within the ForEach container, in order to specify the source column mappings returned via my stored procedure from the "SQLCommand" variable, or for Excel to recognize its worksheet name/column mappings via its "WorksheetName" variable, I had to pre-define default values for the variables. It would have been nice (but quite advanced) for SSIS to anticipate the first row from the initial SQL recordset that fills the ForEach command.

I have one last problem. If the Excel worksheet already contains data, SSIS appears to be insert new rows for the data (or append it to the existing data -- I haven't figured that out yet). Is there any way to get SSIS to clear the worksheet before dumping data into the worksheet. For example, when setting up a PivotTable or other query from within Excel itself, there are multiple options for completely clearing the existing data first, inserting new rows and clearing rows, or just inserting new rows. I would just like the data cleared, because I don't want to have any VLOOKUP references to this spreadsheet to get corrupted. Rright now, each worksheet has a few hundred rows, but the VLOOKUP references rows 2 through 10000 (ignoring header row), just to ensure it gets the whole data range.

Thanks,

Andre

|||

The last bit of pre-processing I want to do in Excel, I know how to do in an Excel macro, but not in SSIS. Can anyone adapt this into an SSIS Script task? Here is the macro code snippet:

Sub ClearWorksheets()

With Application

.Calculation = xlManual

.EnableEvents = False

Worksheets("MyData").Range("A:H").Clear

.EnableEvents = True

.Calculation = xlAutomatic

End With

End Sub

After the range is cleared, then I can fill that same range. Ideally, the worksheet name and workbook name would both come from a variable.

Thanks,

Andre

|||

Andre,

I actually don't know if SSIS can 'delete' or 'truncate' an excel sheet; What i know though, is SSIS is accessing the excel file using and OLE DB provider for Microsoft Jet; so each sheet is treated as table. With this said; do yourself a favor, research arround Microsoft Jet 4.0, its OLE DB provider and may be you will see your options around what you want to acomplish. that may help you also to understand the limitations of the data types.

If truncating or deleting the existing rows before loading the new data is not possible; you could have an empty excel file with the required structure in a specifc location and then you just create a new copy to be used as the target every time the package runs.

I hope this takes you one step further on all this.

Rafael Salas

|||

Hi Rafael,

Trying to use a DELETE statement against the Excel file yields an error message (the syntax varies based on where I click OK), but generally all say something to the effect of "Deleting data in a linked table is not supported by this ISAM. (Microsoft JET Database Engine)".

So my only recourse seems to be, as you suggested, to (1) create a "blank" Excel template with all the worksheets I need, (2) when my package runs, create a working copy of the template, (3) fill the copy with data on each worksheet, (4) archive the "published" Excel file, if it exists, and (5) copy/rename the working copy to the published filename.

Thanks for your comments, your support and encouragement are appreciated; and if any Microsoft people are following this, it would be nice to address all of the SSIS to Excel issues raised in this thread.

Regards,

Andre Chan

sql

how to loop through my workflow by looking at the file system?

Hi, not sure my subject title makes it clear what I want but here it is.

I have a workflow which basically looks at an excel file in a folder on the local drive and then does loads of stuff to it. Everytime I want to process a different excel file that is in the same location al I have to do is change the value of a single local variable, which is just the name of the excel file.

Is there a way to make this automatic? For exmaple....could I somehow put my whole workflow inside a loop that looks inside that local folder and one by one, get the name of the file, assigns the name to that global variable, and then runs the flow...and continues to do that until it gets to the end?

any help would be greatly appreciated...thanks!!!!

andy

Have you look at the ForEachLoop container in the control flow? you can use it to loop through each excel file in a specific folder.

|||awesome, thanks!

How to load a Unicode file into the database in the same order as the file order

The data file is a simple Unicode file with lines of text. BCP
apparently doesn't guarantee this ordering, and neither does the
import tool. I want to be able to load the data either sequentially or
add line numbering to large Unicode file (1 million lines). I don't
want to deal with another programming language if possible and I
wonder if there's a trick in SQL Server to get this accomplished.
Thanks for any help.
Mark Leary
--== Posted via webservertalk.com - Unlimited-Uncensored-Secure Usenet News==--
http://www.webservertalk.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
--= East/West-Coast Server Farms - Total Privacy via Encryption =--
no-email wrote:
> The data file is a simple Unicode file with lines of text. BCP
> apparently doesn't guarantee this ordering, and neither does the
> import tool. I want to be able to load the data either sequentially or
> add line numbering to large Unicode file (1 million lines). I don't
> want to deal with another programming language if possible and I
> wonder if there's a trick in SQL Server to get this accomplished.
> Thanks for any help.
> Mark Leary
>
Why does the order of the rows inserted into the table matter in your
case? Relational databases don't understand row order. If you need them
sorted in some way after the import, you can create a clustered index on
the table to get the rows ordered in a way that helps your queries
perform better.
In general, I think BCP processes the rows in the file sequentially. But
again, I'm not clear on why this matters.
Could you elaborate on the exact issue you are trying to avoid.
David Gugick
Imceda Software
www.imceda.com
|||"David Gugick" <davidg-nospam@.imceda.com> wrote:

> Why does the order of the rows inserted into the table matter in your
> case? Relational databases don't understand row order. If you need them
> sorted in some way after the import, you can create a clustered index on
> the table to get the rows ordered in a way that helps your queries perform
> better.
> In general, I think BCP processes the rows in the file sequentially. But
> again, I'm not clear on why this matters.
> Could you elaborate on the exact issue you are trying to avoid.
I am trying to load a text file sequentially in order to perform text
manipulations using T-SQL that do depend on the exact order. I would be
happy with simply adding a line number to each line of the Unicode text
file, and then loading the file with line number determining the order, but
I want to avoid programming in another language if possible. Eventually the
loaded text would be converted to proper relational tables. This doesn't
have to do with improving performance. Does this help?
Thanks.
|||no-email wrote:
> "David Gugick" <davidg-nospam@.imceda.com> wrote:
>
> I am trying to load a text file sequentially in order to perform text
> manipulations using T-SQL that do depend on the exact order. I would
> be happy with simply adding a line number to each line of the Unicode
> text file, and then loading the file with line number determining the
> order, but I want to avoid programming in another language if
> possible. Eventually the loaded text would be converted to proper
> relational tables. This doesn't have to do with improving
> performance. Does this help?
> Thanks.
Yes. It sounds like you have rows in a specific order that will need to
be processed once on SQL Server. You want to preserve the order of rows
in the file so the rows can be processed in the same order once on SQL
Server.
In order to do this in any relational database, you need a sort key.
There is never a guarantee that a query you run without an ORDER BY will
return rows in the same order in any consistent way.
My understanding is that BCP feeds the rows in the order they appear in
a file. I can't imagine any reason it would or could do it differently.
In that case, you want to insert the data into a table that contains an
IDENTITY column. You can then use that key for your ORDER BY when
processing the rows from whatever process does that.
David Gugick
Imceda Software
www.imceda.com
|||Do you know what order the source file is sorted in? If so, and if the
sort order column(s) are included then you may not need to know the
line number since it is (theoretically anyway) possible to derive that
information from the other data.
If not, then this article has a useful suggestion:
http://www.google.co.uk/groups?selm=...GP11.phx.gb l
not sure if that will work with unicode data though.
David Portas
SQL Server MVP
|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in
message:

> Do you know what order the source file is sorted in? If so, and if
the
> sort order column(s) are included then you may not need to know the
> line number since it is (theoretically anyway) possible to derive
that
> information from the other data.
Unfortunately the data file consists of simple lines of text with no
other way to extract potential column information before loading it
into the database.

> If not, then this article has a useful suggestion:
>
http://www.google.co.uk/groups?selm=...GP11.phx.gb l
> not sure if that will work with unicode data though.
Good suggestion but it does fail with Unicode. Thanks anyway.
--== Posted via webservertalk.com - Unlimited-Uncensored-Secure Usenet News==--
http://www.webservertalk.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
--= East/West-Coast Server Farms - Total Privacy via Encryption =--
|||"David Gugick" <davidg-nospam@.imceda.com> wrote:

> My understanding is that BCP feeds the rows in the order they appear
in
> a file. I can't imagine any reason it would or could do it
differently.
> In that case, you want to insert the data into a table that contains
an
> IDENTITY column. You can then use that key for your ORDER BY when
> processing the rows from whatever process does that.
In general BCP loads the data in the same order as the file but not
always. The ordering sometimes reverses for thousands of rows, or
skips certain rows, but you need to check it carefully to find the
misordering. You can create a table with an identity column and load
the data, but again if the rows are not loaded in the same sequence as
the file this won't matter. You will end up with an ordered table that
is unfortunately not in the same order as the original file.
To be honest I have tried all these suggestions in the past. My
typical solution would be to open the original file in Excel, add a
rownumber column and then save the resulting file as a Unicode file.
This works up until around a maximum of 63,000 rows. You can break a
file into 63,000 row subfiles, but this would be too tedious if you
have row counts approaching a million.
Thanks for the suggestions but I may have to learn some C#.
Unfortunately Visual Basic has problems with Unicode, as I suspect
also C++ and C have similar problems.
--== Posted via webservertalk.com - Unlimited-Uncensored-Secure Usenet News==--
http://www.webservertalk.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
--= East/West-Coast Server Farms - Total Privacy via Encryption =--
|||What about using the CTS Import Wizard to import the data from the flat
file into a table with an identity.
Where is this data coming from? Is there any way to recreate it with a
counter column included in the output?
David Gugick
Imceda Software
www.imceda.com
|||"David Gugick" <davidg-nospam@.imceda.com> wrote:

> What about using the CTS Import Wizard to import the data from the
flat
> file into a table with an identity.
It's the same problem. The table order generally follows the order in
the file but not always.

> Where is this data coming from? Is there any way to recreate it with
a
> counter column included in the output?
It's foreign language dictionary data that cannot be recreated. I
could manipulate the data on the file level but I am trying to avoid
potential problems with Unicode. Once the data gets into SQL Server I
don't have any problems, as long as the table order exactly matches
the file order.
--== Posted via webservertalk.com - Unlimited-Uncensored-Secure Usenet News==--
http://www.webservertalk.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
--= East/West-Coast Server Farms - Total Privacy via Encryption =--
|||no-email wrote:
> It's foreign language dictionary data that cannot be recreated. I
> could manipulate the data on the file level but I am trying to avoid
> potential problems with Unicode. Once the data gets into SQL Server I
> don't have any problems, as long as the table order exactly matches
> the file order.
I'm not sure what problems you would have as long as the tool you are
using to edit the data is unicode aware. I use TextEdit for editing
(www.textpad.com) and it has simple replacement expressions.
Assuming you had each row of data on a single line, you could simply do
the following:
1- Add a leading CARRIAGE RETURN to the file
2- Open the Replace dialog
3- Check the Regular Expression option
4- Type "\n" - WITHOUT QUOTES in the Find What entry- means New Line
character
5- Type "\n\i\t" - WITHOUT QUOTES in the Replace With entry - means New
Line + Auto Number + TAB
6- Click Replace All
7 - Remove the leading carriage return in the file
8 - Click FILE SAVE AS and make sure the UNICODE option is selected
You can replace the TAB character with whatever your file requires or
add DOUBLE QUOTES around the Auto Number, etc.
You can download a free trial of TextPad on the web site.
David Gugick
Imceda Software
www.imceda.com

How to Log Locks within a Database

I was wondering if there was a simple script that could be run or a way to h
ave an entry created in a log file whenever a SQL Lock occurs. Specifically
if it could also give the username of the user running the query that creat
ed the lock. Thank you.You could have a profiler trace running. But be aware that the locking
activity in SQL Server can be *very* high! Do a test first so you don't
overload the system with the logging.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:3A5B46FE-B873-4AD4-8078-27C7B2DC885D@.microsoft.com...
> I was wondering if there was a simple script that could be run or a way to
have an entry created in a log file whenever a SQL Lock occurs.
Specifically if it could also give the username of the user running the
query that created the lock. Thank you.|||Tibor, Thanks for the reply. I tried profiling but noticed the performance
decrease. All I really would like is basically something that alerts me via
either a log file I can check daily or a netsend message that tells me when
the database gets blocked
by a SPID and who it is that is causing the block. I don't actually need to
monitor every lock as I found out with the profiler. Thank you.|||INF: How to Monitor SQL Server 2000 Blocking
http://support.microsoft.com/defaul...kb;EN-US;271509
Also have a look at
INF: Troubleshooting Application Performance with SQL Server
http://support.microsoft.com/defaul...kb;EN-US;224587
HOW TO: Troubleshoot Slow-Running Queries on SQL Server 7.0 or Later
http://support.microsoft.com/defaul...kb;EN-US;243589
INF: Understanding and Resolving SQL Server 7.0
or 2000 Blocking Problems
http://support.microsoft.com/defaul...b;EN-US;Q224453
As well as these articles themselves, they contain links in them to lots
of other performace troubleshooting type articles. Lots of good stuff !
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:3A5B46FE-B873-4AD4-8078-27C7B2DC885D@.microsoft.com...
> I was wondering if there was a simple script that could be run or a way to
have an entry created in a log file whenever a SQL Lock occurs.
Specifically if it could also give the username of the user running the
query that created the lock. Thank you.

How to load jpeg file in SqL2000 and how to retrieve from SQL2000.

Hi friends
Now I am working in SQL2000 as back end .I want to load jpeg file in database and retrieve from database. Please guide me.You can use this:

CREATE TABLE Images ([stream] [image] NULL)
insert into Images ([stream]) values (@.image)

Hope this helps!!
Deven.

How to load images to table

I added a column to a table to hold images. How can I easily upload
these images from the disk file? There are only a few so i thought
there would be a command or utility that I could use, but I haven't
been able to find one.In SQL Server 2005 you can use the BULK option of OPENROWSET:
CREATE TABLE Foo ( image_data VARBINARY(MAX));
INSERT INTO Foo (image_data)
SELECT image_data
FROM OPENROWSET(BULK N'C:\image.jpg',
SINGLE_BLOB) AS ImageSource(image_data);
HTH,
Plamen Ratchev
http://www.SQLStudio.com|||Also take a look at the TEXTCOPY.exe utility. You can Google for TEXTCOPY,
and find a lot of info. But here's one
http://www.mssqlcity.com/Articles/KnowHow/Textcopy.htm
Linchi
"cs_hart@.yahoo.com" wrote:
> I added a column to a table to hold images. How can I easily upload
> these images from the disk file? There are only a few so i thought
> there would be a command or utility that I could use, but I haven't
> been able to find one.
>|||On Jan 22, 2:20 pm, "Plamen Ratchev" <Pla...@.SQLStudio.com> wrote:
> In SQL Server 2005 you can use the BULK option of OPENROWSET:
> CREATE TABLE Foo ( image_data VARBINARY(MAX));
> INSERT INTO Foo (image_data)
> SELECT image_data
> FROM OPENROWSET(BULK N'C:\image.jpg',
> SINGLE_BLOB) AS ImageSource(image_data);
> HTH,
> Plamen Ratchevhttp://www.SQLStudio.com
I tried this but get an error Incorrect syntax near the keyword 'BULK'.|||Are you using SQL Server 2005? As I noted this works only on SQL Server
2005. For uploading images to SQL Server 2000, you can see this example by
Erland Sommarskog:
http://www.sommarskog.se/blobload.txt
HTH,
Plamen Ratchev
http://www.SQLStudio.com

How to load any file into FTP server through Javascript.

Dear all,
Iam with a small problem.
I want to access to FTP server through Html page by entering a username
and password.In the same html page I have to slect a file through
browse button and load it into FTP server.
PLz help me with the code also.
All this should be in Javascript.
Any help is appreciated...
Thanks a lot...~!~!~!
Bye
Hi
If this is ASP then you can use one of the products mentioned on
http://www.aspfaq.com/show.asp?id=2189.
This is not really an XML or SQL Server question so posting in a more
appropriate group may be an idea!
John
"vinodh" <vinodh.singh@.gmail.com> wrote in message
news:1126834578.588797.184610@.f14g2000cwb.googlegr oups.com...
> Dear all,
>
> Iam with a small problem.
> I want to access to FTP server through Html page by entering a username
> and password.In the same html page I have to slect a file through
> browse button and load it into FTP server.
> PLz help me with the code also.
> All this should be in Javascript.
> Any help is appreciated...
>
> Thanks a lot...~!~!~!
> Bye
>

How to load any file into FTP server through Javascript.

Dear all,
Iam with a small problem.
I want to access to FTP server through Html page by entering a username
and password.In the same html page I have to slect a file through
browse button and load it into FTP server.
PLz help me with the code also.
All this should be in Javascript.
Any help is appreciated...
Thanks a lot...~!~!~!
ByeHi
If this is ASP then you can use one of the products mentioned on
http://www.aspfaq.com/show.asp?id=2189.
This is not really an XML or SQL Server question so posting in a more
appropriate group may be an idea!
John
"vinodh" <vinodh.singh@.gmail.com> wrote in message
news:1126834578.588797.184610@.f14g2000cwb.googlegroups.com...
> Dear all,
>
> Iam with a small problem.
> I want to access to FTP server through Html page by entering a username
> and password.In the same html page I have to slect a file through
> browse button and load it into FTP server.
> PLz help me with the code also.
> All this should be in Javascript.
> Any help is appreciated...
>
> Thanks a lot...~!~!~!
> Bye
>

How to load a Unicode file into the database in the same order as the file order

The data file is a simple Unicode file with lines of text. BCP
apparently doesn't guarantee this ordering, and neither does the
import tool. I want to be able to load the data either sequentially or
add line numbering to large Unicode file (1 million lines). I don't
want to deal with another programming language if possible and I
wonder if there's a trick in SQL Server to get this accomplished.
Thanks for any help.
Mark Leary
--== Posted via webservertalk.com - Unlimited-Uncensored-Secure Usenet News==--
http://www.webservertalk.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
--= East/West-Coast Server Farms - Total Privacy via Encryption =--
no-email wrote:
> The data file is a simple Unicode file with lines of text. BCP
> apparently doesn't guarantee this ordering, and neither does the
> import tool. I want to be able to load the data either sequentially or
> add line numbering to large Unicode file (1 million lines). I don't
> want to deal with another programming language if possible and I
> wonder if there's a trick in SQL Server to get this accomplished.
> Thanks for any help.
> Mark Leary
>
Why does the order of the rows inserted into the table matter in your
case? Relational databases don't understand row order. If you need them
sorted in some way after the import, you can create a clustered index on
the table to get the rows ordered in a way that helps your queries
perform better.
In general, I think BCP processes the rows in the file sequentially. But
again, I'm not clear on why this matters.
Could you elaborate on the exact issue you are trying to avoid.
David Gugick
Imceda Software
www.imceda.com
|||"David Gugick" <davidg-nospam@.imceda.com> wrote:

> Why does the order of the rows inserted into the table matter in your
> case? Relational databases don't understand row order. If you need them
> sorted in some way after the import, you can create a clustered index on
> the table to get the rows ordered in a way that helps your queries perform
> better.
> In general, I think BCP processes the rows in the file sequentially. But
> again, I'm not clear on why this matters.
> Could you elaborate on the exact issue you are trying to avoid.
I am trying to load a text file sequentially in order to perform text
manipulations using T-SQL that do depend on the exact order. I would be
happy with simply adding a line number to each line of the Unicode text
file, and then loading the file with line number determining the order, but
I want to avoid programming in another language if possible. Eventually the
loaded text would be converted to proper relational tables. This doesn't
have to do with improving performance. Does this help?
Thanks.
|||no-email wrote:
> "David Gugick" <davidg-nospam@.imceda.com> wrote:
>
> I am trying to load a text file sequentially in order to perform text
> manipulations using T-SQL that do depend on the exact order. I would
> be happy with simply adding a line number to each line of the Unicode
> text file, and then loading the file with line number determining the
> order, but I want to avoid programming in another language if
> possible. Eventually the loaded text would be converted to proper
> relational tables. This doesn't have to do with improving
> performance. Does this help?
> Thanks.
Yes. It sounds like you have rows in a specific order that will need to
be processed once on SQL Server. You want to preserve the order of rows
in the file so the rows can be processed in the same order once on SQL
Server.
In order to do this in any relational database, you need a sort key.
There is never a guarantee that a query you run without an ORDER BY will
return rows in the same order in any consistent way.
My understanding is that BCP feeds the rows in the order they appear in
a file. I can't imagine any reason it would or could do it differently.
In that case, you want to insert the data into a table that contains an
IDENTITY column. You can then use that key for your ORDER BY when
processing the rows from whatever process does that.
David Gugick
Imceda Software
www.imceda.com
|||Do you know what order the source file is sorted in? If so, and if the
sort order column(s) are included then you may not need to know the
line number since it is (theoretically anyway) possible to derive that
information from the other data.
If not, then this article has a useful suggestion:
http://www.google.co.uk/groups?selm=...GP11.phx.gb l
not sure if that will work with unicode data though.
David Portas
SQL Server MVP
|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in
message:

> Do you know what order the source file is sorted in? If so, and if
the
> sort order column(s) are included then you may not need to know the
> line number since it is (theoretically anyway) possible to derive
that
> information from the other data.
Unfortunately the data file consists of simple lines of text with no
other way to extract potential column information before loading it
into the database.

> If not, then this article has a useful suggestion:
>
http://www.google.co.uk/groups?selm=...GP11.phx.gb l
> not sure if that will work with unicode data though.
Good suggestion but it does fail with Unicode. Thanks anyway.
--== Posted via webservertalk.com - Unlimited-Uncensored-Secure Usenet News==--
http://www.webservertalk.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
--= East/West-Coast Server Farms - Total Privacy via Encryption =--
|||"David Gugick" <davidg-nospam@.imceda.com> wrote:

> My understanding is that BCP feeds the rows in the order they appear
in
> a file. I can't imagine any reason it would or could do it
differently.
> In that case, you want to insert the data into a table that contains
an
> IDENTITY column. You can then use that key for your ORDER BY when
> processing the rows from whatever process does that.
In general BCP loads the data in the same order as the file but not
always. The ordering sometimes reverses for thousands of rows, or
skips certain rows, but you need to check it carefully to find the
misordering. You can create a table with an identity column and load
the data, but again if the rows are not loaded in the same sequence as
the file this won't matter. You will end up with an ordered table that
is unfortunately not in the same order as the original file.
To be honest I have tried all these suggestions in the past. My
typical solution would be to open the original file in Excel, add a
rownumber column and then save the resulting file as a Unicode file.
This works up until around a maximum of 63,000 rows. You can break a
file into 63,000 row subfiles, but this would be too tedious if you
have row counts approaching a million.
Thanks for the suggestions but I may have to learn some C#.
Unfortunately Visual Basic has problems with Unicode, as I suspect
also C++ and C have similar problems.
--== Posted via webservertalk.com - Unlimited-Uncensored-Secure Usenet News==--
http://www.webservertalk.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
--= East/West-Coast Server Farms - Total Privacy via Encryption =--
|||What about using the CTS Import Wizard to import the data from the flat
file into a table with an identity.
Where is this data coming from? Is there any way to recreate it with a
counter column included in the output?
David Gugick
Imceda Software
www.imceda.com
|||"David Gugick" <davidg-nospam@.imceda.com> wrote:

> What about using the CTS Import Wizard to import the data from the
flat
> file into a table with an identity.
It's the same problem. The table order generally follows the order in
the file but not always.

> Where is this data coming from? Is there any way to recreate it with
a
> counter column included in the output?
It's foreign language dictionary data that cannot be recreated. I
could manipulate the data on the file level but I am trying to avoid
potential problems with Unicode. Once the data gets into SQL Server I
don't have any problems, as long as the table order exactly matches
the file order.
--== Posted via webservertalk.com - Unlimited-Uncensored-Secure Usenet News==--
http://www.webservertalk.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
--= East/West-Coast Server Farms - Total Privacy via Encryption =--
|||no-email wrote:
> It's foreign language dictionary data that cannot be recreated. I
> could manipulate the data on the file level but I am trying to avoid
> potential problems with Unicode. Once the data gets into SQL Server I
> don't have any problems, as long as the table order exactly matches
> the file order.
I'm not sure what problems you would have as long as the tool you are
using to edit the data is unicode aware. I use TextEdit for editing
(www.textpad.com) and it has simple replacement expressions.
Assuming you had each row of data on a single line, you could simply do
the following:
1- Add a leading CARRIAGE RETURN to the file
2- Open the Replace dialog
3- Check the Regular Expression option
4- Type "\n" - WITHOUT QUOTES in the Find What entry- means New Line
character
5- Type "\n\i\t" - WITHOUT QUOTES in the Replace With entry - means New
Line + Auto Number + TAB
6- Click Replace All
7 - Remove the leading carriage return in the file
8 - Click FILE SAVE AS and make sure the UNICODE option is selected
You can replace the TAB character with whatever your file requires or
add DOUBLE QUOTES around the Auto Number, etc.
You can download a free trial of TextPad on the web site.
David Gugick
Imceda Software
www.imceda.com

How to load a Unicode file into the database in the same order as the file order

The data file is a simple Unicode file with lines of text. BCP
apparently doesn't guarantee this ordering, and neither does the
import tool. I want to be able to load the data either sequentially or
add line numbering to large Unicode file (1 million lines). I don't
want to deal with another programming language if possible and I
wonder if there's a trick in SQL Server to get this accomplished.
Thanks for any help.
Mark Leary
--== Posted via webservertalk.com - Unlimited-Uncensored-Secure Usenet News==--
http://www.webservertalk.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
--= East/West-Coast Server Farms - Total Privacy via Encryption =--
no-email wrote:
> The data file is a simple Unicode file with lines of text. BCP
> apparently doesn't guarantee this ordering, and neither does the
> import tool. I want to be able to load the data either sequentially or
> add line numbering to large Unicode file (1 million lines). I don't
> want to deal with another programming language if possible and I
> wonder if there's a trick in SQL Server to get this accomplished.
> Thanks for any help.
> Mark Leary
>
Why does the order of the rows inserted into the table matter in your
case? Relational databases don't understand row order. If you need them
sorted in some way after the import, you can create a clustered index on
the table to get the rows ordered in a way that helps your queries
perform better.
In general, I think BCP processes the rows in the file sequentially. But
again, I'm not clear on why this matters.
Could you elaborate on the exact issue you are trying to avoid.
David Gugick
Imceda Software
www.imceda.com
|||"David Gugick" <davidg-nospam@.imceda.com> wrote:

> Why does the order of the rows inserted into the table matter in your
> case? Relational databases don't understand row order. If you need them
> sorted in some way after the import, you can create a clustered index on
> the table to get the rows ordered in a way that helps your queries perform
> better.
> In general, I think BCP processes the rows in the file sequentially. But
> again, I'm not clear on why this matters.
> Could you elaborate on the exact issue you are trying to avoid.
I am trying to load a text file sequentially in order to perform text
manipulations using T-SQL that do depend on the exact order. I would be
happy with simply adding a line number to each line of the Unicode text
file, and then loading the file with line number determining the order, but
I want to avoid programming in another language if possible. Eventually the
loaded text would be converted to proper relational tables. This doesn't
have to do with improving performance. Does this help?
Thanks.
|||no-email wrote:
> "David Gugick" <davidg-nospam@.imceda.com> wrote:
>
> I am trying to load a text file sequentially in order to perform text
> manipulations using T-SQL that do depend on the exact order. I would
> be happy with simply adding a line number to each line of the Unicode
> text file, and then loading the file with line number determining the
> order, but I want to avoid programming in another language if
> possible. Eventually the loaded text would be converted to proper
> relational tables. This doesn't have to do with improving
> performance. Does this help?
> Thanks.
Yes. It sounds like you have rows in a specific order that will need to
be processed once on SQL Server. You want to preserve the order of rows
in the file so the rows can be processed in the same order once on SQL
Server.
In order to do this in any relational database, you need a sort key.
There is never a guarantee that a query you run without an ORDER BY will
return rows in the same order in any consistent way.
My understanding is that BCP feeds the rows in the order they appear in
a file. I can't imagine any reason it would or could do it differently.
In that case, you want to insert the data into a table that contains an
IDENTITY column. You can then use that key for your ORDER BY when
processing the rows from whatever process does that.
David Gugick
Imceda Software
www.imceda.com
|||Do you know what order the source file is sorted in? If so, and if the
sort order column(s) are included then you may not need to know the
line number since it is (theoretically anyway) possible to derive that
information from the other data.
If not, then this article has a useful suggestion:
http://www.google.co.uk/groups?selm=...GP11.phx.gb l
not sure if that will work with unicode data though.
David Portas
SQL Server MVP
|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in
message:

> Do you know what order the source file is sorted in? If so, and if
the
> sort order column(s) are included then you may not need to know the
> line number since it is (theoretically anyway) possible to derive
that
> information from the other data.
Unfortunately the data file consists of simple lines of text with no
other way to extract potential column information before loading it
into the database.

> If not, then this article has a useful suggestion:
>
http://www.google.co.uk/groups?selm=...GP11.phx.gb l
> not sure if that will work with unicode data though.
Good suggestion but it does fail with Unicode. Thanks anyway.
--== Posted via webservertalk.com - Unlimited-Uncensored-Secure Usenet News==--
http://www.webservertalk.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
--= East/West-Coast Server Farms - Total Privacy via Encryption =--
|||"David Gugick" <davidg-nospam@.imceda.com> wrote:

> My understanding is that BCP feeds the rows in the order they appear
in
> a file. I can't imagine any reason it would or could do it
differently.
> In that case, you want to insert the data into a table that contains
an
> IDENTITY column. You can then use that key for your ORDER BY when
> processing the rows from whatever process does that.
In general BCP loads the data in the same order as the file but not
always. The ordering sometimes reverses for thousands of rows, or
skips certain rows, but you need to check it carefully to find the
misordering. You can create a table with an identity column and load
the data, but again if the rows are not loaded in the same sequence as
the file this won't matter. You will end up with an ordered table that
is unfortunately not in the same order as the original file.
To be honest I have tried all these suggestions in the past. My
typical solution would be to open the original file in Excel, add a
rownumber column and then save the resulting file as a Unicode file.
This works up until around a maximum of 63,000 rows. You can break a
file into 63,000 row subfiles, but this would be too tedious if you
have row counts approaching a million.
Thanks for the suggestions but I may have to learn some C#.
Unfortunately Visual Basic has problems with Unicode, as I suspect
also C++ and C have similar problems.
--== Posted via webservertalk.com - Unlimited-Uncensored-Secure Usenet News==--
http://www.webservertalk.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
--= East/West-Coast Server Farms - Total Privacy via Encryption =--
|||What about using the CTS Import Wizard to import the data from the flat
file into a table with an identity.
Where is this data coming from? Is there any way to recreate it with a
counter column included in the output?
David Gugick
Imceda Software
www.imceda.com
|||"David Gugick" <davidg-nospam@.imceda.com> wrote:

> What about using the CTS Import Wizard to import the data from the
flat
> file into a table with an identity.
It's the same problem. The table order generally follows the order in
the file but not always.

> Where is this data coming from? Is there any way to recreate it with
a
> counter column included in the output?
It's foreign language dictionary data that cannot be recreated. I
could manipulate the data on the file level but I am trying to avoid
potential problems with Unicode. Once the data gets into SQL Server I
don't have any problems, as long as the table order exactly matches
the file order.
--== Posted via webservertalk.com - Unlimited-Uncensored-Secure Usenet News==--
http://www.webservertalk.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
--= East/West-Coast Server Farms - Total Privacy via Encryption =--
|||no-email wrote:
> It's foreign language dictionary data that cannot be recreated. I
> could manipulate the data on the file level but I am trying to avoid
> potential problems with Unicode. Once the data gets into SQL Server I
> don't have any problems, as long as the table order exactly matches
> the file order.
I'm not sure what problems you would have as long as the tool you are
using to edit the data is unicode aware. I use TextEdit for editing
(www.textpad.com) and it has simple replacement expressions.
Assuming you had each row of data on a single line, you could simply do
the following:
1- Add a leading CARRIAGE RETURN to the file
2- Open the Replace dialog
3- Check the Regular Expression option
4- Type "\n" - WITHOUT QUOTES in the Find What entry- means New Line
character
5- Type "\n\i\t" - WITHOUT QUOTES in the Replace With entry - means New
Line + Auto Number + TAB
6- Click Replace All
7 - Remove the leading carriage return in the file
8 - Click FILE SAVE AS and make sure the UNICODE option is selected
You can replace the TAB character with whatever your file requires or
add DOUBLE QUOTES around the Auto Number, etc.
You can download a free trial of TextPad on the web site.
David Gugick
Imceda Software
www.imceda.com