Copy data from tables with script!

Hello!

I cant figure out how to copy data from tables in my default database to my new automate-generated database.

I can generate Store Procedures, I can generate the Tables but are there anyone one who knows how to copy the data from tables and put it into my new automate-generated shell. The automate-generate funktion i have built just creating a shell/empty database. How to fill it with a sql-script or similar?

Anyone have any tip?

Regards

If you mean you want to script out the data into the same script with the DDL for the tables try the Database Publishing Wizard:

http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=29B4FFD8-AC3A-4481-B352-9B185619A901

|||

It worked perfectly!

Thanks!

Copy annotations out of xml/ dtsx package

Is there a way to strip out the annotations from a dtsx package. I'm looking at quite a few and would like to automaticly pull those out and store them somewhere in either a table or a text file.

Is this possible or am I going to have to hand copy them out?

Thanks for the help
Saitham8

The dtsx file is in xml format, so one would think that this would be possible with an XPath query. However, I made a copy of my dtsx file, then added several annotations and saved. The modified dtsx file showed a new last modified date, but when I used WinMerge and fc (even fc /b - for a binary comparison) there were no changes.

So then I created a brand new blank SSIS package. Then I saved it and made a copy of the entire solution directory structure. Then I added an annotation and saved and closed BIDS. Then I used WinMerge to compare the two directory structures. The only file that was different was the Integration Services Project3.suo file which is at the solution directory level.

To further test I opened up the unaltered copy deleted its blank package and then imported the annotated package. It came up without annotations!

Conclusion: the annotations are stored in the solution in the .suo file!

What kind of design flaw is this!

|||

If that were true then I wouldn't be able to copy a package containing an annotation into a seperate package and have the annotation show up. Which I can.

-Jamie

|||

Jamie,

I am as surprised as you. I am just reporting the result of some testing I did. I made an annotation in a package, saved it and imported it into another solution and the anotation did not come with it. I am running SQL 2005 no sp. I have not yet tried it with SP 1.

Are you having success doing this on SP 1 or pre SP 1?

|||

David Lundell wrote:

Jamie,

I am as surprised as you. I am just reporting the result of some testing I did. I made an annotation in a package, saved it and imported it into another solution and the anotation did not come with it. I am running SQL 2005 no sp. I have not yet tried it with SP 1.

Are you having success doing this on SP 1 or pre SP 1?

AHA. I am on SP1. Perhaps that's why.

-Jamie

|||So it sounds like I'll need to move up to sp1 and then take a look for the anotation tag in the xml. I won't have a chance to get to this for a little while but I'll let you know what I find.|||

I upgraded to SP 1 and saw the same behavior as before.

I create a blank IS project

I copy the project to a new directory.

I annotate the otherwise blank package.dtsx file

I hit the save all button.

I then use winmerge to compare the two directory structures. Again the only file I see that has changed is .suo

So I open the untouched copy. I delete its package.dtsx. I then import existing package and grab it from the original project. I don't see the annotation.

Jamie, could you provide us a step by step of how you get the annotations to move with the package?

|||Everytime you save a modified package in designer, at least two properties are changed: VersionGuid (new GUID is generated) and VersionBuild (build is incremented), so the dtsx files should differ.
I would check if you've saved the modified package.
Once you added an annotation, click away from the annotation on some other place in control flow. The title of the document should get asterisk at the end (like "Package 1.dtsx [Design]*", meaning it is modified and will be saved. Now click save all.
Once the modified file has been saved, windiff should show quite a bit of differences in DTSX files. The annotations and diagram layouts are stored in DTSX file, not in SUO files. This is true both for RTM and SP1 builds.|||

User error. The annotations are stored in the package, not in the SUO file.

You can't use XSLT to strip those out unless you can convert the binary encodings which is how they're persisted.

K

|||

Michael,

I have tried what you suggested. I added the annotation and then clicked in some other place in the control flow. The title of the document did not get an asterisk. I did click Save, and I also tried Save All. I still got the same result.

Now to report the real bug! I closed the package and then when I reopened it the annotation was gone. Since all of these were throwaway packages I was creating just to research the issue for the original posting I did not have a need to go back into them. I did so this morning, and the annotation did not stick in any of them.

So I modified my experiment. I added something else to the control flow, an ActiveX Script task. Then I saved it and copied the whole solution directory. Then I added the annotation (no asterisk appeared). Then I renamed the ActiveX Script Task. At that point I got the asterisk to appear. Then in the comparison I see a difference in the package.dtsx. It looks to me like the text of the annotation is stored in the binary attribute of the ddsxmlobjectstreaminitwrapper element. Then when I move the package the annotation comes with it.

So the bug is if the only change I make is adding or modifying an annotation then BIDS does not indicate that I have changed the package and hence does not save the new or modified annotation! Please try it and you will find the bug. In summary if I go into a package for the sole purpose of adding and/or modifying annotations they will not get saved.

I am glad however to discover that I was wrong about the .suo file, because that sure did not make sense! But based on the actual bug reported and my exact steps you can see how it looked like that!

Thanks for the help, and please report back on what you find out with this bug.

|||David I was noticing that the annotations disapear if you don't have any text in them. I still havent gotten a chance to try my hand at this stuff. Thanks a ton for looking into it.|||

Kirk,

You are correct. The annotations are stored in the package.

However, as I documented in my reply to Michael Entin - MSFT ,
there is a bug in BIDS that caused the behavior I described.

Essentially, if all you do is add or modify annotations those changes do not get saved at all. That is why I only saw changes in the .suo

converting varchar to date from asp.net to sort

for some odd reason our other programmer used varchar datatype to store dates. he claims it gives him more control. now i am trying to sort it based on date. so i create a procedure:

CREATE PROCEDURE GetAllWeekEnding

AS
Select convert(datetime, we) as we2 FROM tblArchive order by we2
GO

if i use the convert function in the procedure, i'll get an error msg when i run the code. this is the code i am using.

Dim MyConnection As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("ConnectionStringSQL"))
Dim MyCommand As SqlCommand

MyCommand = New SqlCommand("GetAllWeekEnding", MyConnection)
MyCommand.CommandType = CommandType.StoredProcedure
MyConnection.Open()

Dim mydr As SqlDataReader = MyCommand.ExecuteReader()
While mydr.Read()
DropDownList1.Items.Add(mydr("we2"))
End While

mydr.Close()
MyConnection.Close()

the error message is: No accessible overloaded 'ListItemCollection.Add' can be called without a narrowing conversion

any ideas?the .Add method expects a ListItem or a String. Try:

DropDownList1.Items.Add(mydr("we2").ToString())

Is there any reason you are not just binding to myDr?|||<<<<Is there any reason you are not just binding to myDr? >>>
yes, when i bind to the listbox, i am getting date and time.

i can get rid of the time by using:

While mydr.Read()
DropDownList1.Items.Add(Format(mydr("we2"), "M/dd/yyyy").ToString)
End While|||You could easily use Convert to convert the datetime to a date only (of type varchar()) in SQL Server in the SP if the only purpose of this SP is to feed this DDL.|||<<<<<You could easily use Convert to convert the datetime to a date only (of type varchar()) in SQL Server in the SP if the only purpose of this SP is to feed this DDL.
>>>>
the problem is that the date is already in varchar datatype in the database. on the aspx page, i want to load all the dates and i want it sorted by date. the only way for me to show the dates on the aspx page sorted by date is to convert the date from varchar to datetime, like this:

CREATE PROCEDURE GetAllWeekEnding

AS
Select Distinct convert(datetime, we) as we2 FROM tblArchive order by we2 desc
GO

once varchar is converted to datetime, it not only shows just the date but also time so i cannot bind it to a dropDownList.

if i just bind the orginal varchar on the DropDownList witout converting it to DateTime, it's not going to be sorted because you cannot sort a varchar like you sort a datetime|||CREATE PROCEDURE GetAllWeekEnding

AS
Select Distinct CONVERT(nvarchar(20),convert(datetime, we),101) as we2 FROM tblArchive order by convert(datetime, we) desc
GO

There is no rule that the representation you SELECT needs to be the same as you use to ORDER BY.